Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
dickelsa
Contributor

Loading multiple sheets

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)

Next

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

Community Browser