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
 Transact-SQL (2005)
 Import from excel

Author  Topic 

Tkoletsis
Starting Member

5 Posts

Posted - 2008-11-27 : 04:22:57
I think it's a very common case to import data from an excel file to sql server 2005 thru a stored procedure. There are many solutions solutions such as OpenDataSource methods, cmd call DTS packages etc.
However i have not succeeded in find a receivable solution when the excel file is on the local machine. These methods work only if the file is at the same volume where the sql server is. But what happens if the excel file is at the local volume? I've read all the previous topic - export to excel - and have searched to a lot of other communities but the result was nothing. Has anyone came up against a similar situation?

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-27 : 04:25:03
Did you try specifying UNC path name?
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-27 : 04:41:32
<<
I've read all the previous topic - export to excel
>>

You must have found a solution

As said use

\\system_name\path

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Tkoletsis
Starting Member

5 Posts

Posted - 2008-11-27 : 07:10:04
With Unc path works perfect:

select f1 from Openrowset
('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\\server\test\test.xls;HDR=NO','Select * from [Sheet1$]')

When try to read the excel from local volume c:

select f1 from Openrowset
('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=c:\test\test.xls;HDR=NO','Select * from [Sheet1$]')

i get this error

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

The stored procedure as runs under the sql server environment doesn't see my local disk. Is there a way to allow my local disk to be recognized by the sql server environment which is allocated to server "server"?
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-27 : 07:36:36
Read this. You can find the answer
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-27 : 07:38:31
[/quote]Is there a way to allow my local disk to be recognized by the sql server environment which is allocated to server "server"? [/quote]

Thats why Madhi and Visakh said UNC path.
Go to Top of Page

Tkoletsis
Starting Member

5 Posts

Posted - 2008-11-27 : 07:56:10
Something like?

select f1 from Openrowset
('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\\my computer\my shared folder\test.xls;HDR=NO','Select * from [Sheet1$]')

It doesn't work.
A more specific answer please?
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-29 : 02:58:34
quote:
Originally posted by Tkoletsis

Something like?

select f1 from Openrowset
('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\\my computer\my shared folder\test.xls;HDR=NO','Select * from [Sheet1$]')

It doesn't work.
A more specific answer please?

Did you get any error?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -