Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ali_hijazi
Partner - Master II
Partner - Master II

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