Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favourite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved 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: 11333 | Views in the last 30 days: 14
 
Related Articles
FORUM

Opendatasource problems with excel

Need to update 50 excel spreadsheets.

ARTICLE

Reading Ad Hoc Text Files with OpenDataSource

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

FORUM

file name in OPENDATASOURCE()

file name in OPENDATASOURCE()

FORUM

Difference between OPENROWSET & OPENDATASOURCE

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

FORUM

BCP vs. OPENDATASOURCE

Performance of BCP versus OPENDATASOURCE

Tags
bcp    
excel    
opendatasource    
 
Contribute

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
Editor, SQLServerCentral.com

Already a member? Jump in:

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