Best way to query Excel spreadsheet

What is the best way to query a worksheet in an Excel 12.0 spreadsheet that resides either on a local drive or a network share that has spaces in the share as well as the filename from SQL 2005?

I have tried what seems like a gazillion methods but either run into ISAM errors or no data being pulled. The SQL server does not have Office installed if this makes a difference.

H E L P ! !
edrz01Asked:
Who is Participating?
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
Check this one out:

http://support.microsoft.com/kb/306397

Provided with an example in the above link

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
   'Data Source=c:\book1.xls;Extended Properties=Excel 8.0')...Sheet1$

kindly give it a try.
0
 
ralmadaCommented:
One way is using linked server. See link below:
 http://support.microsoft.com/kb/306397
If you run inot this error "Could not find installable ISAM". It could occurs if you enter "DataSource" instead of "Data Source"
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Using OPENROWSET is the best method to query from Excelsheet.
For more info refer the below link

http://msdn.microsoft.com/en-us/library/ms190312.aspx
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
EmesCommented:
Linked servers !

EXEC sp_addlinkedserver 'ExcelSource1',
   'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   'c:\BB.xls',
   NULL,
   'Excel 5.0'
GO


SELECT * into temp FROM ExcelSource1...Suzuki081222$

EXECUTE sp_tables_ex ExcelSource1
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
For example, if you have to query each and every time from a different Excel sheet, you have to add that one as linked server and after that do your queries.

OPENROWSET is an alternative approach to Linked Server and there is no need to config for each and every excel sheet.

You can directly specify the provider and then the file name to query from excel.
And you have to remove that linked server connection once your queries are over.

Hence I would suggest you to go for OPENROWSET .
0
 
edrz01Author Commented:
Emes, in your example as a linked server you show Excel 5.0. Does this need to be updated to 12.0 since I am using Office 2007? What significance does the $ sign have - is it required when you reference a sheet?

rrjegan17 - I did not see in your reference anything that mentioned Excel. I looked around on that site and saw an example under OpenDataDource (http://msdn.microsoft.com/en-us/library/ms179856.aspx) but when I tried that with the example I got
"The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" does not contain the table "Sheet1". The table either does not exist or the current user does not have permissions on that table."

ralmada:- I looked at the properties for the Linked Server I previously added and the Data Source shows the file that I want to query....
0
 
edrz01Author Commented:
rrjegan17, when I tried this:
Got it!
It dawned on me that while I was referencing a file on my local computer, SQL2005 is on a remote server and I am using SQL Server Mgmt Studio running on my computer. If I copy the file in question to the local SQL server and remove the space in the sheet name I was finally able to query it!

Now I just have to find a way to deal with the spaces in the UNC for the share, the filename and the page.

Thanks again for the assist!
0
 
edrz01Author Commented:
One last question.... does this support UNC or linking to a file on another server? I can't seem to get it working again if I point to a mapped drive or UNC.
0
 
ralmadaCommented:
Hey rrjegan17, that's the same link I've suggested on the first place :)
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Yes ralmada..
You have referenced that link to give him an option of using Linked Server.
But I have referenced the same link to give him an alternate option to SQL Server ie., OPENROWSET method.

edrz01,
   Yes it supports UNC naming. If you have spaces within your UNC name kindly give it within "\\network share\share path\excel file name.xls ". That will work fine.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.