topic Re: Load excel, loop the worksheet in QlikView

Load excel, loop the worksheet

Thu, 22 Mar 2012 07:20:59 GMT

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.

Load excel, loop the worksheet

Michiel_QV_Fan — Thu, 22 Mar 2012 07:54:12 GMT

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)]);

Load excel, loop the worksheet

Thu, 22 Mar 2012 08:08:13 GMT

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

Re: Load excel, loop the worksheet

Michiel_QV_Fan — Thu, 22 Mar 2012 08:24:14 GMT

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

Good luck.

Load excel, loop the worksheet

Thu, 22 Mar 2012 08:38:48 GMT

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.

Load excel, loop the worksheet

Michiel_QV_Fan — Thu, 22 Mar 2012 09:05:08 GMT

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.

Re: Load excel, loop the worksheet

Thu, 23 Jan 2014 14:34:44 GMT

What if multiple excel files has multiple sheets?

Re: Load excel, loop the worksheet

Fri, 23 May 2014 10:26:42 GMT

Getting "Bad Zip File" error

Re: Load excel, loop the worksheet

Michiel_QV_Fan — Mon, 26 May 2014 11:42:59 GMT

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

Re: Load excel, loop the worksheet

vchuprina — Fri, 27 Feb 2015 17:33:24 GMT

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

Re: Load excel, loop the worksheet

alexandermllr — Thu, 24 Oct 2019 11:13:53 GMT

Hello,

// Optional filtering logic to select certain sheets

How can I filter to select certain sheets?
I want to select only the sheets where the second number is a '1'.

Thanks!