Load excel, loop the worksheet

[#1]

Hi,

Anyone can extend help? I have one excel file, contains different sheets. Each sheet represent the department. How to load this in loop?

Please see attachment.

And will it also be possible to load multiple file using * asterisks? But at first I want to load all sheets contained.

Thanks. Bill.

[Michiel_QV_Fan · Accepted solution]

We had some similar challenge and solved it with this script:

for each file in FileList('D:\Qlikview\communitie\Copy of Record 2012.xlsx')

   ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

tables:

SQLtables;

DISCONNECT; // Don't need ODBC connection anymore

/*

One of the fields loaded by SQLtables is "TABLE_NAME". This field will contain the sheet name.

We will loop through this set of sheet names.

*/

FOR i = 0 to NoOfRows('tables')-1

       /*

       Assign the TABLE_NAME to the variable "sheetName".

       TABLE_NAMEs that contain spaces will be enclosed in single quotes.

       The purgeChar function will remove any quotes AND dollar signs.

       */

       LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));

       // Optional filtering logic to select certain sheets

            Table_with_data:   

             // Now that we have a sheet name, a standard biff (Excel) load can be used.

             LOAD *,

                    '$(sheetName)' as Sheet  // Optionally, the sheetName value may be loaded as a field

             FROM $(file) (ooxml, embedded labels, table is [$(sheetName)]);

NEXT

[#3]

Hi, would you mind to attach the qvw file? Thanks.

[Michiel_QV_Fan]

Not at all. You have to edit the connection string to match your file location.

Good luck.

[#5]

Yes, I did it. But I still have one more problem. I have two records or more, Record 2012 and Record 2013 and soon... Will it be possible to loop the ODBC? Just like using * asterisks again?

You're doing greate huh, you did run it! Good script at all.

[Michiel_QV_Fan]

I tried with * but that only extracts 1 file, and in a second test Qlikview crashed.

Quick work around is to repeat the load in a second load script and concatenate the files, or store them in separate QVD's.

If your history doesn't change then there will be no need to reload all files each time. Keep your history in separate QVD's and only load the active period. Then you will only have to change the source file in your script each year.

Add 'Filename() as Source' (for instance) to identify the source file.

[#7]

What if multiple excel files has multiple sheets?

[#8]

Getting "Bad Zip File" error

[Michiel_QV_Fan]

I don't know this error message. Can you post screen shots? You are loading xls(x) files, not zipped files?

[vchuprina]

Hi,

////////

We will loop through this set of sheet names.

*/

FOR i = 0 to NoOfRows('tables')-1

/////////

If we loop through this set of sheet names, why i cannot control number of sheets, which I want to load?

For example:

If I have 13 sheets in xls file, that means

NoOfRows('tables')-1 = 13

So if I want to load only 11 sheet I should change script to following:

FOR i = 0 to NoOfRows('tables')-3.

But this not work.

Could somebody tell how work this loop?

And how we can control number of required to load sheets?

Regards,

Vitaliy