Load columns in an Excel worksheet to a 2D wave

A code to read all columns in an Excel worksheet into a 2D wave
If the excel sheet contains labels in the first row they are used as dimension labels.
// Fuction to read the raw data from Excel Sheets and concatenate all
// columns in the same worksheet into a 2D wave (matrix)
// It reads the wave names from the first row and uses them as dimension labels

Function ExcelTo2DWave()

// select the excel file
do
    getfilefolderinfo/Q
    if (V_Flag<0)
        return -1               //User cacelled
    elseif (V_isFolder==1)      //User selectes folder instead of file
        DoAlert/T="Message" 0, "Please choose a file not a folder"
    elseif (V_Flag==0 && V_isFile==1)           // make sure a file is selected
        if (stringmatch(S_Path, "*.xls*"))      // make sure it is an Excel file
        String filePath = S_Path                // full file path
        else
        V_isFile = 0
        DoAlert/T="Message" 0, "The selected file is not an Excel file. Please select an Excel file."
        endif
    endif
while (V_isFile !=1)


XLLoadWave/Q/J=1 filePath           //use XLLoadWave/J=1 to get info (the worksheets) in the Excel file
String wkShList = S_value               // a : sepearted list of sheets in the Excel file

// each excel file loads its worksheets in a seperate data folder
// this is useful when loading multiple excel files that may contain repeated worksheet names
// comment out the lines with dataFolder if not needed
String savedDataFolder = GetDataFolder(1)      
String fileName = ParseFilePath(0,filePath,":",1,0)         // get file name from full file path
String extension = "." + ParseFilePath(4, fileName, ":", 0, 0)      // e.g., ".xls"
String dfName = RemoveEnding(fileName, extension)
NewDataFolder/O/S :$dfName

// loop for each worksheet in the excel file to load all columns in to a 2D wave
Variable i, numWkSh
i=0
numWkSh = ItemsInList(wkShList)     // the number of worksheets
String wkShLoad
do     
wkShLoad =StringFromList(i,wkShList)        //pick a worksheet to load wave from
LoadTo2Dwave(filePath, wkShLoad)            //pass the full file path and worksheet name to load waves
i += 1
while (i<numWkSh)
SetDataFolder savedDataFolder
End

// the function loads the columns in to 1D wave and then
//uses concatenate to merge them into a 2D wave

Function LoadTo2DWave(fName, wkShLoad)
String fName, wkShLoad

//Loads each column in "wkShLoad" to a 1D Wave
// dtermine wave type from row 10 - change as appropriate
// takes row 1 as wave names - change as appropriate
XLLoadWave/Q/S=wkShLoad/C=10/W=1/O fName    

//create a list of wave to concatenate exclude 2D waves and Text wave
String conList = WaveList("*",";","DIMS:1,Text:0")
// get row and columns
Variable rows =numpnts($StringFromList(1,conList))
Variable cols = ItemsInlist(conList)
Make/O/N=(rows, cols) wname

//concatenate into wanme and kill source waves
Concatenate/O/KILL/DL conList, wname

// rename 2D wave as the worksheet name
Rename wname $wkShLoad

//kill remaining waves (text waves)
String kList = WaveList("*",";","Text:1")
Variable j
do
String kWave =StringFromList(j,kList)
KillWaves $kWave
j += 1
while (j<ItemsInList(kList))

End
ExcelLoad.ipf

Forum

Support

Gallery

Igor Pro 8

Learn More

Igor XOP Toolkit

Learn More

Igor NIDAQ Tools MX

Learn More