Decimal numbers in CSV file loaded incorrectly

[joeDWG]

I am loading the following csv file into Calc:

id,col1,col2,col3,col4
504,0.0,0.0,0.0,1.908600330352783e-05
504,0.0,0.0,0.0,1.908600330352783e-05
504,0.0,0.0,0.0,3.7109851837158204e-05
504,0.0,0.001,0.001,0.000670088529586792
504,0.0,0.002,0.002,0.0014133477210998536
504,0.01,0.004,0.004,0.003691591024398804
504,0.01,0.006,0.006,0.005206912159919739
504,0.01,0.01,0.01,0.007649389505386353
504,0.01,0.014,0.01,0.010154022574424744

When loading it as a German UTF-8 file, the preview shows the decimal numbers in col2 and col3 correctly:

But when I click OK, some of the decimal numbers get right-truncated and become integers, e.g. 0.014 becomes 14:

I have no idea why that happens… does anyone know what’s going on there?

Calc: Version: 6.0.7.3
Build ID: 1:6.0.7-0ubuntu0.18.04.10

[mikekaganski]

This happens because in your locale, the decimal separator is likely comma, while dot is thousand separator. In this case, the shown text is tried to be interpreted as numbers; for the cases when there’s 3 digits after “thousand separator”, the text is happily converted to numbers - which would be (in case of 14) “zero thousand zero hundred 14”. In other cases, when the number of digits after dot is not 3, it is not a valid “number with thousands separator”, and is imported as text.

You need to set your import settings to en-US, either on the top of the dialog, or individually in columns.

[anon73440385]

Hello,

the german decimal delimiter is , while your data are decimal delimited by . and - as per your screenshot - the import language is set to German (Germany). If you’d set to English (USA) at time of import, you’ll get it correct as decimal numbers (and delimiter changed to ,). Without that you get all .-delimted numbers as text (watch their left alignment)

Hope that helps.

If the answer helped to solve your problem, please click the check mark () next to the answer.