Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

loop through tabs of an excel file (worksheet)

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

@sunny_talwar 
@rwunderlich 

 

I can walk on water when it freezes
5 Replies
Highlighted
Partner
Partner

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;

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
Partner
Partner

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

I can walk on water when it freezes
Highlighted
Partner
Partner

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;
Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
Partner
Partner

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

I can walk on water when it freezes
Highlighted
Partner
Partner

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

 

I can walk on water when it freezes