Getting results of Query Analyzer into Excel
I am sure every SQL Developer/DBA must have had to do this more than once. 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.
Check 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.