Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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/
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
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
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:-(
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