Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
ali_hijazi
Partner
Partner

load excel sheet dynamically

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

I can walk on water when it freezes
5 Replies
paola_valenti
Creator
Creator

I have identical problem.

In Multiple excel sheets(Dynamic) load in qliksense

"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
Creator
Creator

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
Creator
Creator

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
Creator
Creator

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
Creator
Creator

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