openrowset excel data

Question

openrowset excel data

nimmi.smith-624103

SSC Eights!

Points: 876
More actions
June 25, 2009 at 1:03 pm

#135459

I ran this statement
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=\\helo\g$\DATA\JOLMFreight.xls;IMEX=1', 'Select * from sheet1$')
I keep on getting this 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)".
I am sure someone MUST have already figured this out, HELP!!

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

I'm not sure what Excel version you're trying to import from but you might want to check if the following link gives you the answer you're looking for: http://www.sql-server-performance.com/articles/biz/How_to_Import_Data_From_Excel_2007_p1.aspx

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]

Frank Bazan SSCertifiable Points: 5378 More actions · Answer 2

This error is usually a permission problem.

If you are running under a SQL login, check that the service account has read permission on that filepath.

If you are running under a Windows login check that the window account has read permission on that filepath.

If the file is already open you get this error too.

Off the top of my head, other issues might be trying to execute on a 64bit machine, login might not have sufficient privileges on SQL Server. Also I think I read somewhere that service account needs permission on the local servers temp folder...

HTH

Kindest Regards,

Frank Bazan

nimmi.smith-624103 SSC Eights! Points: 876 More actions · Answer 3

Thanks Frank. I checked all the permissions and I am still getting the same error. I have an excel spreadsheet that I am trying to import. It has zip code. Some zips have 5 digits, some have 5 with the dash and 4 digits. When I use ssis to import, it thinks that the zip code should be numeric. For the life of me, I can't seem to make it think that the zipcode needs to be a string. Has someone figured out a way around this?

jill.heyser SSC Enthusiast Points: 108 More actions · Answer 4

Hello,

Check the provider setting and make sure the check box next to "Allow inprocess" is checked. In SSMS this setting is under Linked Servers -> Providers -> Microsoft.ACE.OLEDB.12.0 -> properties (or whatever provider you are using).

OpenRowset does seem to be very sensitive to file locks and I have also seen it leave persistent file locks.

Good luck!