Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Load excel, loop the worksheet

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.

Tags (1)
1 Solution

Accepted Solutions
michielvandegoo
Valued Contributor

Load excel, loop the worksheet

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

9 Replies
michielvandegoo
Valued Contributor

Load excel, loop the worksheet

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

Not applicable

Load excel, loop the worksheet

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

michielvandegoo
Valued Contributor

Re: Load excel, loop the worksheet

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

Good luck.

Not applicable

Load excel, loop the worksheet

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.

michielvandegoo
Valued Contributor

Load excel, loop the worksheet

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.

Not applicable

Re: Load excel, loop the worksheet

What if multiple excel files has multiple sheets?

Not applicable

Re: Load excel, loop the worksheet

Getting "Bad Zip File" error

michielvandegoo
Valued Contributor

Re: Load excel, loop the worksheet

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

vchuprina
New Contributor II

Re: Load excel, loop the worksheet

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

Community Browser