Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

jayaseelan
Contributor 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
Esteemed Contributor

Re: Multiple excel sheets(Dynamic) load in qliksense

Here we go

6 Replies
OmarBenSalem
Esteemed Contributor

Re: Multiple excel sheets(Dynamic) load in qliksense

jayaseelan
Contributor 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.

OmarBenSalem
Esteemed Contributor

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

jayaseelan
Contributor III

Re: Multiple excel sheets(Dynamic) load in qliksense

can you please share the above qvf file?

OmarBenSalem
Esteemed Contributor

Re: Multiple excel sheets(Dynamic) load in qliksense

Here we go

OmarBenSalem
Esteemed Contributor

Re: Multiple excel sheets(Dynamic) load in qliksense

jayaseelan have my intervention helped you?

Community Browser