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?
Re: Loading multiple excel sheets containing different data in one go using for loop
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.
Load '' as DummyField AutoGenerate 0;
FOR EACH file in FileList('C:\Users\Tamil\Desktop\*.xlsx');
ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];
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));
Load * ,
FileBaseName() as File,
FileName() as File_Name,
'$(vSheetName)' as Sheet_name
From $(file)(ooxml, embedded labels, table is [$(vSheetName)]);