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

YourMomzThaBomb_16830 0
Viewed 259355

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.

sql-server sql
14 Answers

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).

Ryan_Farley_16270

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.

user_82200

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

Mitchel_Sellers_132790

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

Michael_K_Campbell_111910

It's a LOT easier just to do it from within Excel.!! Open Excel Data>Import/Export Data>Import Data Next to file name click "New Source" Button On Welcome to the Data Connection Wizard, choose Microsoft SQL Server. Click Next. Enter Server Name and Credentials. From the drop down, choose whichever database holds the table you need. Select your table then Next..... Enter a Description if you'd like and click Finish. When your done and back in Excel, just click "OK" Easy.

user_5439730

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

Russ_Cam_18310
Related