Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Connection for excel file

Hello,

I have excel file with different sheets in the file. I need to read all the sheets in the excel, so I am using below script:

CONNECT TO [Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\QVD\TP_AZ.xls;Extended Properties="Excel 8.0;"];

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

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

        LOAD *,  '$(sheetName)' as Sheet

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

NEXT

above script works fine when I reload the report manually.

But when I schedule the report 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.

I am unable to undestand why it is failing through publisher.

7 Replies
ashfaq_haseeb
Champion III
Champion III

Hi,

There is no need for connection string while reading Excel files.

Have a look at below post.

http://community.qlik.com/docs/DOC-4452

Regards

ASHFAQ

Not applicable
Author

In the link given, sheet name is having some pattern as 'Page 1, Page 2....

But I dont have such pattern. I can have any sheet name.

ashfaq_haseeb
Champion III
Champion III

Hi,

Have  a look at this link too.

http://community.qlik.com/message/223934#223934

Regards

ASHFAQ

Not applicable
Author

Hi Ashfaq,

link u gave, in that it is asking to use ODBC CONNECT 32,

I am already using this. but this is not working when I reload the report through publisher.

So I am asking is there any other way using which I can read the excel with multiple sheets.

vardhancse
Specialist III
Specialist III

For loading Excel sheet not required any connection string.

Only the required valid path of the excel sheet.

Not applicable
Author

Sasi,

Please can you help me writing the code.

I dont know the no of sheets in my excel.. only I know, is that I will be having sheet names as years

e.g. 2013, 2014 etc

so how to read it.

jonathandienst
Partner - Champion III
Partner - Champion III

Hi

You are quite correct using a connection to get the sheet names. I think Rupali identified your problem.Is the provider 32 bit or 64? I suspect that when you load, you are using 32 bit desktop, but the server is running 64 bit and there is no 64 bit provider. Try

CONNECT32 TO [Provider=Micr....


This will cause the server to use a 32 bit provider.


The other possibility is the the Excel provider is not installed at all. I suspect that by default it is only installed if Excel is installed on the server?


HTH

Jonathan


Logic will get you from a to b. Imagination will take you everywhere. - A Einstein