Loading multiple sheets from excel

[#1]

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

[#2]

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

[#3]

Hi Jonathan,

Great collection of articles. Thanks!

[settu_periasamy · Accepted solution]

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;

[#5]

It worked! Thank you very much Settu! I'm very happy with it!