Getting results of Query Analyzer into Excel

Mon Apr 2, 2007 by Dinakar Nethi

I am sure every SQL Developer/DBA must have had to do this more than once. In SQL Server 2000, there is no way to copy the heading from the result set if the output is a grid. If the output is in text you could cut/paste but there were formatting issues especially with datetime columns and with columns that are of varcahr types and have numbers in them..etc.

Recently I was asked to provide some data in an Excel sheet. Some of my columns had values as:

1855760000019
1855760000019
714 Gresham St
4615 Willow Street
2620 Santa Domingo Dr

I tried the methods from The Best Kept Secret About SQL Query Analyzer @ SQLServerCentral.com but I had formatting issues.

The issue I had was that the excel sheet was showing the values as
1.85576E+12
1.85576E+12
714 Gresham St
4615 Willow Street
2620 Santa Domingo Dr

The only way I could get the actual value to show up was by formatting the cell content as "Fraction". Anything else I tried would either put comma's or currency symbols or percentage symbols. And I couldnt do this for each cell either since the result set was a few thousand records and there's no sense in going through each record and formatting. I have googled around and saw some tricks and hacks. Almost all of them dealt with some hacks from Query Analyzer. So I started looking at all the options available from Excel. Surely its not just some tool to pass around chunks of data. It can do more. And I did find my solution. So I thought I'd blog about this if any one else is in a similar situation.

Try this:
In Excel Under Data > Import External Data > New Database Query

If the Data Source is not already in there you can easily create one.
By default the option is selected. So click on OK and the wizard to create the new Data Source will appear.

Give a name
select the database driver (SQL Server is the last one in the list)
click "Connect"
Enter or choose the Server Name
Enter the type of connection - windows/sql
Click on "Options >>" Select the database from the list
If your data is coming straihgt from a table we wouldnt even be going through all this. So assuming the source is a query, leave the option for data source drop down blank and click "OK"
The Query Wizard to choose tables/columns appears. Click on "Cancel". Then close the window that pops up with table names again.
Click on the SQL tab. A new query editor pops up. Copy your query from the Query Analyzer into this editor.
Click on "yes" if it says the results cant be represented graphically.
The query results should appear in a tabular format.
Locate the button that says "Return Data" to the left of the SQL button and click it.

The results should be in the spread sheet now without losing any formatting or any unnecessary data conversions by excel.

Have fun.

Legacy Comments


Stephen Moore 2007-04-04	re: Getting results of Query Analyzer into Excel It's always a good idea to stick the original SQL query as comment on a field or something so that if an end-user wants the spreadsheet updated you can get back to it. If you don't know the DSN (for example the original spreadsheet was from last year, and you've upgraded PC's since then), it can be a real pain without the original SQL statement. These kinds of spreadsheets run rampant in our environment because invariably the thing that is "quick and dirty" today is still a requirement next year.

harsh athalye 2007-04-14	re: Getting results of Query Analyzer into Excel Thankfully, in SQL 2005 adding headers while copying results is pretty simple. You just have to set option "Include column headers when copying or saving the results". I tried this trick in SQL 2005 but doesn't seem to work. I tried it for numeric as well as float column...but with no luck.

Rob A 2007-07-16	re: Getting results of Query Analyzer into Excel This may be helpful. Select all Cells (Ctrl + 'A') on the Excel worksheet where you are going to import your data from SQL. Next, change the Format for all the cells to text. Now, do your import (don't bother selecting anything other general when excel asks for each column's datatype. I believe, not sure... excel will import everything in as text anyway.) Since the text format does not modify data, after completing your import, you should notice all your data appears exactly as it was in SQL. Now, If you need further formating on any of your imported columns, format with Excel as you need. Note: if you need to use excel math functions or functions that use math calculations on any of your data, you will need to convert that data from text format to number format, or currency etc.

Mase 2008-01-08	re: Getting results of Query Analyzer into Excel I've tried all formatting but excel is still not showing the data......even though Microsoft Query is showing the results set.. Help!!!

