topic Re: Wildcard in sheet id when loading excel file? in QlikView

Wildcard in sheet id when loading excel file?

Thu, 23 Apr 2015 00:54:35 GMT

Hi:

I know that we can use wildcard in file name to load in multiple excel files in Qlikview.

I have multiple excel files each has one sheet only and the sheet name share similar pattern.

How can I load in those files with single script?

Can I use a wildcard in the sheet id as well? I cannot make it to work.

Thank you.

Paco

Re: Wildcard in sheet id when loading excel file?

sunny_talwar — Thu, 23 Apr 2015 02:05:48 GMT

What better way to know then to test it, right? I tried it with the wild card and it didn't seem to work for me.

Re: Wildcard in sheet id when loading excel file?

Oleg_Troyansky — Thu, 23 Apr 2015 02:08:23 GMT

Unfortunately, the regular Table File load doesn't allow for the wildcard in the sheet ID. I see two possible options:

1. You can standardize the Sheet IDs and settle on a single name

2. You can load Excel data using an ODBC driver for Excel. Then, you can use the ODBC command SQLTABLES to query the spreadsheet for the existing Table (Sheet) names and load the corresponding sheet. The script is slightly more complex, however it can cover multiple sheets per spreadsheet and allow different sheet names.

cheers,

Oleg Troyansky

www.masterssummit.com - take your QlikView skills to the next level!

Re: Wildcard in sheet id when loading excel file?

danieloberbilli — Thu, 23 Apr 2015 02:15:38 GMT

Just as an idea - but didnt had any success in my case (what might have had other reasons):

try to put the whole string in a variable like you would do with the file name of loading multiple excel files

e.g. for files you would use: FOR each vSourceFile in FileList (vSourcePath)

so you could try something like:

SET vSheet = '(biff, embedded labels, header is 9 lines, table is [Sheet *])';

and later in the script something like:

...

FROM $(vSourceFile)

$(vSheet);

Re: Wildcard in sheet id when loading excel file?

Ralf-Narfeldt — Thu, 23 Apr 2015 05:35:40 GMT

Solution 2. in Oleg's post is described with examples in this document:

Loading Multiple Excel Sheets Dynamically along with file name and sheet name