topic Loading multiple sheets from excel in QlikView

Loading multiple sheets from excel

Mon, 29 Feb 2016 22:27:51 GMT

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)]);

Re: Loading multiple sheets from excel

maxgro — Mon, 29 Feb 2016 23:04:57 GMT

try replacing the Let with

Let vExcelSheets = left(Peek('TABLE_NAME', i, 'EXCELTABLES'), len(Peek('TABLE_NAME', i, 'EXCELTABLES'))-1);

Re: Loading multiple sheets from excel

maxgro — Mon, 29 Feb 2016 23:11:48 GMT

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;

Re: Loading multiple sheets from excel

Wed, 02 Mar 2016 20:37:17 GMT

Just replacing unfortunately doesn't work. It stills gives me only the first result of my sheets.

Re: Loading multiple sheets from excel

settu_periasamy — Thu, 03 Mar 2016 03:02:10 GMT

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

Load all Excel files and all sheets in a folder

Re: Loading multiple sheets from excel

Mon, 21 Mar 2016 22:03:29 GMT

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

Re: Loading multiple sheets from excel

settu_periasamy — Tue, 22 Mar 2016 05:13:31 GMT

Sure. Is it possible to provide the exact script you are working and sample of excel files ?

Re: Loading multiple sheets from excel

jonathandienst — Tue, 22 Mar 2016 05:13:41 GMT

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.

Re: Loading multiple sheets from excel

jonathandienst — Tue, 22 Mar 2016 05:17:21 GMT

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.

Re: Loading multiple sheets from excel

jonathandienst — Tue, 22 Mar 2016 05:19:05 GMT

See these articles by Marcus Sommer for more information:

Get started with developing qlik datamodels

Advanced topics for creating a qlik datamodel

Re: Loading multiple sheets from excel

Tue, 22 Mar 2016 13:59:27 GMT

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

Re: Loading multiple sheets from excel

Tue, 22 Mar 2016 14:02:37 GMT

Hi Jonathan,

Great collection of articles. Thanks!

Re: Loading multiple sheets from excel

settu_periasamy — Wed, 23 Mar 2016 02:47:20 GMT

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;

Re: Loading multiple sheets from excel

Wed, 23 Mar 2016 18:27:16 GMT

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