Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

elakkians
New Contributor III

Multiple sheets from excel in Qliksense

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

Tags (3)
1 Solution

Accepted Solutions
kaanerisen
Contributor III

Re: Multiple sheets from excel in Qliksense

Hi Elakkian,

How about this,

Script:

Untitled.png

Excel:

Untitled2.png

Output:

Untitled3.png

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;

6 Replies
undergrinder
Valued Contributor II

Re: Multiple sheets from excel in Qliksense

Hi Elakkian,

This document will help you:

Load Multiple excel sheets using For loop

G.

elakkians
New Contributor III

Re: Multiple sheets from excel in Qliksense

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

undergrinder
Valued Contributor II

Re: Multiple sheets from excel in Qliksense

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
Contributor III

Re: Multiple sheets from excel in Qliksense

Hi Elakkian,

How about this,

Script:

Untitled.png

Excel:

Untitled2.png

Output:

Untitled3.png

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
New Contributor III

Re: Multiple sheets from excel in Qliksense

Thanks Kaan.

I am getting the below shown error.

Sample.JPG

rangam_s
Contributor II

Re: Multiple sheets from excel in Qliksense

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

Community Browser