topic Re: Load specific sheets from multiple excel files in QlikView

Load specific sheets from multiple excel files

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Load specific sheets from multiple excel files

Frank_Hartmann — Thu, 20 Jul 2017 14:31:40 GMT

see here:

Error: File extdata.cpp, Line 2903

Re: Load specific sheets from multiple excel files

Colin-Albert — Thu, 20 Jul 2017 15:25:12 GMT

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

Re: Load specific sheets from multiple excel files

Anonymous — Fri, 21 Jul 2017 06:34:53 GMT

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?

Re: Load specific sheets from multiple excel files

Anonymous — Fri, 21 Jul 2017 07:07:43 GMT

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.

Re: Load specific sheets from multiple excel files

Kushal_Chawda — Fri, 21 Jul 2017 08:00:17 GMT

look into this

Dynamically Loading Multiple Excel Files

Re: Load specific sheets from multiple excel files

Anonymous — Fri, 21 Jul 2017 08:48:51 GMT

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