Apache OpenOffice (AOO) Bugzilla – Issue 126052
Calc: Scientific notation imported as text, unless "special numbers" is checked
Last modified: 2017-05-20 09:54:59 UTC
Created attachment 84442 [details] Screenshot from Calc after import Using the Danish version of AOO 4.1.1 I open a csv-file with just the following contents (three lines with one number on each line): 5.49 5.49E0 5.49E-2 Calc asks me what locale should be used for the import, and I choose "English (USA)". However, only the first number (5.49) is imported as a number, while the two numbers in scientific notation (5.49E0 and 5.49E-2) are imported as text. I have found that if I tick "Detect special numbers", all three numbers are imported as numbers. Still, I would consider this a bug. To me, "special numbers" are numbers like NaN (not a number) or inf (infinity), not scientific notation. My AOO is the Danish brand. I have not tried other language versions. The attached snip of a screenshot shows the result after import, with the "Highlight values" (Ctrl+F8) feature turned on. Only 5.49 are in blue and right-formatted as a number (shown as "5,49" because Danish use comma as decimal point). I am aware that this report have similarities with the closed issue 115683 But I am already using the language setting suggested in the responses to that issue. Best regards Lars
I don't reproduce with AOO 4.1.1 french version. "English (USA)" notation convert these values into: 5,49 5,49 0,0549 So maybe specific to your danish localization. Try with US version and come back to confirm this information.
I have tried with US version 4.1.1 (after de-install Danish version, install US version) with no change: Only the first line is recognized as a number, the two numbers in scientific notation are imported as text. Additional finding: If I select the column at import and change the type from "Standard" to "English (US)" then the scientific format IS recognized as numbers. This behaviour is the same for the US and the Danish version of AOO. To sum up: Opening af *.csv file, I find that the scientific format is recognized if I a) select "Detect special numbers" b) or explicitly mark the column as "English (US)" but not by a standard import. My findings are identical for the Danish and the US versions of AOO 4.1.1. I have now also tried changing the Windows number setting to English (US). No change (except that of course the correctely imported number is shown as "5.49" instead of "5,49"). Thus, the issue does not seem to relate to the Windows settings.
Read the help F1 page on Text Import : Detect special numbers When this option is enabled, Calc will automatically detect all number formats, including special number formats such as dates, time, and scientific notation. The selected language influences how such special numbers are detected, since different languages and regions many have different conventions for such special numbers. When this option is disabled, Calc will detect and convert only decimal numbers. The rest, including numbers formatted in scientific notation, will be imported as text. A decimal number string can have digits 0-9, thousands separators, and a decimal separator. Thousands separators and decimal separators may vary with the selected language and region. This is how OpenOffice works. Not a bug.
I see. But if 5.49E-2 is to be considered a special number, how comes that specifically marking the column as "US English" format, will make it import as a number WITHOUT marking "Detect special numbers"? It seems inconsistent. If not, what is the logic behind? Best regards, Lars
Explicitly marking the column as English or date implies that you want values to be interpreted as numbers. Otherwise there is no need to mark them. The individual column settings can be used to override the general options in the upper part of the dialog. You can specify to not interprete "special numbers" except this particular English column and that particular ISO date. Likewise you can interprete all numbers except for the column that are marked as "Text".
Okay. Thanks for the comments and explanations. Based on those, I am closing the issue as NOT_AN_ISSUE. Best regards, Lars