Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Please someone help, looking for an answer for long, but i am getting solution for the scenarion when the sheet have same data format
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;
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