Save SQL Results to Excel

[CrystalStart]

I am executong statement in SQL and i need to save results to an Excel file with headers as nice looking as possible.
How do I do that?
Thanks

 

[esdee]

try with a dts package

------------------------

http://esdeeblog.blogspot.com/

dev at viewmoresoft.com

http://www.viewmoresoft.com

tools for your database

 

[CrystalStart]

what am I looking for in dts? thanks

 

[Catadmin]

There are several methods in DTS. I think the simplest is the DTS Import/Export Wizard. Right click the database you're pulling the data from and navigate to All Tasks -> Export Data.

Look up DTS in Books Online. This should help you. Additionally, check the DTS forum for FAQs that can help you accomplish your task.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[CrystalStart]

I can not believe nobody ever saved query result to Excel with headers? How can I click database I am pulling from?
I am totally confused...

 

[dhulbert]

Crystal.
If you export to excel from SQL server you just get the raw data and then you need to tidy it up.
If you use the SQL server statement to create a view or a temprary table then set up a database query in Excel to pulll the data out you will get much more control over the format of the data.

 

[Pattycake245]

CrystalStart,

This may not be what you're after but you could run your query in Query Analyzer in grid mode(ctrl-d). You then click on the top left corner which will select all rows and you can save that as a CSV. You could then open that up and save as Excel. This is not very elegant and may not be what you need but I thought I'd offer it regardless.

Tim

 

[Catadmin]

Since Pattycake245 started this, I'll continue. @=)

I've done this from Enterprise Manager. Opened all rows in a table, hit the SQL button, entered my SQL statement and hit the red ! sign. Then highlighted all the results and columns by left clicking on the first column and moving the mouse pointer all the way to the last column while the left button is still down. Right click and choose copy, then paste into an open but blank Excel spreadsheet.

It keeps the headers and all. Unfortunately, there's no way to only copy certain columns from this. And usually you have to hide the ones you don't want to see in Excel and expand others that are longer than Excel's default column width so you can see all the values instead of the first several letters.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[pullesj]

If the query is not too complex you could use Excel directly using 'Data' -> 'Get External Data' if Microsoft Query is installed on your PC. You would need to set up a connection to the database (ODBC) and then write/build your query and save it via Microsoft Query. You then return the data to Excel. You would need to set the properties of return to not include your column headings. You must save the query to be able to dynamically refresh the data if the data is not static using 'Data' -> 'Refresh Data'. This solution is for simple queries (or, I should say, I only use this method for simple queries as I've had some difficulties saving more complex queries and being able to refresh the data) connecting to a single database.

 

[CrystalStart]

Right now I am using Pattycake245 method but when it comes to a long result with tons of headers to copy/paste - it is waste of time.
I will try all of yours methods and thanks to all for being here for me.
Good weekend

 

[CrystalStart]

OK, I am back with this.
I am executing my query in Query Analyzer (store procedure to be exact) and results are saved as csv file.
Now can I open csv file and save it to look like this:

R,,,NECN 90,8009720481,02/21/2005,13:42:55,1,0,,0,0,0,0,0,0,0,0,0,N,,,,,,,,,,,

Many thanks