Import Excel 2007 into SQL Server 2005

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 2005 Forums

SSIS and Import/Export (2005)

Import Excel 2007 into SQL Server 2005

Author

Topic

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2010-04-20 : 06:32:28

Hi

When importing Excel 2007 data into sql via Import wizard:

choose "Microsoft Office 12.0 Access Database Engine OLE DB
Provider" as the data source in SQL Server Import and Export Wizard, then click Properties, switch to the All tab, input your excel file path to the Data source field and input "Excel 12.0" to the Extended Properties field and then click OK to follow the wizard.

I get the following error:
Error 0xc0202009: Source An OLE DB error has occurred. Error code: 0x80004005

Error 0xc02020e8: Source Opening a rowset for failed

Exception from HRRESULT: 0xc02020e8 (Microsoft.sqlserver.dtspipelinewrap)

I have recently upgraded to Vista and did not have this problem in XP, does anybody know how to fix this, as I really need to get the data into SQL server 2005 (without using SSIS)

Thanks!

tomy74
Starting Member

32 Posts

Posted - 2010-04-23 : 10:26:08

Hi,

You can try the following example(without using SSIS).

This command will pull in all data from worksheet [Sheet1$]. By using the INSERT INTO command you can insert the query results into table dbo.ImportTable.

For Excel 97-03 workbook:
INSERT INTO dbo.ImportTable
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\ImportData.xls', [Sheet1$])

For excel 2007, you need Provider=Microsoft.ACE.OLEDB.12.0.

It can be downloaded from MSDN, this one I think:

http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

INSERT INTO dbo.ImportTable
SELECT *
FROM
OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=C:\ImportData.xls;
Extended Properties=Excel 12.0')...[Sheet1$]

Regards,

Tarek Ghazali

SQL Server MVP
http://www.sqlmvp.com

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2010-04-26 : 04:36:32

Hi Tarek,

Thank you for that, but unfortunately I still get the error:

Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.

Any idea what this means and how to register it?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-04-26 : 04:46:59

google for the error- there are fixes available. You just need to install few new drivers.

phenreid
Starting Member

29 Posts

Posted - 2010-05-04 : 16:27:38

I am geting the same error with ACCDB.
Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.

Where does the provider need to be installed. Client or server where SQL/Server is residing?

Can anyone tell me on which machine it is not registered?

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2010-05-05 : 04:34:16

Hi

I finally managed to get around this problem by installing SQL Server 2005 Service Pack 3. Hope that helps for you.

phenreid
Starting Member

29 Posts

Posted - 2010-05-05 : 14:53:20

I solved my problem by installing the ACE OLEDB provider on the same machine as the SQL Server. If you go to linked tables in SSMS, you will see there a list of available drivers. ACE was missing -- which was the clue.

It seems obvious to me now. You are passing through a t-sql command with an openrowset, for example, that refers to Microsoft.ACE.OLEDB.12.0. Obviously, that has to be installed on the same machine as the SQL/Server instance that is processing the query.

I was using SQL Server 2008. But it was very difficult for me to find the information on this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources