MySQL Bugs: #106406: import csv with comma as decimal separator truncate the numbers

Bug #106406	import csv with comma as decimal separator truncate the numbers
Submitted:	8 Feb 2022 13:45	Modified:	26 Mar 8:46
Reporter:	Valerio Messina	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Workbench	Severity:	S1 (Critical)
Version:	8.0.41	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	data loss

Description:
importing a csv file using a comma as decimal separator, truncate the numbers to integers

How to repeat:
1) Save a csv file using comma as decimal separator (Italian locale)
2) define a table with a field with Datatype=DOUBLE
3) import the csv file
4) check the DOUBLE field, had the float truncated to integers

the field separator does not matter

Attachment: MySQL_Workbench_8.0.28_import.png (image/png, text), 16.03 KiB.

Hello Valerio Messina,

Thank you for the bug report.
I tried to reproduce your issue on windows 10 with workbench 8.0.28 and followed exact steps but I am not seeing any issues at my end.
Could you please provide sample CSV file? Thanks.

Regards,
Ashwini Patil

here the table definition with three field defined as DOUBLE

Attachment: MySQL_Workbench_8.0.28_tableDef.png (image/png, text), 157.43 KiB.

an example of CSV using comma as decimal separator

Attachment: DB_PSA_ANSI_lite.csv.7z (application/octet-stream, text), 550 bytes.

with attached csv, the value "0,0001" to field "value" is truncated to 0 and
value "1,67" to field "rthcw" is truncated to 1

I cannot find a Workbench settings to set the locale, so it should use the OS settings

Hello Valerio Messina,

Thank you for the requested details.
I tried to reproduce your issue with the csv file provided but I am not seeing any issues at my end. 
You can try adding double quotes for fields that contain commas. Since CSV files use the comma character "," to separate columns, values that contain commas must be handled as a special case.

Regards,
Ashwini Patil

8.0.28 test results

Attachment: 106406_test_results.PNG (image/png, text), 98.79 KiB.

sorry, our csv use ";" as separator, see the atteched csv example.
Workbench correctly recognize the field separator, see attached import dialog.
I have no decimal separator selection below preview, like in your image, see attached import dialog.
Note: my preview is always correct, but the resulted import is wrong

Import dialog

Attachment: MySQL_Workbench_8.0.28_decimalImport1.png (image/png, text), 25.30 KiB.

Import dialog

Attachment: MySQL_Workbench_8.0.28_decimalImport0.png (image/png, text), 17.79 KiB.

I saw another strange difference between your import dialog and my.

In the Columns: section you have:
Source Column   Field Type

While I have:
Source Column   Dest Column

Are you using the standard 8.0.28 ?

about dialog

Attachment: MySQL_Workbench_8.0.28_about.png (image/png, text), 18.02 KiB.

here the result of the import, ID changed to 3904/5 to keep near

Attachment: MySQL_Workbench_8.0.28_decimalImport2.png (image/png, text), 99.48 KiB.

are you able to reproduce the bug with my Workbench version?

may be related to Community version only.
The one that I'm using now is
"Workbench Version 8.0.28 build 1474738 CE (64 bits)"

What is your build version?

I saw strange difference between your import dialog and my.
I have no decimal separator selection below preview, like in your image, see attached import dialog.
Also in the Columns: section you have:
Source Column   Field Type
While I have:
Source Column   Dest Column

Are you using the standard 8.0.30 ?
What is your build version?
The one that I'm using now is:
"Workbench Version 8.0.30 build 2054668 CE (64 bits)"

Please repeat your test with the Community edition 8.0.30
I can confirm the bug is always repeatable in 8.0.30
In case change the bug status, as "Can't repeat" is wrong

just tested the 8.0.38 CE on Windows, it still truncate CSV files with field containing double values when:

1) semicolon is used as field separator

2) comma is used as decimal separator

happen also using TAB as field separator
and also using or not double quote to enclose fields

importing the file with 4th field value=3,3:
doubleCommaDecimalSeparator.csv
the result is truncated as 3 (comma is not considered valid numeric)

While importing the file with 4th field value=3.3:
doubleCommaDecimalSeparatorDot.csv
the import result in 3.3

So the trouble is that the user cannot select the decimal separator in the import dialog and no WARN is shown for data damage

confirmed on 8.0.41 on Win10

I changed the Severity to Critical as it is a data loss with no WARN