Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Wildcard in sheet id when loading excel file?

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

Tags (3)
4 Replies

Re: Wildcard in sheet id when loading excel file?

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.

MVP
MVP

Re: Wildcard in sheet id when loading excel file?

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!

danieloberbilli
Valued Contributor II

Re: Wildcard in sheet id when loading excel file?

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

Employee
Employee

Re: Wildcard in sheet id when loading excel file?

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

Community Browser