topic Re: Loading multiple excel files and sheets not working in QlikView

Loading multiple excel files and sheets not working

ingoniclas — Sat, 16 Nov 2024 21:50:43 GMT

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-sheets/m-p/372845

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

Re: Loading multiple excel files and sheets not working

thomaslg_wq — Thu, 22 Nov 2018 13:14:27 GMT

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

Re: Loading multiple excel files and sheets not working

ingoniclas — Thu, 22 Nov 2018 15:14:01 GMT

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

Re: Loading multiple excel files and sheets not working

ingoniclas — Thu, 22 Nov 2018 15:16:02 GMT

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

Re: Loading multiple excel files and sheets not working

marcus_sommer — Thu, 22 Nov 2018 15:43:46 GMT

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

Re: Loading multiple excel files and sheets not working

ingoniclas — Thu, 22 Nov 2018 20:51:44 GMT

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!