topic Re: How to load multiple sheets from an excel file in QlikView

How to load multiple sheets from an excel file

Wed, 25 Nov 2020 16:16:04 GMT

Hi ,

Can anyone please help me to load multiple sheets from an excel file.

I have an excel file with 16+ sheets and every week the number of sheets may vary. The sheet names are like 'Reseller master France','Reseller master Germany' etc and all sheet has same columns.

Is there any way to consolidate the data from all sheets.

Regards ,

Indu

Re: How to load multiple sheets from an excel file

marcus_malinow — Tue, 29 Aug 2017 15:10:05 GMT

If your file is in XLSX format, you can use SQLTables to build a list of sheets in your file, then loop through these.

ODBC CONNECT TO [Excel Files;DBQ=Your Excel File.xlsx];

//use SQLTables to assemble a list of all the sheets in this Excel file

XLSSheetList:

SQLTables

;

DISCONNECT; //from excel

for n = 0 to NoOfRows('XLSSheetList')

LET vXLSXSheet = peek('TABLE_NAME', n, 'XLSSheetList');

[Your Data]

LOAD [your field list]

FROM

[Your Excel File.xlsx]

(ooxml, no labels, table is '$(vXLSXSheet )');

next n

Re: How to load multiple sheets from an excel file

Wed, 30 Aug 2017 13:24:39 GMT

Hi Marcus ,

Thank you so much for your help.

However when I am running the script I am getting the error

The following error occurred:

CONNECTs other than LIB CONNECT are not available in this script mode.

Could you please help.

Thanks in advance ,

Indu

Re: How to load multiple sheets from an excel file

marcus_malinow — Wed, 30 Aug 2017 15:37:18 GMT

You're going to need to set up a connection - see this link

http://help.qlik.com/en-US/sense/2.2/Subsystems/Hub/Content/LoadData/connect-data-sources.htm?_ga=2.127292431.329800257.1503991029-182005855.1502108373