Multiple excel sheets(Dynamic) load in qliksense

[jayaseelan]

Hi experts,

I need to load all data in multiple excel sheet without mentioning the sheet name in the script.

anyone please guide me t complete this task.

i have attached the sample data.

Thanks,

[OmarBenSalem]

please refer to this:

Load Multiple excel sheets using For loop

[jayaseelan]

Hi Omar,

Thanks for your valuable reply.

Can you please load the sample file which i have attached using your query.

[OmarBenSalem]

First of all, disable the standard Mode so u can enter full path :

in desktop :

1. Open Settings.ini in a text editor.
2. Change StandardReload=1 to StandardReload=0.
3. Save the file and start Qlik Sense Desktop.

in entreprise:

Editing an engine ‒ Qlik Sense

then load one sheet of ur excel file (so we can copy the (biff, embedded labels, table is [1$]); for a later use)

and then comment it:

// LOAD

//     ID,

//     NAME

// FROM [lib://data/Data.xls]

// (biff, embedded labels, table is [1$]);

now use this : change the path to ur excel file (u can directly enter Data.xls instead of *.xls)

FOR EACH file in FileList('C:\Users\OmarBenSalem\Desktop\Data\*.xls');

ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

tables:

SQLtables;

DISCONNECT;

FOR i = 0 to NoOfRows('tables')-1

LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));

table:

LOAD

   *

FROM [lib://data/Data.xls]

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

NEXT i

Next

result:

[jayaseelan]

can you please share the above qvf file?

[OmarBenSalem · Accepted solution]

Here we go

[OmarBenSalem]

jayaseelan have my intervention helped you?

[Aditya_Chitale]

counttable:
LOAD
shcount
FROM [lib://mycon/task 22-7-19.xls]
(biff, embedded labels, table is demo$);


mytable:

load shcount
Resident counttable;
let vTrack=2;
let vCount=Num(Peek('shcount',0,counttable));

For index= vTrack to vCount

load * FROM [lib://mycon/task 22-7-19.xls]
(biff, embedded labels, table is @$(index));

next

[adam1988]

Hi Omar,

any chance there is a solution for enterprise WITHOUT the need to turn off standard Mode? 

It is not feasible in my environment due to related security risk.

Thanks.

[ganesan7]

Hi Adam. 

I have the same situation here. I need to load multiple Excel files with different sheet names. 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. Have you found any solution? 

Thank you. I also provide my sample Qlik View script below for reference. I have to do a similar script in Qlik Sense Enterprise.

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;