Loading multiple Excel tabs in sequence

sangland2 — Wed, 25 Nov 2020 16:16:04 GMT

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;

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.

Re: Loading multiple Excel tabs in sequence

adamdavi3s — Fri, 10 Mar 2017 08:08:51 GMT

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

Excel Sheet Names in Sheet Order

topic Loading multiple Excel tabs in sequence in QlikView

Loading multiple Excel tabs in sequence

Re: Loading multiple Excel tabs in sequence