Qlik Community

QlikView Publisher

Discussion Board for collaboration on QlikView Publisher.

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

Re: Connection for excel file

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

Re: Connection for excel file

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.

Re: Connection for excel file

Hi,

Have  a look at this link too.

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

Regards

ASHFAQ

Not applicable

Re: Connection for excel file

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
Valued Contributor II

Re: Connection for excel file

For loading Excel sheet not required any connection string.

Only the required valid path of the excel sheet.

Not applicable

Re: Connection for excel file

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.

MVP
MVP

Re: Connection for excel file

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
Community Browser