topic Re: load excel sheet dynamically in App Development

load excel sheet dynamically

ali_hijazi — Mon, 15 Jul 2019 08:45:26 GMT

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

Re: load excel sheet dynamically

paola_valenti — Mon, 23 Sep 2019 15:13:22 GMT

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 :

Open Settings.ini in a text editor.
Change StandardReload=1 to StandardReload=0.
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

Re: load excel sheet dynamically

wayne-a — Tue, 24 Sep 2019 12:43:54 GMT

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

Re: load excel sheet dynamically

paola_valenti — Tue, 24 Sep 2019 14:40:58 GMT

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

Re: load excel sheet dynamically

wayne-a — Tue, 24 Sep 2019 15:17:15 GMT

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-with-file-name/ta-p/1481095

Re: load excel sheet dynamically

paola_valenti — Tue, 24 Sep 2019 15:20:58 GMT

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