• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 548
  • Last Modified:

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 ! !
0
edrz01
Asked:
edrz01
  • 4
  • 3
  • 2
  • +1
1 Solution
 
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
 
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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
 
Raja Jegan RSQL 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
 
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now