Importing Excel into SQL Problem

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

General SQL Server Forums

New to SQL Server Programming

Importing Excel into SQL Problem

Author

Topic

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2013-10-24 : 04:34:14

Hi

I am trying to import an excel 2007 file with about 368,000 rows into an existing table in SQL Server express 2008.

I tried setting these parameters below as per advice on other forums and I still get error:


USE [master] 
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 
GO

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

Then trying this query as a test:


select * FROM OPENROWSET(
  'Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0;Database=C:\myfiles\myfile.xlsx;HDR=YES',
  'SELECT * FROM [Report 1$]')

Gives:

quote:
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)".

Any ideas what I need to do to get this working?

Thanks

G

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2013-10-24 : 05:21:49

Is it possible to append data using the import wizard or will it rewrite over any data?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-10-24 : 06:03:17

Is it possible to append data using the import wizard ?

In the Import wizard there will be one check box for appending data at the end of existing table

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-24 : 08:27:30

can you try this some tweak?


select * FROM OPENROWSET(
  'Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0 Xml;Database=C:\myfiles\myfile.xlsx;HDR=YES',
  'SELECT * FROM [Report 1$]')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2013-10-25 : 07:49:54

That works - thanks again visakh16!

G

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-25 : 08:32:17

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources