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.
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? |
|
|
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 solutionAs said use\\system_name\pathMadhivananFailing to plan is Planning to fail |
|
|
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 errorMsg 7399, Level 16, State 1, Line 1The 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 1Cannot 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"? |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
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. |
|
|
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? |
|
|
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?MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|