topic Re: Loading multiple excel sheets containing different data in one go using for loop in QlikView

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

atiyaharem — Wed, 25 Nov 2020 16:16:04 GMT

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.

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

atiyaharem — Mon, 05 Mar 2018 09:21:23 GMT

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

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

tamilarasu — Tue, 06 Mar 2018 04:20:02 GMT

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;

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

amit_saini — Tue, 06 Mar 2018 04:37:06 GMT

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,