Loading Multiple Excel Files and their sheets dynamically

[#1]

Hi,

I'm trying to load through multiple spreadsheets and all their tabs. My current script is as follows:

//To read each file from the specified folder
FOR EACH file in FileList('C:\Users\huseyin.azizcan\Desktop\Envirisearch\Poland Reach\*.xlsx');


//In order to get the file information from SQLtables command making use of the ODBC connection format 
ODBC CONNECT TO [Enviresearch - MS EXCEL ODBC CON;DBQ=$(file)];


tables: 
SQLtables; 
DISCONNECT;


FOR i = 0 to NoOfRows('tables')-1
LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));


Table:
Load 
  *,
  FileBaseName()as File,
  FileDir() as Dir,
  FileName() as [File Name],
  '$(sheetName)' as Excel_Workbook_Name,
    rowno() as [Row Number]
From $(file)(ooxml, embedded labels, header is 1 lines, table is [$(sheetName)]);


NEXT i


Next

When there is a single xlsx file in the Filelist location, everything works fine. The script connects to the excel file and extracts all data from all tabs within it. The moment, I place a second excel file (exact same layout and fields), I get the below error:

I read the this post: How to solve this error 'File extdata.cpp, Line 2754'

And the solution there doesnt seem to make sense to me.

Can someone please help me out?

Thanks

[#2 · Accepted solution]

I solved it. I had to drop the 'tables' table after the second for loop. The second spreadsheet was concatenating onto the first spreadsheet. This cause for the incorrect sheet name to be added to my sheetName variable.

See below for correct script, just in case someone else needs it.

1. //To read each file from the specified folder 
2. FOR EACH file in FileList('C:\Users\huseyin.azizcan\Desktop\Envirisearch\Poland Reach\*.xlsx'); 
3. //In order to get the file information from SQLtables command making use of the ODBC connection format  
4. ODBC CONNECT TO [Enviresearch - MS EXCEL ODBC CON;DBQ=$(file)]; 
5.    
6. tables:  
7. SQLtables;  
8. DISCONNECT; 
9.  
10. FOR i = 0 to NoOfRows('tables')-1 
11. LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36)); 
12.  
13. [Table]: 
14. Load  
15.   *, 
16.   FileBaseName()as File, 
17.   FileDir() as Dir, 
18.   FileName() as [File Name], 
19.   '$(sheetName)' as Excel_Workbook_Name, 
20.     rowno() as [Row Number] 
21. From $(file)(ooxml, embedded labels, header is 1 lines, table is [$(sheetName)]); 
22.  
23. NEXT i 
24. drop table "tables";
25. Next