SSMS Export to Excel

Problem

You’re running an ad-hoc query in a Microsoft SQL Server database with SQL Server Management Studio (SSMS) and need to further analyze the result set in an Excel spreadsheet. How do you export the data?

Solution

SSMS gives us a few options to get our results to Excel. As with most problems, there is more than one way to solve it so we’ll step through more than one solution that can all get us to the same place.

Getting Query Results from SSMS to Excel

These are the four methods of getting query results from SSMS to Excel we’ll be covering:

1. Copy and paste from the results tab
2. Save results as a delimited file
3. Saving results directly to a fixed-width .rpt file
4. Using the SQL Server Import and Export Wizard

You would likely be working with a lot more data than this, but in the interest of keeping the screenshots clean and readable, and focusing on the solution instead of the data, we’ll run the following T-SQL query in an SSMS Query Window to obtain a list of product names and models from the AdventureWorksLT2019 database.

USE AdventureWorksLT2019;
GO
 
SELECT [p].[Name]  AS [ProductName]
      ,[pm].[Name] AS [ProductModel]
FROM   [SalesLT].[Product]      [p]
  JOIN [SalesLT].[ProductModel] [pm] ON [p].[ProductModelID] = [pm].[ProductModelID]
ORDER BY [p].[Name];
GO

Note: Examples were done with SSMS 18.11.1 which is the latest version as of this writing and Microsoft Excel for Microsoft 365.

Copy and Paste from Results Tab

This method requires the results to be sent to the default grid output. If the results are not going to the grid here is how to change it:

1. Right-click in the Query Window
2. Results To
3. Results to Grid

Or simply Ctrl+D

To make the setting permanent

1. Tools
2. Options…

1. Query Results
2. SQL Server
3. General
4. Results to grids in the Default destination for results: dropdown

Okay, now we have our results going where we want them, we can quickly and easily get those results into Excel with a simple copy and paste. Note: you can also select a contiguous subset of the records but for this example, we’re presuming you’ve already filtered out what you want in the query.

1. Click the box in the upper left-hand corner of the Results pane to highlight all records
2. Click on Copy with Headers or Ctrl+Shift+C

Open a blank workbook in Excel

1. Right-click
2. Paste

Alternatively, Ctrl+V

And your results are pasted in the workbook and ready for your analysis.

One annoyance you may run into is you paste your data into Excel and concatenates your columns and pastes everything into one column like this.

If this happens:

1. Highlight the column
2. Choose Data from the Ribbon
3. ClickText to Columns

And the Text to Columns Wizard will open.

1. Next

1. Check the Tab box and be sure to uncheck any others
2. Finish

Now each column is in its own Excel column where it belongs.

No need to save the change. Excel will remember it.

Save Results as a Delimited File

In addition to copying and pasting, SSMS also provides the ability to export the result set to either a comma delimited or tab delimited file that can be opened with Excel. Instead of highlighting and copying the results:

1. Right-click in the Results window
2. Save Results As…

1. Select location to save the file
2. Name file
3. Select comma or tab delimited from Save as type dropdown
4. Save

1. Right-click file
2. Open with and choose Excel

The file can now be saved as an Excel spreadsheet.

Saving Results Directly to a Fixed-Width .rpt File

Instead of a delimited file, we may want to work with a fixed-width file. SSMS also provides the functionality to output the results directly to a fixed-width file without going to the result pane.

1. Right-click in Query Window
2. Results To
3. Results to File

Alternatively, Ctrl+Shift+F

Run the query and you’ll be presented with a dialog box.

1. Select the folder where you want to save the file
2. Give the file a name
3. Save

Open the file in Excel.

1. Folder
2. All files
3. Click on file
4. Open

This will open the Text Import Wizard.

1. Fixed Width instead of the default of Delimited
2. Uncheck My data has headers. If you don’t have headers
3. Next

1. Verify / edit break line(s)
2. Next

1. Change data type if you wish
2. Finish

Save as a .xlsx file.

Using the SQL Server Import and Export Wizard

We can also use the built-in SQL Server Import and Export Wizard. The Import and Export Wizard is a tool that uses SQL Server Integration Services (SSIS) to copy data from a source to a destination via an SSIS Package. Here, the source will be the query to obtain a list of product names and models and we’ll export the results directly to an Excel file destination.

1. Expand SQL Server in Object Explorer
2. Right-click on the database you’re exporting from
3. Tasks
4. Export Data…

1. Next

1. Select Data source: from the dropdown
2. Confirm or change Server name:
3. Leave Use Windows Authentication (if you’re using the credentials you’re running SSMS or choose Use SQL Server Authentication and enter login and password)
4. Confirm or change Database:
5. Next

1. Choose a location for destination file
2. Name file
3. Open

1. Choose a version from the Excel version dropdown
2. Leave the First row has column names checked if your data has headers, uncheck if not
3. Open

1. Paste in SQL (take out GOs)
2. Parse

1. OK

1. Next

1. Next

1. Next

1. Verify actions
2. Finish

1. Check for Success
2. Close

And here’s your file.

Next Steps

Here are some links to more tips and tutorials for SSMS export to Excel:

• SQL Server Management Studio Tips
• How to Export Data from SQL Server to Excel
• Export SQL Server Table to Excel with C#
• Export SQL Server Data to Multiple Excel Worksheets with Integration Services
• Export data from SQL Server to Excel
• Line Split Issues when Copying Data from SQL Server to Excel