Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
aldo-tgh
Partner
Partner

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

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]; 

Picture2.jpg

1 Solution

Accepted Solutions
rwunderlich

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

View solution in original post

6 Replies
rbecher
MVP
MVP

How about:

Sheets:

SQLTables;

- Ralf

Vizlib Head of R&D
aldo-tgh
Partner
Partner
Author

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

Aldo.

rbecher
MVP
MVP

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

Vizlib Head of R&D
aldo-tgh
Partner
Partner
Author

It seems that ODBC provider for Excel files is missing.

What should I install?

rwunderlich

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

aldo-tgh
Partner
Partner
Author

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

OLE DB runs also.

Thank you guys!

Aldo.