OPENROWSET syntax for excel
-
Can anyone try the following syntax to query an excel sheet? Which one works for you?
The post http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=240019
claimed the following syntax worked for him if right file/sheet name is given:
SELECT * FROM OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\book1.xls',
'[sheet1$]')
It never works on my machine. The following works for me (excel 2003 on win 2000. sql server 2000 sp4):
SELECT * FROM OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\book1.xls',
'SELECT * FROM [sheet1$]')
OR (using "Excel 5.0 " instead of "Excel 8.0 " )
SELECT * FROM OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'Excel 5.0;Database=c:\book1.xls',
'SELECT * FROM [sheet1$]')
Please change the file and sheet name to a excel file on your machine(attach a "$" sign to the sheet name and use brackets "[" and "]" to reference the sheet name 
. Just want to know whether it's configurable to make the two syntax all work. Thanks. -
if you look at BOL both of the solutions should work
Syntax
OPENROWSET ( 'provider_name'
, { 'datasource' ; 'user_id' ; 'password'
| 'provider_string' }
, { [ catalog. ] [ schema. ] object
| 'query' }
)
the first solution is an object based case and yours is a query based one. followup on BOL for OPENROWSET
-
Yes, I know the syntax is clear in the BOL. But it may depends on the provider.
Can you try the code in my original post on your machine, and let me know which one works for you (or maybe both).
-
i did use the BOL syntaxt and it worked
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\book1.xls', [Sheet1$])
the problem with the first query you are doubting is that it has single quotes around the object. use the one i have pasted and see if it works.
put your response so that i can follow it up
-
Yes. It's the single quotes. After removing it, it works. Thank you very much.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply