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
