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

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

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

Labels (3)
14 Replies
paola_valenti
Creator
Creator
Author

I don't think is a good thing ask this and in general I don't think is the correct solution.

If I have to use an expedient, I prefer not to involve IT to change settings.

I will have a copy of my file with R and standardize sheet name so that could be loaded.

kingsley101
Contributor III
Contributor III

I may have found a solution. If you only have one sheet per excel file, then load the file as you wish but remove the part that defines the table (everything in bold and italics):


LOAD *
FROM [XXX.xlsx]
(ooxml, embedded labels, table is [Sheet1]);

 

I haven't found a solution for multiple sheets per excel file yet.

 

Thanks

Kingsley

paola_valenti
Creator
Creator
Author

Thank you, I didn't realize there was this loophole!

Unfortunatly, my file can have more than one sheet, so I hope in some other solution.

Paola 

tarteflambee
Contributor II
Contributor II

Hi @paola_valenti 

Sorry for digging this out again,

Have you been able to found any solution/workaround ? I think we are facing the exact same need. 

In charge of technical solutions for business users, one of my goals is to advise simple & efficient solution to consume and transform data. As we're forced to used excel spreadsheets I'd like to avoid the complex workarounds for big project / impacting apps with tons of inputs. Hope that this basic functionality has been solved! Any news ?

paola_valenti
Creator
Creator
Author

Unfortunately no news.

As far as I know nothing change since I opened this question.

I am glad to know that is not a problem only for me.

I will let the post open in hope someone can find the solution and share with everyone.

Paola