Copy results grid data to Excel as an XML spreadsheet to preserve data types
When working on a management server with SQL Prompt and SSMS, we usually don't have Microsoft Office with Excel installed there. This makes it very cumbersome to use the current feature of SQL Prompt to export grid results to an Excel file as there is no application available on the server to process the file.
Instead, I often use a very nice feature in SSMSBoost that copies to grid results data to an XML spreadsheet which can then be pasted into Excel on your client, preserving data types. The regular copy/paste from SSMS to Excel only copies text data from the grid, forcing Excel to guess the data type. In addition, when copying text data that contains newlines or SQL code, you can't paste the data reliably in Excel.
When you copy to an XML spreadsheet instead, the copy/paste operation works reliably for all data types and strings, allowing Excel to correctly process the grid data and format it nicely in corresponding spredsheet cells.
For reference to how this feature looks in SSMSBoost, check the following link:
https://www.ssmsboost.com/Features/ssms-add-in-results-grid-data-to-excel
Jonas Olsson
shared this idea
-
Dan W
commented
It sounds like this would also make it easier to deal with our elevated user situation where I need to run SSMS as an elevated user account for any database updates, but it's difficult to keep Excel activated for that elevated user account (that gets used for Excel when selecting the "Open in Excel" option. If the "Open in Excel" feature allowed a copy-paste option, it would be easier to bridge that gap and still benefit from the wonderful Excel formatting that SQL Prompt does.