Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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

2 Solutions

Accepted Solutions
Highlighted
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

Highlighted
Creator II
Creator II

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

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:-(

 

Highlighted
Creator II
Creator II

Something seems to be wrong with the looping throught the worksheets.
Highlighted
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

Highlighted
Creator II
Creator II

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