Issue 126052 - Calc: Scientific notation imported as text, unless "special numbers" is checked
Summary: Calc: Scientific notation imported as text, unless "special numbers" is checked
Alias: None
Product: Calc
Classification: Application
Component: open-import (show other issues)
Version: 4.1.1
Hardware: PC Windows 7
: P3 Normal (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Reported: 2015-01-20 13:28 UTC by Lars Jødal
Modified: 2017-05-20 09:54 UTC (History)
3 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

Screenshot from Calc after import (1.82 KB, image/png)
2015-01-20 13:28 UTC, Lars Jødal
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description Lars Jødal 2015-01-20 13:28:42 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):


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
Comment 1 oooforum (fr) 2015-01-20 14:51:32 UTC
I don't reproduce with AOO 4.1.1 french version.
"English (USA)" notation convert these values into:

So maybe specific to your danish localization.
Try with US version and come back to confirm this information.
Comment 2 Lars Jødal 2015-01-21 10:01:47 UTC
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.
Comment 3 bmarcelly 2015-01-23 19:50:37 UTC
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.
Comment 4 Lars Jødal 2015-01-26 10:35:50 UTC
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,
Comment 5 Andreas Säger 2015-01-26 10:47:15 UTC
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".
Comment 6 Lars Jødal 2015-01-26 10:58:51 UTC
Okay. Thanks for the comments and explanations. Based on those, I am closing the issue as NOT_AN_ISSUE.

Best regards,