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

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 2

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.