OPENROWSET T-SQL function

Yesterday, I was in the situation where I needed to import the data from an Excel spreadsheet into a table in SQL Server. No problem - use DTS import/export wizard right? Well that is what I did, but after I uploaded it, the user kept coming back and giving me a new editted version of the spreadsheet to upload. Second time, no problem (should be the last right?). Delete the rows and then the import data wizard again...I think it took at least 4 times before we had the final spreadsheet to upload. I wish I had saved the DTS package the first time, but I didn't (it wouldn't be used again after the final upload anyways)

Next time I will use the OPENROWSET function. This will allow me to edit the SQL as needed each back and forth. Check it out, the following SQL will take all the records out of my Excel spreadsheet and insert them into the BonusUpload table:

GO
DELETE BonusUpload
GO
SELECT *
INTO BonusUpload
FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0'
,'Excel 8.0;Database=C:\_projects\CorpAdmin\reports\numbers.xls'
,'SELECT * FROM [Sheet1$]'
)
A great alternative to using the DTS import/export wizard

posted on Saturday, March 06, 2004 12:30 PM

Feedback

# re: OPENROWSET T-SQL function

I am trying the same thing. But the error coming is :

the worksheet name is Book1 in my amit.xls file


[OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'Book1$'. Make sure the object exists and that you spell its name and the path name correctly.]
6/24/2004 8:38 PM | amit

# re: OPENROWSET T-SQL function

I am gettng the same error even though the worksheet quite clearly exists
2/21/2005 4:25 AM | Vijay

# re: OPENROWSET T-SQL function

Sorry guys all I can say is check out this article: http://www.databasejournal.com/features/mssql/article.php/3331881 and the SQL Server books online (that is where I first found it)
2/21/2005 5:04 AM | Jason Haley

# re: OPENROWSET T-SQL function

Try putting the spreadsheet on the server where your SLQServer instance is running.
3/17/2005 8:15 AM | xx

# re: OPENROWSET T-SQL function

I've been able to perform this successfully, but only when the SQLServer service is set to interact with the desktop.
3/8/2006 3:46 PM | Daniel Bates

# re: OPENROWSET T-SQL function

The excel sheet name I'm passing in have spaces in them
i'm getting error msg. that it can't find the sheet

any ideas
7/17/2006 6:31 AM | CMull

# re: OPENROWSET T-SQL function

if you are getting Sheet1 or file not found; Check if sql services are running on you remote server?? then you need to move phsical excel file on database server itself.

SQL Service can not access your computer's local path. if you can not move xls file on database server.. try giving UNC path like \\yourMachineName\sharedfolder also do not forget to give permissions.

Cheers
Deepak Kumar

http://forum.sqlknowledge.com
4/11/2007 1:27 AM | Deepak Kumar

# re: OPENROWSET T-SQL function

Spaces in worksheet name are replaced with #

IE Spreadsheet "Number 1" (Sheet1) should be referenced as "Number#1$"

Yipee!
5/15/2007 1:27 AM | V

# re: OPENROWSET T-SQL function

If you are having trouble accessing table names with spaces, try enclosing them in both brackets and single quotes like:

SELECT *
FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0'
,'Excel 8.0;Database=C:\_projects\CorpAdmin\reports\numbers.xls'
,'SELECT * FROM [''This has Spaces$'']'
)

or if you already have a linked server try
select * from Linkedserver...['This Sheet Name Has spaces$']
9/28/2007 11:32 AM | Tim Wiseman

# re: OPENROWSET T-SQL function

ghjghjghj
7/11/2008 10:13 AM | hgj

Post Comment

Title  
Name  
Url
Comment   
Please enter the following code into the box below to stop spammers

   		Enter Code Here *