Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Decimal separator missed when pasting into excel from sql query Expand / Collapse
Author
Message
Posted Monday, January 7, 2013 5:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 9, 2013 12:08 PM
Points: 3, Visits: 5
I have an sql query which returns some numeric values that sql server is representing with a decimal point. Since we are using a Spanish version of Excel, excel is expecting a decimal comma instead of a decimal point, thus when I copy data in SSMS then paste into Excel value 1.24219 is interpreted as 124.219 when it should be 1,24219

I have solved this using the replace function in my query, as in:

select replace(1.24129,'.',',')

the drawback is I have to use a replace function for every decimal column which makes all sql queries harder to read and is more time consuming, both for writing and for the sql execution itself.

A second workaround is going into excel, options, advanced options, and telling excel it won't use local regional settings but always use '.' as a decimal separator, which is not nice either because we are not used to see the point as a decimal separator in Spanish.

I think there must be a more elegant way to let SQL Server Management Studio I want to see all decimal separators with a comma instead of a point, is there any way of doing this?

Thank you,

Post #1403558
Posted Monday, January 7, 2013 12:25 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:31 AM
Points: 194, Visits: 717
hi there,

I think you are one of thousands having this problem. I personally still don't know any easy solution to this. I personally work with a third solution: copy the result into a test editor, use the "find and replace"-command to replace the dot with a comma, and then insert it into the excel file.
Otherwise I use the method with the replalce-function too.
Post #1403809
Posted Monday, January 7, 2013 12:57 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
I don't think that's an issue with SQL Server. I think that's an issue with Management Studio. Is the workstation you're running queries on set to the proper localization settings? I think SSMS inherits those from the workstation.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1403832
Posted Monday, January 7, 2013 1:03 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:31 AM
Points: 194, Visits: 717
This is a problem many people have. In the german language the decimal separator is a comma too. When having the english database system all decimal separators are returned with a dot.
When switching the local country settings the decimal separator is changed in management studio but also in excel. And, by the way, in ALL other programs what you don't want.
What is needed is a configuration for the decimal separator for the management studio only. So one could change the output to get the comma as decimal separator and copy it to excel without having problems. But this configuration is still not existing.
Post #1403837
Posted Monday, January 7, 2013 1:29 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Since SSMS is designed to inherit that kind of thing from Windows, I think the only solution would be to run SSMS in a virtual computer, with the localization set on that. Seems like overkill for this, but it should get the job done.

Of course, another solution would be to build an add-on for SSMS that would do this kind of thing. Should be possible. After all RedGate built add-ons for SSMS, as do many other companies. Not sure that solves the problem for you, but if there are really thousands of people who need this (and it makes sense that there are at least thousands), then that might be a good product with a real market and might make someone some real money if they can get it working.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1403852
Posted Tuesday, January 8, 2013 3:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 9, 2013 12:08 PM
Points: 3, Visits: 5
Thank you very much to you all for your answers. At least I don't feel like a complete newbie as I can see there is not a straightforward solution to this problem.

I think the same way as GSquared - that this seems to be a problem inside SSMS and since my database is running on windows 2008 server running in English (both the OS server and the SQL Server) I wonder if the results would be the same should the database and OS be in Spanish... maybe I will try this but those being in English is a requirement from our ERP vendor.
Post #1404475
Posted Wednesday, January 9, 2013 11:10 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
fbernausberraondo (1/8/2013)
Thank you very much to you all for your answers. At least I don't feel like a complete newbie as I can see there is not a straightforward solution to this problem.

I think the same way as GSquared - that this seems to be a problem inside SSMS and since my database is running on windows 2008 server running in English (both the OS server and the SQL Server) I wonder if the results would be the same should the database and OS be in Spanish... maybe I will try this but those being in English is a requirement from our ERP vendor.


Yeah, I don't think Microsoft anticipated this particular problem. Might try running SSMS in a VM that's set to Spanish. That might work.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1404907
Posted Wednesday, January 9, 2013 11:39 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:45 PM
Points: 1,786, Visits: 5,681
or you might be able to set up a local / domain user with spanish region settings and just run SSMS as that user using the "runas" command...

not nice, but it might help.


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1404920
    Posted Wednesday, January 9, 2013 12:10 PM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Wednesday, January 9, 2013 12:08 PM
    Points: 3, Visits: 5
    That's a good one mister.magoo !
    Post #1404938
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse