Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Partner
Partner

Re: Multiple excel sheets(Dynamic) load in qliksense

8 Replies
Highlighted
Partner
Partner

Re: Multiple excel sheets(Dynamic) load in qliksense

Highlighted
Creator III
Creator III

Re: Multiple excel sheets(Dynamic) load in qliksense

Hi Omar,

Thanks for your valuable reply.


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

Highlighted
Partner
Partner

Re: Multiple excel sheets(Dynamic) load in qliksense

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

Highlighted
Creator III
Creator III

Re: Multiple excel sheets(Dynamic) load in qliksense

can you please share the above qvf file?

Highlighted
Partner
Partner

Re: Multiple excel sheets(Dynamic) load in qliksense

Highlighted
Partner
Partner

Re: Multiple excel sheets(Dynamic) load in qliksense

jayaseelan have my intervention helped you?

Highlighted
Contributor III
Contributor III

Re: Multiple excel sheets(Dynamic) load in qliksense

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

Highlighted
Contributor II
Contributor II

Re: Multiple excel sheets(Dynamic) load in qliksense

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.