Import data from Excel 2007 (xlsx) file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Import data from Excel 2007 (xlsx) file

Author

Topic

lebedev
Posting Yak Master

126 Posts

Posted - 2009-04-29 : 22:37:09

I just spent two hours Googling for a solution to my problem and am very frustrated.

What I need to do is import data from Excel 2007 (xslx) file into a SQL Server 2008 or 2005 database table.

I double-checked all paths and made sure that Microsoft.ACE.OLEDB.12.0 is installed and shows up under Linked Servers->Providers.

Here is the query I am running:

select	top 50 *
from OpenDataSource( 
		'Microsoft.ACE.OLEDB.12.0',
		'Data Source=C:\Temp\test.xlsx;Extended properties="Excel 12.0 Xml;HDR=Yes;IMEX=1"')...[Sheet1$]

Here is the error I am getting:


Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
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)".

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-05-10 : 09:50:32

Save it in XLS format and import with Export/Import Wizard.