Querying excel file from SQL

[stoymigo]

SELECT * 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\file.xls',
'SELECT  * FROM [sheetname$]')

This code doesn't work when I run it, but if I transfer my file to a network folder, then it does.
When I run this code from my home machine which has sql server installed locally, it works fine.

I'm guessing the sql server on my company's network doesn't look at my work pc's c drive, how do I fix this?

Basically I'm trying to find a fast way to read an excel file, if I do this from C# using oledb it works but it doesn't pull through all values because the excel file is made by copying and pasting from multiple sources.

 

[ToxicBunny]

If you're running this off a SQL server remotely, then telling it to look at C:\xxxx will make the SQL Server look at the C drive of the Node its running on not your machine.

If you share the file on your machine and have the query reference the file with a full network path, and give SQL rights to your machine then it should work.

 

[Toitjie]

this has happened many times to me to. lol

ie, when making a back up, using tsql , I usually put it in C: somewhere, and then look for it everywhere on my machine, just to realize it is actually on the servers C:

 

[ToxicBunny]

this has happened many times to me to. lol

ie, when making a back up, using tsql , I usually put it in C: somewhere, and then look for it everywhere on my machine, just to realize it is actually on the servers C:

I'm disappointed.. I expected better of you lot

 

[Toitjie]

I'm disappointed.. I expected better of you lot

pfft, I run management studio on my machine not the server dammit :wtf: