SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

My favorite way to access Excel files

By Gaby Abed,

Here is my personal favorite way to quickly access data in Excel files. If you want an intuitive way to add data to a table from an .xls file, or simply query the file as if it were a table, select from opendatasource(...) is my personal favorite easy way to do so.

In my example, using SQL 2008 Server Management Studio, I right-clicked on a grouping of registered servers and selected new query (a great new feature of SQL 2008). This opens a query analyzer window with a pink bar at the bottom indicating that multiple servers are about to be queried. Use this feature with caution though, especially in mixed SQL 2000 and 2008 environments, as well as production environments, as not all queries are compatible across all servers.

In this query window, I executed exec sp_validatelogins which gave me a list of all potential orphaned logins along with the server associated with it. After copying the data to a spreadsheet, I had two columns, Server and Orphaned Logins, and saved it as c:\temp\invalidlogins.xls

To query that spreadsheet, keep the middle two lines the same as in the script provided, except for a) changing the path of the file and b) selecting the appropriate sheet name.

You can also use this method to join two queries on the spreadsheet. If you had data on Sheet1 and Sheet2, you could do a join on the two as if they were tables. As for how OPENDATASOURCE works on massive amounts of data as compared to BCP, you may still want to consider using BCP to do so, but for the flexibility of querying a spreadsheet or two, this way of using OPENDATASOURCE is one option to consider.


Gaby Abed

Total article views: 11527 | Views in the last 30 days: 3
Related Articles

Opendatasource problems with excel

Need to update 50 excel spreadsheets.


Reading Ad Hoc Text Files with OpenDataSource

Discusses the techniques and reasons to use opendatasource for reading text files in SQL Server 2005...


file name in OPENDATASOURCE()

file name in OPENDATASOURCE()



Benefits of OPENROWSET & OPENDATASOURCE & where it is used and how


OpenDataSource query

INSERT INTO OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=D:\Book1.xls;Extended Propertie...