I often get questions that how to fix the issue where excel loses decimal values when values are pasted from SSMS Resultset.
Well the answer is simple –
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 (https://blog.sqlauthority.com)
9 Comments. Leave new
how about preserving leading zero? may be same way as you mentioned in your article but I wanted to have it for only one column? Any easy idea?
1. Right click on cell
2. select Format cell
3. select Number
4. Increase/Decrease Decimal Places according to your need.
I’m not sure why you’d want to do that? Decimal places are missing intentionally because Excel is simply representing the number, not the textual layout.
In the solution you’ve given, they’re now text and you can’t sum them up or perform operations on them. If you genuinely only want the exact same representation then fine, otherwise you’re better off correctly formatting them as a custom format using something like 0.00000?
I find the most common time this causes a problem is with things like order numbers which have leading zeros in which cause using the text formatting before pasting is acceptable because I don’t generally ever have to perform numeric operations on order numbers.
Worked great. Thanks!
Thank you for this one Pinal. Encountered the issue when dealing with phone numbers without the international prefix.
Hi Dave, in my SSMSBoost add-in I have implemented “Results Grid scripter”, that exports Results Grid as Open XML Spreadsheet. Benefits:
-datatypes remain preserved. Numbers remain numbers, text remains text (etc). Excel does not try to convert anything on it’s own
-you can export selection, one or even ALL ResultGrids, if you have several. In this case each Results Grid is exported as own Worksheet in same Excel document…
This solution among many others is not working for me, instead I upload data as text after I multiply to get rid of commas into decimal table column and then I update table and divide to cancel previous number