How to load multiple sheets from an excel file

[#1]

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

[marcus_malinow]

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

[#3]

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

[marcus_malinow]

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....