Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
ganesan7
Partner - Contributor II
Partner - Contributor II

Load multiple excel sheets dynamically without turning off standard mode in Qlik Sense Enterprise.

Hi, everyone. I have a question that needs to be solved.

Qlik Sense version: Qlik Sense August 2021

Qlik Sense Server: 14.28.3

My situation is to load multiple Excel files with different sheet name. The column in all the sheets will be the same. The data is differentiated by sheets. So I need to load the sheet name to get differentiate the data. 

But all the Excel files and their sheets have the same column name structure. So I want to dynamically load all the sheets and concatenate them. I want to do this in Standard Mode for Qlik Sense Enterprise. 

I know there is a way to use ODBC connection and sqltable but that requires to turn off standard mode in Qlik Sense. I do not want to turn off standard mode. 

I have included my old Qlik view script below here, I want to create a similar script for Qlik Sense without turning off standard mode:

Folder:
LOAD * INLINE [
    Folder
    A
    C
    D
];
 
 
FOR i=0 to NoOfRows('Folder')-1
 
    LET vFolder = Peek('Folder', $(i), 'Folder');
 
    FOR EACH file in FileList('\SampleServer\QlikDataSource\$(vFolder)\Excel\CEX\*_CEX_*.xlsx');
 
        ODBC CONNECT TO [Excel Files;DBQ=$(file)];
 
        SheetList:
        SQLtables;
        
        DISCONNECT;
        
        FOR j=0 to NoOfRows('SheetList')-1
        
        LET vSheet = purgeChar(purgeChar(peek('TABLE_NAME', $(j), 'SheetList'), chr(39)), chr(36));
        
        Temp:
        LOAD 
                   Column A,
                   Column B
        FROM
        [$(file)]
        (ooxml, embedded labels, table is [$(vSheet)]);
        
        NEXT j;
        
        drop table SheetList;
 
    NEXT;
 
NEXT i;
 
drop table Folder;

 

Labels (3)
1 Solution

Accepted Solutions
Or
MVP
MVP

As far as I know, there's no other way to do this if the sheet names are dynamic (If they're static, it's easy to do - just spell them out). 

However, if you're able to modify the source, there are multiple alternatives:

* Add a list of the sheet names in your Excel file, load that, and then loop through those to read all the required sheets

* Use a VBA macro in a separate Excel file to create a list of the sheets to load from the relevant Excel file (Note: I have not tested this, but I believe it should work if security on the Excel side doesn't block it)

* Use an automation to split the sheets into separate files and then read all files in a folder using a wildcard or a loop

View solution in original post

6 Replies
Or
MVP
MVP

As far as I know, there's no other way to do this if the sheet names are dynamic (If they're static, it's easy to do - just spell them out). 

However, if you're able to modify the source, there are multiple alternatives:

* Add a list of the sheet names in your Excel file, load that, and then loop through those to read all the required sheets

* Use a VBA macro in a separate Excel file to create a list of the sheets to load from the relevant Excel file (Note: I have not tested this, but I believe it should work if security on the Excel side doesn't block it)

* Use an automation to split the sheets into separate files and then read all files in a folder using a wildcard or a loop

ajaykakkar93
Specialist III
Specialist III

Hi,
Please try this solution hope this works for you
https://community.qlik.com/t5/QlikView-Documents/Loading-Multiple-Excel-Sheets-Dynamically-along-wit...

Please mark the correct replies as Solution. Regards, ARK
Profile| GitHub|YouTube|Extension|Mashup|Qlik API|Qlik NPrinting

ganesan7
Partner - Contributor II
Partner - Contributor II
Author

Hi. Thank you for your input but my issue is for Qlik Sense Enterprise in standard mode.

ganesan7
Partner - Contributor II
Partner - Contributor II
Author

Hi. Thank you for your response. I think your options 1 and 2 might work for my issue. But most probably the user don't want to add new sheet for sheet names. So for now I will keep this open for other response. 

marcus_sommer

There is also another possibility. A xlsx-file is a zip-folder of multiple xml-files (just make a copy of one, rename the extension to zip and open it with the explorer) and from there you could also derive the included sheets (you would need some time and efforts to investigate the data-structure).

So the big question is if you could do the zipping-stuff within the standard mode? I don't know. But if not I could imagine that's easier to enable for such kind of task a controlled batch-job as if it would be to enable external macros to read the sheets. 

ajaykakkar93
Specialist III
Specialist III

This will work in standard mode as you are creating an ODBC CONNECT32 TO an Excel Files which is dynamic & just looping to the sheets inside 

Please mark the correct replies as Solution. Regards, ARK
Profile| GitHub|YouTube|Extension|Mashup|Qlik API|Qlik NPrinting