topic Re: How to read the data from multiple sheets of an excel file? in QlikView

How to read the data from multiple sheets of an excel file?

Mon, 27 Feb 2012 08:41:14 GMT

Hi All,

First, greetings to all as this is my very first day at QlikCommunity.

I have a scenario, where I need to read the data from an excel workbook, which contains multiple work sheets. Each sheets contains the data in same format, but the name of sheet is not known to me. The script should be able to read the sheet name as well as data inside the sheet dynamically.

To elaborate more on this let me use an example.

A excel workbook contain the details of student grades in a excel workbook (student.xlsx). Lets say, this excel file contains the data of 10 students. Each sheet is saved as students name for example, sheet name 'bob' contains the grade transcript of student bob. Apart from this the student name is not mentioned anywhere in the sheet.

Currently, I am using a for loop with the names of students as hardcoded, but now I need to read it dynamically.

Please let me know, how can I acheive this. I hope I am making sense.

It looks like an interesting problem, hope to get a solution soon. Thanks

Regards

Re: How to read the data from multiple sheets of an excel file?

CELAMBARASAN — Mon, 27 Feb 2012 08:51:58 GMT

Hi,

Have a look in to the attached file it will help you.

Celambarasan

Re: How to read the data from multiple sheets of an excel file?

Mon, 27 Feb 2012 09:49:20 GMT

Thanks Celambarasan,

Thanks for your help. I really appreciated the same.

This solution looks close but in this script you look for all worksheet starting with Sales*. But, I have no clues about the nature of names.

I think we can use the same logic with some custamization.

Re: How to read the data from multiple sheets of an excel file?

CELAMBARASAN — Mon, 27 Feb 2012 10:16:58 GMT

Hi,

In your case don't use If Statement apply directly.

Remove this

IF wildmatch('$(sheetName)', 'Sales*') THEN // Only sheetNames that begin "Sales"

END IF // End of optional sheet filtering

Use the following alone

Sales:

LOAD *,

'$(sheetName)' as Student

FROM workbook.xls (biff, embedded labels, table is [$(sheetName)]);

Celambarasan

How to read the data from multiple sheets of an excel file?

Mon, 27 Feb 2012 10:23:18 GMT

Thanks a lot

I will try it and let you know.

Re: How to read the data from multiple sheets of an excel file?

Anonymous — Wed, 10 Jul 2013 09:34:44 GMT

Note: If you are using a 64-bit system then you should use

Provider=Microsoft.ACE.OLEDB.12.0;

instead of

Provider=Microsoft.Jet.OLEDB.4.0;

The following is a modified 64-bit example for loading workbook.xls which is put in C:\qv_data folder:

/* Connect using the Jet provider. Specify the workbook path in the "Data Source" spec below. */

CONNECT TO [Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\qv_data\workbook.xls;Extended Properties="Excel 8.0;"];

The SQLtables statement will load the "tables" table with a set of fields

describing the tables in the ODBC datasource. For Excel, each sheet is a table.

tables: // Name the table that will be created by SQLTables stmt

SQLtables;

DISCONNECT; // Don't need ODBC connection anymore

One of the fields loaded by SQLtables is "TABLE_NAME". This field will contain the sheet name.

We will loop through this set of sheet names.

FOR i = 0 to NoOfRows('tables')-1

Assign the TABLE_NAME to the variable "sheetName".

TABLE_NAMEs that contain spaces will be enclosed in single quotes.

The purgeChar function will remove any quotes.

LET sheetName = purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39));

// Optional filtering logic to select certain sheets

IF wildmatch('$(sheetName)', 'Sales*') THEN // Only sheetNames that begin "Sales"

Sales:

// Now that we have a sheet name, a standard biff (Excel) load can be used.

LOAD *,

'$(sheetName)' as Sheet // Optionally, the sheetName value may be loaded as a field

FROM C:\qv_data\workbook.xls (biff, embedded labels, table is [$(sheetName)]);

END IF // End of optional sheet filtering

DROP TABLE tables; // The table list is no longer needed

Re: How to read the data from multiple sheets of an excel file?

Mon, 18 Aug 2014 06:18:57 GMT

Hello Quan,

The script is working fine when I reload the report manually

But when I reload using qlikview publisher, I am getting below error:

CONNECT*Provider*

Error: ErrorSource: Microsoft OLE DB Service Components, ErrorMsg: Class not registered

General Script Error

Execution Failed

Execution finished.

What can be the reason?

Re: How to read the data from multiple sheets of an excel file?

Thu, 30 Oct 2014 19:52:30 GMT

Same issue here. My QV version is 11.20

Any hints?