Load more than one Excel sheet dynamically

[Bel01234]

Hi,
I'm trying to load all the sheets from an Excel file into Qlik Cloud. These sheets have different names, and users keep adding new ones. So, I need logic that allows me to dynamically read all the sheets in the file.

The Excel file is stored in a shared OneDrive folder. How can I do this? I know it used to be solved with OLEDB CONNECT32, but Saas doesn't allow it.

Thanks!

Hola, estoy intentando leer todas las hojas de un excel en Qlik Cloud. Esas hojas tienen diferentes nombres y el usuario va agregando nuevas. Asi que necesito una logica que me permita leer dinamicamente todas las hojas del archivo. 

El archivo excel esta alojado en una carpeta compartida de OneDrive. Saben como resolverlo?

Se que antes se resolvia con OLEDB CONNECT32, pero Saas no lo permite.

Gracias!

Gracias

[Andrea_Bertazzo]

hi @belu,

To load Excel sheets from OneDrive in Qlik Cloud, you can use the Qlik OneDrive Connector. This connector allows you to stream data directly into your app from OneDrive.
You can:

-Connect to OneDrive through the Add data option or Data load editor in Qlik Sense.
-Authenticate with your OneDrive credentials to generate an authentication code.
-Use the "Items" table to retrieve a list of items (including Excel files) from a specific drive and folder.
-Select the desired Excel files to load into your Qlik Sense app.

The OneDrive connector treats remote data files as if they were local, allowing you to select and load data seamlessly. This method uses Qlik's built-in connector to access OneDrive data directly.
You can find more info in this article.

[Bel01234]

Hi Andrea, thank you for your response. 

My issue isn’t reading Excel sheets from OneDrive — I’m already doing that through the connector. The challenge is doing it dynamically. I don’t want to create a separate connection for each sheet. Instead, I want Qlik to automatically detect when a sheet is renamed or when a new one is added, and load those changes without requiring updates to the script.

I need a logic that allows me to read all the sheet names dynamically and then load the corresponding data file. Is there a recommended approach for this?

[stevedark · Accepted solution]

Hi @Bel01234 

You are absolutely correct about being able to use an ODBC connection to a spreadsheet to be able to get a list of 'tables' in the connection which relates to each sheet. You are also correct that this isn't an option in Qlik Cloud.

Unfortunately I believe that the only workable route is to have an index sheet at the front of the spreadsheet which users have to keep up to date with the sheet names as they enter new sheets.

I would expect that there would be a way with clever Excel macros to have a trigger that rebuilds the list of sheets on that index, either periodically or when a button is clicked. That would be a question for an Excel forum, not a Qlik one. Every time I have had to do similar though users have been disciplined enough to enter their new sheets on the list (or not get their data).

Getting users to be consistent about columns and header rows on multiple Excel sheets, that is a totally other matter!

Good luck.

Steve

[marcus_sommer]

In addition to the hints from @stevedark the xlsx is only a zip-file with an own extension. If you de-zip the file and going to xl\workbook.xml you could fetch the sheet-names from there. I don't know if it's supported in SaaS to this but it might be already triggered in beforehand. 

[Bel01234]

Thanks, everyone. I solved it with Excel. There's a button that rebuilds the sheet list in the index, just like Steve said.