Tricks - Copy T SQL Result to Excel

,

Sometimes, some of the silliest of tricks save so much of time and effort. Very common scenario is to copy a query result to excel. Most take either of the 2 approaches

* Copy the result from result grid of management studio and paste to Excel as shown below


The problem with above approach is sometimes, when one pastes the result to excel, the formatting can go haywire, especially if the result contains carriage return (new line) and commas as shown below.


* Export to excel via export wizard

Little tedious and sometimes getting the mapping right can be a pain.

Over the years, the simplest and effective technique, I have used is running T SQL query directly from excel (Yes, you can do that :)). Not just that, it gives a cleanly formatted result all the time!!! Step by step instructions provided below

How to run TSQL Query from Excel:


Open Excel and move to data tab and click on the icon "Other Data Sources" and select "SQL Server" as shown below.


Enter the server to connect to and the credentials


Select the database against which you want to run the query. Pick a random table (doesn't matter if you are not going to query it. Any table is fine)


Click next and finish (we are not done yet :))


Click on properties


Uncheck background refresh check box


Click on the "Definition" tab and change the command type to "SQL" from "Table"


Paste the query you want to run on the "Command Text" box as shown below


Result would be a nice formatted excel sheet as shown below


From excel once can not only get select query results but stored procedures as well. Excel also servers as an excellent tool to query database when one needs to access the database on a machine that doesn't have SQL Server Management Studio. Hope the tip was useful!!!

Original post (opens in new tab)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating