Hello,
I have multiple Excel files in a folder and each has several sheets. The table structure for each file and sheet is the same. I am trying to run a loop so that all the data is input and auto-concatenated. The ODBC connector works when linked to a single Excel file, but I get a variety of error messages when trying to get it to look at all the Excel files.
I have used a script posted on here and searched for anything else that might help, but haven't had any luck yet. I have set up the System DSN with 'Driver do Microsoft Excel' and that is currently linked to the Excel file that works. Do I need to change this to make it look at all the files in the folder? I tried not linking to a specific file but that didn't work. Or perhaps it is the syntax used for the 'Lib Connect To' statement?
Below is the script I am using. I have commented out the parts that I was using to try and loop through multiple files.
// LET vFilePath = 'C:\Users\****\Assessment data';
// FOR EACH file in FileList('$(vFilePath)\*.xlsx'); // Loops each excel file in the given Folder
//In order to get the file information from SQLtables command making use of the ODBC connection format
LIB CONNECT TO 'Excel';//DBQ=$(file);
SheetNames:
SQLtables; // Loads all sheet names in the Excel file.
DISCONNECT;
FOR index = 0 to NoOfRows('SheetNames')-1 // Loops for each sheet in the Excel file.
LET sheetName = PurgeChar(PurgeChar(Peek('TABLE_NAME', index, 'SheetNames'), Chr(39)), Chr(36));
if not WildMatch(sheetName,'*xlnm*') then //In case your sheet contains any filter or other
Assessment_Data_Temp:
Load
StudentID,
Set as Assessment.Class,
[Year group] as Assessment.YearGroup,
Subject
From $(file)(ooxml, embedded labels, table is [$(sheetName)]);
ENDIF
NEXT index
DROP TABLE SheetNames;
// NEXT Any help is greatly appreciated.
Getting this to work would be amazing!
Thanks in advance,
Matt
