topic Re: loop through tabs of an excel file (worksheet) in App Development

loop through tabs of an excel file (worksheet)

ali_hijazi — Mon, 01 Jun 2020 11:08:52 GMT

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

Re: loop through tabs of an excel file (worksheet)

Vegar — Mon, 01 Jun 2020 14:29:43 GMT

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;

Re: loop through tabs of an excel file (worksheet)

ali_hijazi — Mon, 01 Jun 2020 14:42:16 GMT

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

Re: loop through tabs of an excel file (worksheet)

Vegar — Tue, 02 Jun 2020 07:56:50 GMT

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;

Re: loop through tabs of an excel file (worksheet)

ali_hijazi — Tue, 02 Jun 2020 10:23:27 GMT

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

Re: loop through tabs of an excel file (worksheet)

ali_hijazi — Tue, 02 Jun 2020 11:08:50 GMT

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?!