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: 
jayaseelan
Creator III
Creator III

Multiple excel sheets(Dynamic) load in qliksense

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,

1 Solution

Accepted Solutions
OmarBenSalem

9 Replies
OmarBenSalem

jayaseelan
Creator III
Creator III
Author

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:

Capture.PNG

jayaseelan
Creator III
Creator III
Author

can you please share the above qvf file?

OmarBenSalem

Here we go

OmarBenSalem

jayaseelan have my intervention helped you?

Aditya_Chitale
Specialist
Specialist

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
Contributor II
Contributor II

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
Partner - Contributor II
Partner - Contributor II

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;