Load data in Qlik Sense from Excel file not knowing exact Excel sheet name

[paola_valenti]

I need to load in Qlik Sense some data from Excel File.

I know this file's name (is named after today date), but don't know sheet's name (I only know sheet's name starts with word "daily").

It's not possible to insert jolly character in loading script code (for instance, you can't write:  table is [daily*]).

In Qlik View was simple to find Excel File's Sheet name. There are lots of posts about this, for instance:

Use OLEDB to get Excel sheet names

Loading Multiple Excel Sheets Dynamically along with file name and sheet name

Unfortunatly in Qlik Sense is not so simple, because this code line "ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];" gives error.

In community, I find out some open post in which people ask how to solve. It appears that the only possibility is a workaround, modifying file settings.ini. Here some post I found:

Multiple excel sheets(Dynamic) load in qliksense

Connect to Excel Files in Sense to load multiple sheets

I can't apply this solution, because I can't change file settings.ini.

Is there some other way to find sheet name in the excel and load data from there?

Paola

[wade12]

for i = 1 to $(vNoOfSheets)

load a, b, c

From MyExcel.xls

(biff, embedded labels, table is [Sheet$(i)]);

Next i;

If the excel file has customised sheet names then use this code:

SET vSheet1 = 'FirstSheet';

SET vSheet2 = 'SecondSheet';

SET vSheet3 = 'ThirdSheet';

FOR j = 1 to 3

LOAD a, b, c

From MyExcel.xls

(biff, embedded labels, table is [$(Sheet$(j))]);

Next j;

[paola_valenti]

I'm not looking for loading more than a sheet.

I want to load ONE sheet from ONE Excel file.

Every day, on reload, Excel file loaded will be different (it will be the file of that day, named YYYYMMDD.xlsx).

This file can have one or more sheet, but only one sheet is named "dailyXXX", where XXX is a code.

I can't know "XXX" value before, so I need to have a way to generical identificate sheet name or a way to list all sheets in the excel and find the only one whose name start with "daily" word.

Paola

[wade12]

for j = 1 to 1

[paola_valenti]

And what if sheet "dailyXXX" is the second or the third in the excel file?

[wade12]

ok, so, load sheet names dynamically, then just strip out where sheetname field contains "daily".

please see this link:

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

[paola_valenti]

Yes, this is exactly the second link I quoted.

It apparently works only in Qlik View.

Do you know a way to have same result in QLIK SENSE (without changing settings.ini)?

Paola

[marcus_sommer]

Are there special reasons why you couldn't change the settings.ini in regard to the reload-mode. I know it's regarded as a potential security risk but I'm not so sure that the potential risks really justify the restrictions from it. More background could you find here: Qlik-Sense-Legacy-mode.

If you really couldn't change this setting there is no way from the Sense side to get this information respectively to trigger some macros or a zip-tool which would be useful for it - you may trigger such task separately with a windows task.

Alternatively you could try to ensure that your sheet is always the first sheet in the application - which would be automatically grabbed if no sheet is specified.

Another method might be to combine the suggestion from Wade with looping through n generic created sheet-names and ERRORMODE.

- Marcus

[paola_valenti]

Risk matter is not the problem, but I'm not allowed to change file because I'm not administrator of it.

I'm shocked about the fact that Qlik Sense hasn't forecasted a way to import data from a variable sheet name, considering that in Qlik View it was so easy to do this.

I have no control on file source: I have a read-only access.

I can't change sheet order or be sure of the code after "daily" word.

So this are not solutions to me.

[marcus_sommer]

That you couldn't change this file doesn't mean that's not changeable - just make a request to your IT stuff which is responsible for the Sense adminstration. It might not be a daily business to request administrative changes but it's definitely not uncommon.

- Marcus