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: 
atiyaharem
Contributor
Contributor

Loading multiple excel sheets containing different data in one go using for loop

I have an excel workbook with 20 worksheets. Each worksheet has a different name (i.e. Product,customer,sales, etc.) All the worksheets are in the different format and contain the different fields. How do I create a loop to load all worksheets in the excel workbook in Qlikview?


I don't want to load sheet one by one manually.

3 Replies
atiyaharem
Contributor
Contributor
Author

Please someone help, looking for an answer for long, but i am getting solution for the scenarion when the sheet have same data format

tamilarasu
Champion
Champion

Hi Atiya,

The below script consolidate all excel files and sheets into singe table. If you have any issues with the below code, I would suggest you to attach sample excel files and post expected output. Good luck.

Data:

Load '' as DummyField AutoGenerate 0;

FOR EACH file in FileList('C:\Users\Tamil\Desktop\*.xlsx');

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

Temp:

SQLtables;

DISCONNECT;

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

LET vSheetName = PurgeChar(Peek('TABLE_NAME', i, 'Temp'), chr(36));

LET vSheetName = Replace(vSheetName,chr(39) & chr(39),chr(39));

Concatenate (Data)

Table:

Load * ,

FileBaseName()   as File,

FileName()       as File_Name,

'$(vSheetName)'  as Sheet_name

From $(file)(ooxml, embedded labels, table is [$(vSheetName)]);

NEXT i

Drop table Temp;

Next

DROP Field DummyField;

amit_saini
Master III
Master III

Hi Atiya,

See below:

Code to Access all files in directory (subdirectories)

SUB DoDir (Root)

FOR each File in filelist(Root& '\*.xls')

Tab1:

LOAD <<Field Names>>

FROM

[$(File)]

(biff, embedded labels, table is <<Table name>>$);

NEXT File

FOR each Dir in dirlist (Root&'\*')

CALL DoDir(Dir)

NEXT Dir

END SUB

CALL DoDir('Your Directory');

Code to Access All sheets in a Excel work book

Directory;

for a=1 to 3

LOAD employee

FROM

Looping\constructs1.xlsx // this is the excel sheet name

(ooxml, embedded labels, table is Sheet$(a));// $ sign allows to access the value of a

Next

Thanks,

AS