Loading multiple excel files and sheets not working

[ingoniclas]

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

[thomaslg_wq]

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]

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]

Something seems to be wrong with the looping throught the worksheets.

[marcus_sommer · Accepted solution]

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 · Accepted solution]

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!