Sajan 2008-03-05	re: Getting results of Query Analyzer into Excel I have a tool (created in C#), run queries from a text file and it outputs a nice excel file with tabs for each query!. It also has the option to format the columns as how you need it displayed! -S

Immad 2008-04-16	re: Getting results of Query Analyzer into Excel Thank you for this post. It works great!!

John 2008-04-16	re: Getting results of Query Analyzer into Excel I've been doing this for a long time. Another beauty of it is by saving the Excel file with a descriptive title, you can later go back to a report you pulled last year, when a user calls back wanting it again updated for the current year, and just right-click in the data and choose "Edit Query" to modify the dates and rerun without having to re-invent. Why oh why did Excel 2007 make this such a MUCH more laborious process?

Nas 2008-11-19	re: Getting results of Query Analyzer into Excel @Sajan - how can we hold of this tool? I have nearly 200 queries I need to automate into Excel! Appreciate any feedback.

sports man 2009-04-26	re: Getting results of Query Analyzer into Excel Interesting! This may be helpful

Phoenix 2009-09-27	re: Getting results of Query Analyzer into Excel what if i want to use some data from a column i have in excel(since i have atleast 200 reg. no. and extract data from the database into excel ? eg: select (etc etc) from (etc etc) where regn In ('excel')

columbia jackets 2010-10-23	re: Getting results of Query Analyzer into Excel Recently I was asked to provide some data in an Excel sheet. Some of my columns had values as: columbia jackets \| snow boots \| snow boots for women \| columbia sportswear \| columbia sportswear outlet \| cheap north face jackets \| the north face outlet \| mac makeup \| cheap makeup

Pavan 2010-10-27	re: Getting results of Query Analyzer into Excel Help me in getting the temp table data from sql to excel note; i do not have permesion to creat tables and procesure, so i have to get the data directly from temp file i.e.., #table

asigurari locuinte 2011-08-05	re: Getting results of Query Analyzer into Excel Excel helps you to view your data more easily. You can analyse very simple all your results and make different comparisons.

essay services 2011-08-19	re: Getting results of Query Analyzer into Excel Thank you for this tutorial. Had been trying to Getting results of Query Analyzer into Excel , but I am new to it, I did encounter a few puzzling questions and even a couple of headaches.

billig bilforsikring 2011-08-29	re: Getting results of Query Analyzer into Excel This tutorial really help me. Thank you for posting this.

asigurari locuinte 2011-09-08	re: Getting results of Query Analyzer into Excel I always had problems with getting results of Query Analyzer into Excel. Thanks for clearing that out for me.

asigurari locuinte 2011-09-23	re: Getting results of Query Analyzer into Excel This tutorial really help me.

mancare chinezeasca 2011-10-26	re: Getting results of Query Analyzer into Excel Excel is a very powerful tool. It can do magic in the proper hands.

florari.com 2011-12-21	re: Getting results of Query Analyzer into Excel Excel helps you to view your data more easily. You can analyse very simple all your results and make different comparisons.

Online math tutoring 2011-12-26	re: Getting results of Query Analyzer into Excel I'm very curious how this interrogations work in Excel. I worked with sql data bases, with queries in Microsoft Access, but I'm new with Excel. I just know to use a few functions. Shame on me :)

nexium vs prilosec 2012-03-12	re: Getting results of Query Analyzer into Excel You can analyse very simple all your results and make different comparisons.

nexium vs prilosec 2012-03-12	re: Getting results of Query Analyzer into Excel You can analyse very simple all your results and make different comparisons. I'm very curious how this interrogations work in Excel. nexium vs prilosec

Vicodin dosage 2012-03-13	re: Getting results of Query Analyzer into Excel I've tried all formatting but excel is still not showing the data......even though Microsoft Query is showing the results set.. Vicodin dosage

andysjq 2012-06-11	re: Getting results of Query Analyzer into Excel yes,I find you

sdfsdf 2012-06-11	re: Getting results of Query Analyzer into Excel If you have enough income All right,that I agree with you ,I'll focus on you!come on!.

Florarie.ro 2012-07-23	re: Getting results of Query Analyzer into Excel There is a more convenient way to use excel. It is very simple!

joypolo 2012-09-15	re: Getting results of Query Analyzer into Excel excel to change it