The latest update to SSMS 22 adds 4 new results export formats: Excel, JSON, Markdown, and XML.
In this post I cover where to find the new export formats and one gotcha you need to be mindful of.
Export formats in previous versions of SSMS
Anyone who’s ever worked with SQL Server Management Studio needed, at some point, to export the result of a query to a file.
Unfortunately, previous versions of SSMS were restricted in terms of output formats to CSV and text files.
These were great formats back when grandma was querying AdventureWorks1965, but they haven’t been enough for a while now.
Especially since most folks want to export data to Excel and the only “quick export” option in this case was just copy-pasting the results into a workbook. (And, no, not everyone is aware of the Data Export/Import Wizard.)
This is great for small result sets, but loading a large result set into the clipboard on workstations with insufficient resources was hit or miss. Plus you didn’t always end up with the right format.
How to export to Excel, JSON, Markdown, and XML from SSMS’s results grid
First, just execute your query with the Results to Grid selected (this is the default mode).
I’m running the following query against the AdventureWorks database
| 1 | SELECT * FROM [HumanResources].[vEmployee]; |
Once the result is rendered in the grid, go to the header of the result set, right click, and, in the newly opened context menu, click on Save Result As… .
A Save Grid Results dialog opens. And here you can specify the location (this defaults to the Documents folder), the file name and type.
In the Save as type drop-down you can now pick between SSMS 22.4.1’s 6 available formats: CSV, text, Excel, JSON, Markdown, and XML.
Note that you can control the default export location from Tools > Options > All Settings > Query Results > SQL Server > General, where you can pick another location in the Query Results Directory section.
If you’re curios about other options you can change to improve your work with SSMS, check out my post about my SSMS 22 config.
Gotcha when saving to Excel
By default, when exporting a result set to Excel, the file won’t contain the column headers.
To fix that go in All Settings > Query Results > SQL Server > Results to Grid and check “Include column headers when saving or dragging from the results grid”.
This change doesn’t need a restart of SSMS to take effect.
But, at least from my tests, works only after opening a new query editor tab, running the query in it, and exporting from there.
Exporting to Excel does not require Excel to actually be installed
I’m sure this will probably one of those questions that lots of folks will ask the first time they see this option.
Rest assured, neither Excel nor the rest of the O365 suite needs to be installed on the the machine for the export to Excel to work.
Sure, to open it you’ll need to copy that file to another machine that has Excel installed or move it to SharePoint and use Excel online, but that’s just required for reading the resulting file.
Does this work when results are directly written to a file instead of the grid?
Don’t worry, I’m not breaking Betteridge’s law here. The answer is No.
For the time being, the only file type available here is rpt.
Conclusion
I’m sure the ability for SSMS to export directly Excel, JSON, Markdown, and XML, is a welcomed enhancement for anyone working with SSMS.
This also knocks another item off my wish list for SSMS 21 (yup, 21).
If you haven’t made the switch from SSMS 21 to 22 due to the lack of an option to migrate your connection history, check out this blog post. I got you covered 🙂 .