Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Loading Multiple Excel Files and their sheets dynamically

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:

Capture.PNG

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

1 Solution

Accepted Solutions
Not applicable
Author

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
  24. drop table "tables";
  25. Next 

View solution in original post

1 Reply
Not applicable
Author

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
  24. drop table "tables";
  25. Next