Loading multiple sheets

dickelsa — Thu, 11 Dec 2014 18:42:56 GMT

Hi guys,

I ran into the problem that I wanted to load multiple sheets form 1 workbook in just 1 load statement.

I found some scripts saying you should connect to the Excel file using the ODBC connect line, but there is another way.

This one is a bit more work, but it is easier to understand I think.

What you need to do:

First, load the file into Qlikview, to see how Qlikview sees the sheet names (mostlikely there is a dollar sign behind the name)

1) In the file you want to load , create a table with field name 'SheetNames'

2) Type every sheet name you want to load from the file, adding '$' to the end of each sheetname

3) Rename the sheet of the file to 'Index'

4) Save it.

5)Use the code below

TRACE INDEXING SHEETS;

SET var_filename='EXCELTABLE.xls'

INDEXING:

LOAD SheetNames,

autonumber(SheetNames) as RowNo

FROM

[..\$(var_filename)]

(biff, embedded labels, table is Index$);

TEMPINDEX:

load

max(RowNo) as maxload

resident INDEXING;

TRACE SETTING LOAD VARIABLES TO DEFAULT;

LET var_indexload=peek('maxload',0,'TEMPINDEX');

SET var_startload=0;

LET var_tabload=0;

set var_tabindex='';

TRACE LOADING SPECIFIED SHEETS;

DO WHILE var_tabload<var_indexload

LET var_tabindex=Peek('SheetNames',$(var_tabload),'INDEXING');

Loop_Temp:

Load *

FROM

[..\$(var_filename)]

(biff, embedded labels, table is [$(var_tabindex)], filters(

Remove(Row, RowCnd(CellValue, 1, StrCnd(null)))

)) ;

LET var_tabload=var_tabload+1;

LOOP

TRACE STORING LOOP DATA INTO QVD;

STORE Loop_Temp into TempLoad.qvd (qvd);

This one worked for me, I hope you enjoy the code!

Gr.

Dick

topic Loading multiple sheets in QlikView

Loading multiple sheets