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 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;
try replacing the Let with
Let vExcelSheets = left(Peek('TABLE_NAME', i, 'EXCELTABLES'), len(Peek('TABLE_NAME', i, 'EXCELTABLES'))-1);
this is a full load many files, many sheets
DIRECTORY;
Table:
load '' as field autogenerate 0;
For Each vFile in FileList('excel load files load sheets*.xlsx')
trace file=$(vFile);
ODBC CONNECT32 To [Excel Files;DBQ=$(vFile)];
Sheets:
SQLTABLES;
DISCONNECT;
For i = 0 To NoOfRows('Sheets')-1
Let vSheet = PurgeChar(left(Peek('TABLE_NAME', i, 'Sheets'), len(Peek('TABLE_NAME', i, 'Sheets'))-1), chr(39) & '$');
trace $(vSheet);
concatenate(Table)
LOAD '$(vSheet)' as [Tab Name],
*
From [$(vFile)]
(ooxml, embedded labels, table is [$(vSheet)]);
Next;
DROP Table Sheets;
NEXT;
Just replacing unfortunately doesn't work. It stills gives me only the first result of my sheets.
Hi,
Try this..
LET vFolder = ''C:\Users\Stefan\Downloads\';
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)),'#','.');
[$(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
This seems to be working. When I follow the steps in the 'executing script window' the sheet are properly fetched. Unfortunately i ran in some trouble in this fase. Al the lines are fetched but the scripts doesn't finish. It keeps running, without results. The windowns doesn't closes aswell.
I think that the script hastrouble creating synthethic tables. For every field / column a relation is created. Is there any way I can prevent this or check this? Perhaps concatenating the table could work, but i'm not sure how to execute that correctly.
Would you mind helping me?
Gr,
Stefan
Sure. Is it possible to provide the exact script you are working and sample of excel files ?
If the fields in the sheets are not identical, you will land up with multiple sheets, and a mess of synthetic keys (ie composite keys). This is not what you want. But the solution will depend on the natures of the data you are trying to load. Lots of people may suggest QUALIFY *, and it may work for you, but it is rarely the best solution.
It is important to do some analysis up front and load to a data model design, rather than a 'load and hope' approach. That way you will know whether the excel sheets represent data that should be loaded into a common table - when the loop will be a good solution, but do an explicitly concatenate - or whether the data represents more than one logical table, in which case the loop is not a good solution.
See these articles by Marcus Sommer for more information:
Get started with developing qlik datamodels
Advanced topics for creating a qlik datamodel
More advanced topics of qlik datamodels