Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register 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
Partner
Partner

8 Replies
OmarBenSalem
Partner
Partner

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

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

Here we go

OmarBenSalem
Partner
Partner

jayaseelan have my intervention helped you?

Aditya_Chitale
Creator
Creator

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.