0 Replies Latest reply: Dec 11, 2014 1:42 PM by dick elsman RSS

    Loading multiple sheets

    dick elsman

      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