Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
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: 11466 | Views in the last 30 days: 10
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



Performance of BCP versus OPENDATASOURCE


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones