Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
ingoniclas
Creator II
Creator II

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

Labels (6)
2 Solutions

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

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

View solution in original post

ingoniclas
Creator II
Creator II
Author

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!

View solution in original post

5 Replies
thomaslg_wq
Creator III
Creator III

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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

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

View solution in original post

ingoniclas
Creator II
Creator II
Author

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!

View solution in original post