Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm trying to load multiple sheets from an excel document. I know there are already many tutorials available. I checked out a lot, but still I don't managed to get it fixed. I tried to replicate these steps from a youtube video (Qlikview FOR EACH... Load Multiple Excel Sheets by RFB 200 - YouTube). I have the feeling that I'm close.
The issue at hand is:
The script only loads one sheet (the first sheet). When i check the script with the debugger it tells me that it can't find the next value because its a <null>. But it definitly has a valid name. I think it's a quote / '[]' issue.
Anyone got an idea?
Thanks in advance guys.
ODBC CONNECT TO [Excel Files;DBQ=C:\Users\Stefan\Downloads\Diensten_2015.xlsx];
Let vfile = 'C:\Users\Stefan\Downloads\Diensten_2015.xlsx';
exceltables:
SQLTABLES;
DISCONNECT;
For i = 0 to NoOfRows('exceltables')-1
Let vExcelSheets = purgechar(peek('TABLE_NAME', i, 'EXCELTABLES'),chr(36));
LOAD *,
'$(vExcelSheets)' as Sheet
FROM
$(vfile)(ooxml, embedded labels, table is [$(vExcelSheets)]);
next
Hi Settu,
This is currently my script:
LET vFolder = 'C:\Users\Stefan\Downloads\Qlik\Sheets\';
FOR EACH vFile IN FILELIST('$(vFolder)*.xlsx')
ODBC CONNECT TO [Excel Files;DBQ=$(vFile)];
tables:
SQLtables;
DISCONNECT;
LET vSheetCount = NoOfRows('tables');
FOR iSheet = 0 to $(vSheetCount) -1
LET vSheetName = replace(purgeChar(purgeChar(peek('TABLE_NAME', $(iSheet), 'tables'), chr(39)), chr(36)),'#','.');
[$(vSheetName)]:
LOAD *,1 AS SampleCount,
'$(vSheetName)' as Sheet,
'$(vFile)' as FileName
FROM [$(vFile)]
(ooxml, embedded labels, table is [$(vSheetName)]);
NEXT iSheet
DROP TABLE tables;
Next vFile
Hi Jonathan,
Great collection of articles. Thanks!
Hi Semper,
Yes you are right. based one your data, if you load it as normal, it will create synthetic tables.
I just created the script using cross load.. i think now the data looks fine.
Directory;
LET vFolder = 'C:\Users\Stefan\Downloads\Qlik\Sheets\';;
FOR EACH vFile IN FILELIST('$(vFolder)*.xlsx')
ODBC CONNECT32 TO [Excel Files;DBQ=$(vFile)];
tables:
SQLtables;
DISCONNECT;
LET vSheetCount = NoOfRows('tables');
FOR iSheet = 0 to $(vSheetCount) -1
LET vSheetName = replace(purgeChar(purgeChar(peek('TABLE_NAME', $(iSheet), 'tables'), chr(39)), chr(36)),'#','.');
AllData:
CrossTable(WeekYear, Data,2)
LOAD '$(vSheetName)' as SheetName,*
FROM [$(vFile)]
(ooxml, embedded labels, table is [$(vSheetName)]);
NEXT iSheet
DROP TABLE tables;
Next vFile
Final:
LOAD *,
F1 as Dimension,
Right(WeekYear,4) as Year,
MakeWeekDate(Right(WeekYear,4),mid( WeekYear, index( WeekYear, ' ', 2 ) -2, 2 )) as WeekDate,
mid( WeekYear, index( WeekYear, ' ', 2 ) -2, 2 ) as WeekNum,
MonthName(MakeWeekDate(Right(WeekYear,4),mid( WeekYear, index( WeekYear, ' ', 2 ) -2, 2 ))) as Month
Resident AllData;
DROP Field F1;
DROP Table AllData;
It worked! Thank you very much Settu! I'm very happy with it!