How do you transfer or export SQL Server 2005 data to Excel.

Question

I have a simple SQL 'Select' query, and I'd like to dump the results into an Excel file. I'm only able to save as .csv and converting to .xls creates some super ugly output. In any case, as far as I can tell (using Google) this doesn't seem to be so straight forward. Any help would be greatly appreciated.

Answer 1

Use "External data" from Excel. It can use ODBC connection to fetch data from external source: Data/Get External Data/New Database Query

That way, even if the data in the database changes, you can easily refresh.

Answer 2

SSIS is a no-brainer for doing stuff like this and is very straight forward (and this is just the kind of thing it is for).

1. Right-click the database in SQL Management Studio
2. Go to Tasks and then Export data, you'll then see an easy to use wizard.
3. Your database will be the source, you can enter your SQL query
4. Choose Excel as the target
5. Run it at end of wizard

If you wanted, you could save the SSIS package as well (there's an option at the end of the wizard) so that you can do it on a schedule or something (and even open and modify to add more functionality if needed).

Answer 3

If you are looking for ad-hoc items rather than something that you would put into SSIS. From within SSMS simply highlight the results grid, copy, then paste into excel, it isn't elegant, but works. Then you can save as native .xls rather than .csv

Answer 4

Here's a video that will show you, step-by-step, how to export data to Excel. It's a great solution for 'one-off' problems where you need to export to Excel:
Ad-Hoc Reporting

Answer 5

You could always use ADO to write the results out to the worksheet cells from a recordset object