SQL SERVER – Excel Losing Decimal Values When Value Pasted from SSMS ResultSet
- by pinaldave
No!
It is not a SQL Server Issue or SSMS issue. It is how things work. There is a simple trick to resolve this issue.
It is very common when users are coping the resultset to Excel, the floating point or decimals are missed.
The solution is very much simple and it requires a small adjustment in the Excel. By default Excel is very smart and when it detects the value which is getting pasted is numeric it changes the column format to accommodate that. Now as Zero which are training any digit after decimal points have no value, Excel automatically hides it. To prevent this to happen user has to convert columns to text format so it can preserve the formatting.
Here is how you can do it.
Select the corner between A and 1 and Right Click on it.
It will select complete spreadsheet. If you want to change the format of any column you can select an individual column the same way. In the menu Click on Format Cells…
It will bring up the following menu. Here by default the selected column will be General, change that to Text. It will change the format of all the cells to Text.
Now once again paste the values from SSMS to the Excel. This time it will preserve the decimal values from SSMS.
Solved!
Any other trick you do you know to preserve the decimal values? Leave a comment please.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, SQL Utility, T SQL, Technology Tagged: Excel
