Multiple sheets from excel in Qliksense

[elakkians]

Hi,

I need to load all the sheets from an excel file. In qlikview we use ODBC to connect excel But in Qlik sense i am not able to do that .

Attached a sample file for my requirement.

Help Please!!!!!!!!!!!!!!!

[undergrinder]

Hi Elakkian,

This document will help you:

Load Multiple excel sheets using For loop

G.

[elakkians]

Thanks , But in my case the sheet name are not sequential and it has a different texts .

[undergrinder]

I disagree, your sheets names are sequential, but in other manner, Month-Year style.

you can create that sheet names in your load script.

I didn't tried, but found an other solution, with ODBC connect:

Loading Multiple Excel Sheets Dynamically along with file name and sheet name

Maybe the solution above is better fit for your requirements.

G.

[kaanerisen · Accepted solution]

Hi Elakkian,

How about this,

Script:

Excel:

Output:

OLEDB CONNECT32 TO  [Provider=Microsoft.ACE.OLEDB.12.0;Data Source='{{ExcelFilePath}};Extended Properties="Excel 12.0;HDR=YES";];

tables:

SQLtables;

DISCONNECT;

SheetNameTable:

load mid(TABLE_NAME,2,len(TABLE_NAME)-3) as TABLE_NAME

Resident tables;

drop table tables;

for each i in FieldValueList('TABLE_NAME')

MasterData:

LOAD  *,

  '$(i)' as SheetName

  FROM [lib://downloads/test.xlsx]

     (ooxml, embedded labels, table is [$(i)]);

next;

drop table SheetNameTable;

[elakkians]

Thanks Kaan.

I am getting the below shown error.

[rangam_s]

Update as below in your Qlik Sense Setting File (Settings.ini) so that you can read the data in legacy mode as well (Qlik View).

StandardReload=0

Path of the File - C:\Users\UserName\Documents\Qlik\Sense