Connect a SQL Server database to your Excel workbook

How many times a day do you open a table or run a procedure and want to review the results in Excel. You right click on the result set to select all, select copy with header, open Excel, select the entire sheet and format as text then paste. There is a better way. Under the data menu in Excel you will find some menu selections for pulling in external data. If you select “From Other Source” you will see that there is a selection for SQL Server. With the server name and your windows authentication you will be presented with a list of Views and Tables that you can select from in each database. It is fast and easy to open a table for review in Excel. The data connection wizard creates an ODC connection to the data and pulls it into Excel. Once the connection is made you will be able to reuse the ODC connection anytime via Existing Connections. Check it out.

Please keep in mind that this process for selecting data from a table is the same as if you opened a table without any criteria, it returns the entire table. Do not use this for production unless you know how many records you will be retrieving. The impact on production can be substantial.


Leave a Reply

Your email address will not be published. Required fields are marked *