Problem with OpenRowset to excel

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

Problem with OpenRowset to excel

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-09-01 : 11:13:20

I have the below statement.

select *
into Test FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\ExcelFiles\Copy of AOC Leads List.xls',
'SELECT * FROM [Sheet1$]')

and am recieving the following error. and can not work out what is wrong. can someone help me out please

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.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.Jet.OLEDB.4.0" for linked server "(null)".

MCTS / MCITP certified

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-01 : 12:30:27

is this your local path or server path?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-09-01 : 12:37:29

Also, the name of the file with blanks in between- I am not sure but could you try changing the name of the file for once and then uploading.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-01 : 12:46:35

also is the file kept opened

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-02 : 04:20:47

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-09-02 : 04:32:41

try to change the path of the existing file and place it on the desktop and try again hope it will work

With Regards
Kashyap M

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-09-02 : 04:37:35

i worked on the same code it should work. change the path

With Regards
Kashyap M

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-09-02 : 07:14:02

i have changed the file path to my desktop. code is as follows

select *
into Test FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Documents and Settings\robertd\Desktop\test.xls',
'SELECT * FROM [Sheet1$]')

and i receive the following error

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-09-02 : 08:02:28

quote:
Originally posted by masterdineen

i have changed the file path to my desktop. code is as follows

select *
into Test FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Documents and Settings\robertd\Desktop\test.xls',
'SELECT * FROM [Sheet1$]')

and i receive the following error

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

 
select * into Test1 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=C:\Documents and Settings\robertd\Desktop\test.xls',
'SELECT * FROM [Sheet1$]')

try this

With Regards
Kashyap M

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-09-02 : 08:03:31

Hope it works

With Regards
Kashyap M

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-09-02 : 08:29:21

Tried that and received the following errors

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-02 : 08:54:29

Note that the file should be in Server's directory

Madhivanan

Failing to plan is Planning to fail

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-09-02 : 11:17:30

i have moved the file onto server and this is the code i used.

select * into Test1 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\bifmsrv1\ICTdept\DTS Files\ExcelFiles\test.xls',
'SELECT * FROM [Sheet1$]')

and recieved

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.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.Jet.OLEDB.4.0" for linked server "(null)".

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-09-03 : 02:57:02

sorry for the delay did you had all the permissions do you work as user or admin

With Regards
Kashyap M

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-09-03 : 06:01:41

create a sample table having the columns in the excel sheet

With Regards
Kashyap M

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-09-05 : 15:02:27

Hello Kashyap

I work as dba. So i should have all permissions i need,

but just in case what permissions that are common i could of missed out.

Also can you not simply insert into a #temp table.

Regards

Rob

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-09-05 : 17:03:32

Also do i need a linked server to be set up?

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-09-06 : 13:22:53

i have worked this out now. The file needs to be saved within the SQL Server route.
ie ( C:\Program Files\Microsoft SQL Server\MSSQL.1 )
Is there any settings within SQL Server so i can have the desired location open to the openrowset function.

Can i just say thankyou to everyone who has helped me out this this problem

Kind Regards

Rob

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-09-08 : 06:18:04

sry for the delay can you elaborate the data in the excel file

With Regards
Kashyap M

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-09-08 : 06:19:03

i think you had all the permissions or else it will show that permission error

With Regards
Kashyap M

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources