MySQL Bugs: #12572: Decimal comma in Excel changed to decimal point with vs 5.0.10

Bug #12572	Decimal comma in Excel changed to decimal point with vs 5.0.10
Submitted:	14 Aug 2005 7:04	Modified:	30 Aug 2005 16:23
Reporter:	Lars Johansson	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51.12	OS:	Windows (Windows XP pro)
Assigned to:		CPU Architecture:	Any

Description:
See my bugreport 12388.The report was rejected as no bug. 
The problem is not only cosmetic, as Excel do not recognise decimals from 5.0.10 as numeric values, e.g. a pivot table shows blanks instead of computed numeric values. I have tried to find a solution in Excel and MS query but I failed. 
I have traced myODBC driver 3.51.12.00 and found it delivers different data types to MS query and Excel depending on Mysql server release. It seems the data types given to Excel is not correct according to mysql manual 25.1.17.
I import one decimal(2,1) column (value=1.1) from mysql 4.1.11 and from mysql 5.0.10.

Import from mysql 4.1.11.
The column arrives to MS query as SQL_C_CHAR and is right truncated. The value shows as ‘1,’. When I import the column to Excel the datatype is SQL_C_DOUBLE and the value is ‘1,1’ and this is treated as numeric in Excel. This is fine to me!

Import from mysql 5.0.10.
The column arrives to MS query as SQL_C_CHAR. The value shows as ‘1.1’. When I import the column to Excel the datatype is SQL_C_WCHAR and the value is ‘1.1’ and this is not treated as numeric in Excel. This is not ok for me!

If this is not a bug, I would appreciate if anyone could advice a solution or where to start looking for a workaround.

How to repeat:
Import decimal values into Excel from mysql 4.1.11 and 5.0.10
and compare the result.

Looking at the active bugs I found 12545, which probably is the same problem I have.

Sorry. I was not able to reproduce the bug. In both case Excel returned numeric value with decimal point according to international options.

I don't think this is a problem using a locale where decimal point is used.
My problem will probably only show up when you are using decimal comma.

My Windows environement:
Windows XP professional 5.1.2600 SP2 build 2600
Microsoft Office Excel 2003 (11.6355.6360) SP1
Locale Sweden  

If I turn off 'use system separators' and explicit specify '.' (dot) as decimal separator in Excel (tools->options->international) everything is fine.
But we use 'system separators' and the decimal separator is ',' (comma).

You may be able to reproduce my problem by explicitly specify the decimal separator as ',' (comma) in Excel, and then try to use the value (1.1) in a calculation. E.g. if the value 1.1 is in cell A2, create a sum in cell B2 as
=sum(A2), then you would get the result 0 which is not correct.

Nota Bene! You will get the value (1.1) into an Excel cell allright, but you can't use it in calculations because Excel does not recognize 1.1 as a numeric, as the decimal separator is ',' (comma).

if I change the separator in Excel, Excel replaces all separators in all float values in book. I tried various ways to reproduce the bug, but I was not able. I attached screen copy of result.

screen copy

Attachment: screencopy.GIF (image/gif, text), 6.74 KiB.

Hi Vasily,
thanks for your efforts. 
It seems we have incompatible environments in some way. However I looked through the ODBC bug reports and I found verified bug reports I suspect describe the same problem I have.
I suggest you close my bug. 
I keep an eye on updates on the ODBC driver. If I come up with something new I open a new case.