topic Re: OLE DB Connection string to Excel 2013 xlsx file - Read / retrieve list of worksheets in QlikView

OLE DB Connection string to Excel 2013 xlsx file - Read / retrieve list of worksheets

aldo-tgh — Tue, 16 Dec 2014 17:46:34 GMT

Hi Guys,

I need to read the list of worksheets in an Excel file. In this case is xlsx but I will need to deal also with xls.

I know this task can be done using Extended properties of ODBC /OLE DB connection.

In order to implement this solution, I am trying to connect to an unprotected Excel 2013 xlsx file with the connection string below, but getting the pop-up below asking for some missing information.

Any help will be appreciated.

Thanks,

Aldo.

OLEDB CONNECT32 TO [Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=$(vPath);Mode=Share Deny None;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False];

Re: OLE DB Connection string to Excel 2013 xlsx file - Read / retrieve list of worksheets

rbecher — Wed, 17 Dec 2014 08:03:04 GMT

How about:

Sheets:

SQLTables;

- Ralf

Re: OLE DB Connection string to Excel 2013 xlsx file - Read / retrieve list of worksheets

aldo-tgh — Wed, 17 Dec 2014 08:06:12 GMT

The problem is that I am getting the pop-up attached above when trying to connect, before SQLTABLES statement.

Aldo.

Re: OLE DB Connection string to Excel 2013 xlsx file - Read / retrieve list of worksheets

rbecher — Wed, 17 Dec 2014 08:08:54 GMT

Because you need to set User/Password in the connect statement...

Re: OLE DB Connection string to Excel 2013 xlsx file - Read / retrieve list of worksheets

aldo-tgh — Wed, 17 Dec 2014 08:27:09 GMT

It seems that ODBC provider for Excel files is missing.

What should I install?

Re: OLE DB Connection string to Excel 2013 xlsx file - Read / retrieve list of worksheets

rwunderlich — Wed, 17 Dec 2014 16:45:27 GMT

I don't think you can use the Jet provider for xlsx.

On my machine, I have an ODBC driver for Microsoft Excel. The DSN is present in the UserDSN. I assume it installed with MS Office.

I believe you can use OLEDB as well, but you have to download a driver.

Info - Microsoft ACE OLEDB 12.0 - ConnectionStrings.com

-Rob

Re: OLE DB Connection string to Excel 2013 xlsx file - Read / retrieve list of worksheets

aldo-tgh — Wed, 17 Dec 2014 19:32:17 GMT

After installing the provider for Office, ODBC is running ok.

OLE DB runs also.

Thank you guys!

Aldo.