SQL SERVER – Excel Losing Decimal Values When Value Pasted from SSMS ResultSet

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)

11 thoughts on “SQL SERVER – Excel Losing Decimal Values When Value Pasted from SSMS ResultSet

  1. 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.

    Like

    • Great point.

      Quite often people want to do this for display purpose, if they want to add the value or do any mathematical operation then you have to either do this in SQL Server or convert them to INT format in Excel.

      Like

  2. Pingback: SQL SERVER – Preserve Leading Zero While Coping to Excel from SSMS « SQL Server Journey with SQL Authority

  3. 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…

    Like

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #048 | Journey to SQL Authority with Pinal Dave

  5. Pingback: SQL SERVER – Weekly Series – Memory Lane – #049 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s