Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sangland2
Partner - Creator
Partner - Creator

Loading multiple Excel tabs in sequence

Hi, I needed to load data from multiple excel tables in order, say form left to right into a single QV table,  I don't know what the tabs are called. Left most data tab will be "Current" and next tabs may contain additional older data that still needs to be concatenated (using where not exists). I can use @1, @2 etc in a loop but I have found that this does not correspond to the way Excel store the tabs . Basically new tabs are last no matter where the user puts the tab. The users can rename the tabs "1_xxx", 2_xxx" however internally Excel stores these as "1_xxx$' and does not return them in order.

I have found that renaming tabs A_CURRENT,C_SHEET3 , B_SHEET2 etc works to control the sequence alphabetically.

  For Each vFile in FileList('D:\Qlikview Protected\mysheet.xlsx') 

     ODBC CONNECT64 To [Excel Files;DBQ=$(vFile)]; 

     Sheets: 

     SQLTABLES; 

     DISCONNECT;

      For i = 0 To NoOfRows('Sheets')-1 

          Let vSheet = left(Peek('TABLE_NAME', i, 'Sheets'), len(Peek('TABLE_NAME', i, 'Sheets'))-1); 

          TRACE $(vSheet) ;

          LET vNoOfSheets=$(vNoOfSheets)+1 ;

        Next; 

     DROP Table Sheets;       

  Next

    LET vNoOfSheets=$(vNoOfSheets)-1 ;

    TRACE 'No of Sheets: ' $(vNoOfSheets) ;

eg Returning

A_CURRENT

B_SHEET2

C_SHEET3

Note that when sheets are renamed, excel seems to store "Blank" sheets still so vNoOfSheets can be incorrect.

1 Reply
adamdavi3s
Master
Master

I think this is an inherent issue with the excel ODBC / OLEDB connection I am afraid:

Excel Sheet Names in Sheet Order