loop through tabs of an excel file (worksheet)

[ali_hijazi]

Hello is there a way to loop through worksheets of an excel file without using the Legacy Mode in QlikSense?

@sunny_talwar 
@rwunderlich 

[Vegar]

You could make use of the Excel odbc connector. xls-connector comes with windows, but xlsx do not. If you do not have it you can get it here:  https://www.microsoft.com/en-US/download/details.aspx?id=13255

Then you just connect to your excel file using odbc and then call the SQLTABLE command to get the sheet names.

See sample code below.

LIB CONNECT TO 'Multi Sheet Excel ODBC Connector';
SQLTABLES:
SQLTABLES;

LOAD TABLE_NAME RESIDENT SQLTABLES;
TRACE
##START LOADING;
FOR each _tab in fieldvaluelist('TABLE_NAME')
TRACE
Load excelfile using tab='$(_tab)');
NEXT
TRACE
##STOP LOADING;

[ali_hijazi]

Actually all the files are .xlsx
and I'm not connecting to one or two files, but rather I got a folder that has several sub-folders and each got several excel files

so your suggestion doesn't work for me

[Vegar]

Does the files look similar in regards to sheet names? If so then you could combine odbc and normal load.

/

/Use ODBC to find the sheet names
LIB CONNECT TO 'Multi Sheet Excel ODBC Connector';
SQLTABLES:
SQLTABLES;

LOAD TABLE_NAME RESIDENT SQLTABLES;
FOR each _tab in fieldvaluelist('TABLE_NAME')
   //Use normal folder lib to extract the data usin the sheet names found in SQLTABLES
    LOAD * FROM LIB:\\Excelpath\*.xlsx (oomlx, sheet=$(_tab))  //Loading using a folder LIB
NEXT

DROP TABLE SQLTABLES;

[ali_hijazi]

let me download the excel connector and check it from my side then

[ali_hijazi]

but not all excel files have the same number / name of sheets
I need to check on the existence of some sheet and if they exist I should read data from them

so the excel connector is bound to a one Excel File

is there a way to use it dynamically I mean I pass to it the file name during script execution?!