load excel sheet dynamically

[ali_hijazi]

Hello I'm working on Qlik Sense (latest version)
I want to connect to an excel file and load all sheets with specific name
how can I accomplish this?
I got an old script from Qlik View 

FOR EACH file in FileList('C:\Users\ut2eq5\Desktop\Data\*.xls');

//In order to get the file information from SQLtables command making use of the ODBC connection format
ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

tables:
SQLtables;
DISCONNECT;

the line in orange fails in Qlik Sense because this function needs the full path and not a path that starts with lib://

Kindly advise

[paola_valenti]

I have identical problem.

In Multiple excel sheets(Dynamic) load in qliksense, OmarBenSalem said:

"First of all, disable the standard Mode so u can enter full path :

in desktop :

1. Open Settings.ini in a text editor.
2. Change StandardReload=1 to StandardReload=0.
3. Save the file and start Qlik Sense Desktop.

in entreprise:

Editing an engine ‒ Qlik Sense "

but I can't modify Settings.ini or enterprise.

Is there nothing else you can do to have Excel sheet name's list?

Paola

[wayne-a]

Hi, I just created another data connection that is the location of where all the excel files are stored.  I use a normal load statement and the end looks like this.  The "table is [For Upload]" part specifies the worksheet name.  It load that worksheet from each excel file.

FROM [lib://Budget2020/2020 Budget Template*.xlsx]
(ooxml, embedded labels, header is 2 lines, table is [For Upload]);

[paola_valenti]

I know Excel file name, but I don't know sheet name in the Excel file.

So I like to have a variable in which is stored sheet name.

In your example, sheet is "For Upload" (ooxml, embedded labels, header is 2 lines, table is [For Upload]);

But my excel can have a sheet named like: "Daily 1" or "Daily 2" etc.

I need to read excel file in order to know all sheet name, then I can load data only from sheet named "Daily"+a number.

Any idea?

Paola

[wayne-a]

Hi, ok didn't realize you didn't know the name.  I found this in the community for dynamic sheet names.  It is a Qlik View solution but the scripting generally works in Qlik Sense as well.

https://community.qlik.com/t5/QlikView-Documents/Loading-Multiple-Excel-Sheets-Dynamically-along-wit...

[paola_valenti]

Like you said, is a Qlik View solution. I used it many times in Qlik View.

Unfortunatly, it dosn't work in Qlik Sense, because ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

give an error.

Please try, if you can fix it.

Paola