Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
I think this is an inherent issue with the excel ODBC / OLEDB connection I am afraid: