Dinakar Nethi Blog

Dinakar Nethi

Getting results of Query Analyzer into Excel

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')

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.

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 :)

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