Load columns in an Excel worksheet to a 2D wave

// 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