Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 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 :
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
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]);
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
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.
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