Linked Server Error: Unable to read excel 2010 data through sql server 2008

Question

I am trying to read the data from the excel file through the SQL Server 2008.

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
      'Excel 8.0;Database=C:\Hierarchy.xlsx',
      'SELECT * FROM [Group$]')

Its giving me a weird error:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Excel sheet format is correct, path is correct. don't know why its giving me an error.

dIvYaNsH sInGh · Accepted Answer · 2013-07-18 22:43:07Z

1

I found this solution:

I save the existing file in backward compatibility and then fire this one:

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
'Excel 12.0;HDR=NO;Database=D:\Hierarchy.xls;', 
[Group$])

Don't know why it works and others not, may someone have a good idea about this.

edited Jul 18, 2013 at 22:43

answered Jul 18, 2013 at 22:07

dIvYaNsH sInGh

1,9933 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vasanth · Accepted Answer · 2013-07-18 19:28:05Z

0

Try using ACE OleDB and excel version 12.0

Example:

SELECT * FROM 
OPENROWSET
(           

        'Microsoft.ACE.OLEDB.12.0',
        'Data Source=C:\Hierarchy.xlsx;Extended Properties="Excel 12.0;HDR=YES"', 
        'SELECT * FROM [Group$]'
)

answered Jul 18, 2013 at 19:28

Vasanth

1,7101 gold badge13 silver badges17 bronze badges

5 Comments

dIvYaNsH sInGh Over a year ago

Gives following error: OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Could not find installable ISAM.". Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Vasanth Over a year ago

Try installing the ACE provider from this URL

Doug Glancy Over a year ago

Note that this answer is a good one if you're Excel version is 2007 or later. If it's 2003 or earlier than you want to use the Jet connection, as in your original post.

Andy G Over a year ago

The installable ISAM error is because it should be Extended Properties="Excel 12.0 Xml;HDR=YES"'

dIvYaNsH sInGh Over a year ago

It is excel 2010 version.

Collectives™ on Stack Overflow

Linked Server Error: Unable to read excel 2010 data through sql server 2008

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related