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

[ganesan7]

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;

[Or · Accepted solution]

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]

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

[ganesan7]

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

[ganesan7]

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]

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