Load specific sheets from multiple excel files

[Anonymous]

Hi!

Can someone help me with my load statement?

I am trying to load mulpiple numbered sheets from multiple files. In each file there can be numbered sheets from 1-12 (12 is max, can be less) and sheets with different kind of names (I don't want to load those). Fields in each of the numbered sheets are exactly the same.

Currently I am working on this script:

LET vStartSheetNumber = 1;
LET vEndSheetNumber = 12;
LET vPath = 'C:\Users\User1\Desktop';

FOR Each vFile in FileList(vPath & '\*.xlsx')

ODBC CONNECT32 TO [Excel Files;DBQ=$(vFile)];

Temp:
LOAD *;
SQLtables;
DISCONNECT;

Data:
LOAD * INLINE [
xxx ];

FOR index = vStartSheetNumber TO vEndSheetNumber
Concatenate(Data)
LOAD
*
FROM [$(vPath)\*.xlsx]
(ooxml, embedded labels, header is 1 lines, table is [$(index)]);
NEXT index

NEXT vFile

Basicaly I have to problems:

1) I am getting this error message for each loaded sheet in the second FOR loop:

Error: File extdata.cpp, Line 2903

Concatenate(Data)

     LOAD

          *

     FROM

     (ooxml, embedded labels, header is 1 lines, table is [1])

How can I get rid of it?

2) When pressing through the errors I can see that each file is loaded multiple times if there are more then 1 file in the folder (if there are 2 files that each file is loaded 2 time, 3 times for 3 files, etc.). Where might be the problem?

Thank you!

[Frank_Hartmann]

see here:

Error: File extdata.cpp, Line 2903

[Colin-Albert]

The problem is that you are using *.xlsx twice in your script.

Once in the for each filelist  command, and again in the Concatenate(data) load.

This is the causing the xls files to load multiple times

Try using this in your script. The trace command will show the file you are loading in the progress window/document log

trace ...Loading data from [$(vFile)];

FOR index = vStartSheetNumber TO vEndSheetNumber

Concatenate(Data)

LOAD

*

FROM [$(vFile)]

(ooxml, embedded labels, header is 1 lines, table is [$(index)]);

NEXT index

[Anonymous]

I already saw this post. The thing I didn't understand from the answer that was given there is how to load specific sheets without specifying?

[Anonymous · Accepted solution]

Thank you for your coment. It was quite helpful. I made a few changes and it seems to be working.

Based on your coment I made to seperate FOR loops instead of  nested FOR loops. That fixed the multiple loading of files. And for error problem I just set ErrorMode to 0 before the second FOR loop and turned it back on after it.

[Kushal_Chawda]

look into this

Dynamically Loading Multiple Excel Files

[Anonymous]

I used your link and Load Multiple excel sheets using For loop as examples.