Bug #14983 Problems with "copy row values" (also, "copy field content") in non English-loc
Submitted: 16 Nov 2005 12:58 Modified: 3 Nov 2008 14:29
Reporter: Gisbert Selke (Basic Quality Contributor) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Query Browser Severity:S4 (Feature request)
Version:1.1.17 OS:Windows (Win XP)
Assigned to: CPU Architecture:Any

[16 Nov 2005 12:58] Gisbert Selke
Description:
I am working with QB 1.1.17 under Win XP in a German locale. Decimal
delimiter is comma (,), list delimiter is semicolon (;) -- standard in
Germany. I routinely work with data in MySQL and then (want to) paste
them into some spreadsheet (usually, OpenOffice Calc; but works the same
with MS Excel). (I know that I can also export to a file, but for small
result sets this is too tedious, especially since I often have many
small queries the results of which I then gather and post-process by
hand in a spreadsheet.

Problem 1: The Windows standard for passing to the clipboard something
consisting of various columns seems to be to pass in the individual
columns delimited by tabs. The programme that accepts the paste
interprets these as column delimiters, so a spreadsheet programme will
populate several adjacent columns with the data. QB seems not to honour
this standard. Instead, it passes in the values in each row delimited
with commas. On pasting this into the spreadsheet, everything ends up in
one single cell, which makes the result useless. Hence, "copy row
values" is unworkable, as it stands. (Again, this is an experience from
a German locale. Others may differ.) (Something similar has been reported as bug #9044, which entry appears to be stale.)

Problem 2: For both "copy row values" and "copy field content", any
number with a possible fractional part (float, double, ...) will be
copied to the clipboard with a decimal point. This is fine in an English
locale. In a locale using comma as decimal separator, however, pasting
this value into a spreadsheet will produce something interpreted as a
string (in the best case; in a medium case, the decimal point will
just vanish; in the worst case, the value will be interpreted as
a date and be completely mangled afterwards). Again, this makes pasting
useless or at least very tedious and error-prone. It would be nice if QB
respected the locale settings.

Note: I appreciate that people with a programmer's perspective might in
fact prefer decimal points instead of local decimal separator. For
these, it might be nice to have the decimal-point vs.locale separator to
be a configurable preference. However, the majority of users in a locale
would expect the locale settings to be honoured.

How to repeat:
Switch Win XP to German locale (control panel "regional and language
settings", choose "Deutsch (Deutschland)". Possibly check settings
using "configure" button(?) next to drop-down box.
(Re-)start the spreadsheet of your choice, create new spreadsheet.

Now, back in QB:
create table t (i int, f float)
insert into t values (42, 3.14159)

For problem 1:
select * from t
click on row, "copy row values".
Switch to spreadsheet, highlight cell A1, and paste.
Expect:
A1: 42              B1: 3.14159
Get:
A1: "42, 3.14159"   B1: <empty>

For problem 2:
select f from t
click on single result row, "copy field content"
Switch to spreadsheet, paste the just-copied value into some clean area.
Expect:
3,14159
Get:
3.14159
(Check by whatever means your spreadsheet has that this is indeed
interpreted as a string, not a number.)
[16 Nov 2005 13:03] Gisbert Selke
I'm not sure whether this should be classified as a "feature request" (as indicated by Aleksey in bug #9044) or as a bug. At least problem 1 is one where the behaviour is non-compliant to Windows "standards" (yeah, I know that term is problematic ;-) ).
[3 Nov 2008 14:29] Susanne Ebrecht
Many thanks for writing a feature request. Unfortunately, this is not a MySQL bug this is a Microsoft Excel bug. Try to fill out a numeric data with . instead of , in a German Excel...

Unfortunately I couldn't find a option in Excel how to switch languages or to change this. 

What you can do here is to set the "Zellenformat" in Excel to Text.

Best would be you will ask Microsoft how to handle international communication with Excel.