Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
ingoniclas
Contributor

Loading multiple excel files and sheets not working

Hi there,

 

I have a couple of excel files with 4 worksheets in each file that I need to load in QlikView. While searching for solutions in the community I came uop with the script below, taken form the following sources:

 

http://qlikviewcookbook.com/2008/09/loading-multiple-excel-sheets/

https://community.qlik.com/t5/QlikView-Scripting/Loading-from-multiple-Excel-files-and-multiple-shee...

 

However, for some reason it doesn't work the way it's supposed to be: I can load the table 'Sheets' with the ODBC connection, but for some reason only the first worksheet of each file gets loaded 4 times, instead of all the 4 worksheets. I couldn't figure out why this is since the script seems to be ok to me. Any ideas??? Thanks.

 

For Each vFile in FileList('MyDirectory\*.xlsx')
     ODBC CONNECT32 To [Excel Files;DBQ=$(vFile)];
     Sheets:
     SQLTABLES;
     DISCONNECT;
     For i = 0 To NoOfRows('Sheets')-1
    for each vSheet in Peek(‘TABLE_NAME’, i, ‘Sheets’)
         table1:
          LOAD  A, B, C …
          From [$(vFile)]
          (ooxml, no labels, table is [$(vSheet)]);

      Next
Next

2 Solutions

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Loading multiple excel files and sheets not working

Just put some TRACE statements with your loop-variables within the loops and then you could see within the progress-window or within the document-log what happens in each iteration. With it it shouldn't be very difficult to detect what's wrong.

- Marcus

ingoniclas
Contributor

Re: Loading multiple excel files and sheets not working

Thanks Marcus! With the help of the log file and the TRACE statement I finally figured what was causing the problem: The worksheet names were pulled surrounded with single quotes and $ sign like 'Jun 18$'. Therefore, the variable statement let vSheet=... was empty, and so only the first (default) worksheet was pulled in. The solution was to change the variable like this:

LET vSheet=PurgeChar(PurgeChar(Peek('TABLE_NAME', $(i), 'Sheets'), chr(39)), '$');

Now it works like a charm!
5 Replies
thomaslg_wq
Contributor III

Re: Loading multiple excel files and sheets not working

You have 3 "For" and only 2 "Next".

I think you should just :

1 - change this part :
"for each vSheet in Peek(‘TABLE_NAME’, i, ‘Sheets’)" ===> inconsistant
with
"let vSheet=Peek(‘TABLE_NAME’, $(i), ‘Sheets’)"===> define the right sheet for each i (row of sheets table)

2 - Add this between the two "next" :
"Drop table Sheets;" ==> because you add new sheets value each time you connect to vFile without deleting the former ones

Hope this helps !

Regards,
Thomas
ingoniclas
Contributor

Re: Loading multiple excel files and sheets not working

Hi Thomas,

thanks for your reply. However, it didn't solve my problem. Having applied your suggestions the script still keeps drawing the first sheet 4 times instead of drawing the 4 different sheets consecutively:-(

 

ingoniclas
Contributor

Re: Loading multiple excel files and sheets not working

Something seems to be wrong with the looping throught the worksheets.
MVP & Luminary
MVP & Luminary

Re: Loading multiple excel files and sheets not working

Just put some TRACE statements with your loop-variables within the loops and then you could see within the progress-window or within the document-log what happens in each iteration. With it it shouldn't be very difficult to detect what's wrong.

- Marcus

ingoniclas
Contributor

Re: Loading multiple excel files and sheets not working

Thanks Marcus! With the help of the log file and the TRACE statement I finally figured what was causing the problem: The worksheet names were pulled surrounded with single quotes and $ sign like 'Jun 18$'. Therefore, the variable statement let vSheet=... was empty, and so only the first (default) worksheet was pulled in. The solution was to change the variable like this:

LET vSheet=PurgeChar(PurgeChar(Peek('TABLE_NAME', $(i), 'Sheets'), chr(39)), '$');

Now it works like a charm!