OpenRowSet issues

Question

Post reply

OpenRowSet issues

J39L4753

SSCrazy

Points: 2803
More actions
March 29, 2011 at 5:40 am

#236869

I have an Excel2007 file (*.xlsx) that I want to import into SQL Server. I need to use OpenRowSet as I am having to use SQL server 2005 Express.
This is what I am using
SELECT * INTO #DataLoad
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;Database=C:\BCP\DataLoadv2.xlsx',
'SELECT * FROM [Sheet1$]');
The problem I have is this below
OLE DB provider "Microsoft.ACE.OLEDB.12.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.ACE.OLEDB.12.0" for linked server "(null)".
How can I get around this and import my xlsx file?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Is it possible the Excel file is still open (= in use by another application)?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

GSquared SSC Guru Points: 260824 More actions · Answer 2

You're probably missing the Office 2007 data providers, based on the error.

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

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

J39L4753 SSCrazy Points: 2803 More actions · Answer 3

LutzM (3/29/2011)
Is it possible the Excel file is still open (= in use by another application)?

Thanks for the reply but it wasnt open, the file was a copy into a new directory.

J39L4753 SSCrazy Points: 2803 More actions · Answer 4

GSquared (3/29/2011)
You're probably missing the Office 2007 data providers, based on the error.
They can be found at: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

Thanks, I'll download and install this and see what happens.