Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Decimal separator missed when pasting into excel from sql query


Decimal separator missed when pasting into excel from sql query

Author
Message
fbernausberraondo
fbernausberraondo
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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,
WolfgangE
WolfgangE
SSC Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
Points: 216 Visits: 777
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.
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
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
WolfgangE
WolfgangE
SSC Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
Points: 216 Visits: 777
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.
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
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
fbernausberraondo
fbernausberraondo
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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.
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
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
mister.magoo
mister.magoo
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2272 Visits: 7827
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


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • fbernausberraondo
    fbernausberraondo
    Forum Newbie
    Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

    Group: General Forum Members
    Points: 3 Visits: 5
    That's a good one mister.magoo !
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search