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.

posted @ Monday, April 02, 2007 11:47 AM

Print

Comments on this entry:

# re: Getting results of Query Analyzer into Excel

Left by Stephen Moore at 4/4/2007 4:24 PM
Gravatar
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.

# re: Getting results of Query Analyzer into Excel

Left by harsh athalye at 4/14/2007 9:31 AM
Gravatar
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.

# re: Getting results of Query Analyzer into Excel

Left by Rob A at 7/16/2007 8:01 AM
Gravatar
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.

# re: Getting results of Query Analyzer into Excel

Left by Mase at 1/8/2008 3:25 AM
Gravatar
I've tried all formatting but excel is still not showing the data......even though Microsoft Query is showing the results set..

Help!!!

# re: Getting results of Query Analyzer into Excel

Left by Sajan at 3/5/2008 7:09 AM
Gravatar
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

# re: Getting results of Query Analyzer into Excel

Left by Immad at 4/16/2008 9:22 AM
Gravatar
Thank you for this post. It works great!!

# re: Getting results of Query Analyzer into Excel

Left by John at 4/16/2008 1:12 PM
Gravatar
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?

# re: Getting results of Query Analyzer into Excel

Left by Nas at 11/19/2008 5:30 AM
Gravatar
@Sajan - how can we hold of this tool? I have nearly 200 queries I need to automate into Excel! Appreciate any feedback.

# re: Getting results of Query Analyzer into Excel

Left by sports man at 4/26/2009 11:02 PM
Gravatar
Interesting!
This may be helpful

# re: Getting results of Query Analyzer into Excel

Left by Phoenix at 9/27/2009 11:48 PM
Gravatar
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')

# re: Getting results of Query Analyzer into Excel

Left by columbia jackets at 10/23/2010 8:34 AM
Gravatar
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

# re: Getting results of Query Analyzer into Excel

Left by Pavan at 10/27/2010 9:26 PM
Gravatar
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

# re: Getting results of Query Analyzer into Excel

Left by asigurari locuinte at 8/5/2011 4:49 AM
Gravatar
Excel helps you to view your data more easily. You can analyse very simple all your results and make different comparisons.

# re: Getting results of Query Analyzer into Excel

Left by essay services at 8/19/2011 4:16 AM
Gravatar

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.

# re: Getting results of Query Analyzer into Excel

Left by billig bilforsikring at 8/29/2011 10:00 PM
Gravatar
This tutorial really help me. Thank you for posting this.

# re: Getting results of Query Analyzer into Excel

Left by asigurari locuinte at 9/8/2011 2:55 AM
Gravatar
I always had problems with getting results of Query Analyzer into Excel. Thanks for clearing that out for me.

# re: Getting results of Query Analyzer into Excel

Left by asigurari locuinte at 9/23/2011 2:08 PM
Gravatar
This tutorial really help me.

# re: Getting results of Query Analyzer into Excel

Left by mancare chinezeasca at 10/26/2011 10:43 AM
Gravatar
Excel is a very powerful tool. It can do magic in the proper hands.

# re: Getting results of Query Analyzer into Excel

Left by florari.com at 12/21/2011 6:55 AM
Gravatar
Excel helps you to view your data more easily. You can analyse very simple all your results and make different comparisons.

# re: Getting results of Query Analyzer into Excel

Left by Online math tutoring at 12/26/2011 5:32 AM
Gravatar
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 :)

# re: Getting results of Query Analyzer into Excel

Left by nexium vs prilosec at 3/12/2012 8:43 AM
Gravatar
You can analyse very simple all your results and make different comparisons.

# re: Getting results of Query Analyzer into Excel

Left by nexium vs prilosec at 3/12/2012 8:44 AM
Gravatar
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

# re: Getting results of Query Analyzer into Excel

Left by Vicodin dosage at 3/13/2012 6:11 AM
Gravatar
I've tried all formatting but excel is still not showing the data......even though Microsoft Query is showing the results set..
Vicodin dosage

# re: Getting results of Query Analyzer into Excel

Left by andysjq at 6/11/2012 8:52 PM
Gravatar
yes,I find you

# re: Getting results of Query Analyzer into Excel

Left by sdfsdf at 6/11/2012 8:57 PM
Gravatar
If you have enough income All right,that I agree with you ,I'll focus on you!come on!.

# re: Getting results of Query Analyzer into Excel

Left by Florarie.ro at 7/23/2012 2:20 AM
Gravatar
There is a more convenient way to use excel. It is very simple!

# re: Getting results of Query Analyzer into Excel

Left by joypolo at 9/15/2012 1:38 AM
Gravatar
excel to change it
Comments have been closed on this topic.
«October»
SunMonTueWedThuFriSat
2829301234
567891011
12131415161718
19202122232425
2627282930311
2345678