Bug 39829 - L10n decimal and thousands separator selection for CSV (or other delimiter) text being imported to Calc
Summary: L10n decimal and thousands separator selection for CSV (or other delimiter) t...
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
3.4.2 release
Hardware: Other All
: medium enhancement
Assignee: Not Assigned
: 39830 114459 (view as bug list)
Depends on:
Blocks: CSV-Dialog
  Show dependency treegraph
Reported: 2011-08-04 07:49 UTC by Nicolas Mailhot
Modified: 2018-06-21 08:53 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Note You need to log in before you can comment on or make changes to this bug.
Description Nicolas Mailhot 2011-08-04 07:49:09 UTC
France uses the period as decimal separator. However far too many programs output numbers using US dot convention.

It is currently not possible to specify the decimal separator to use when opening a csv file.

The workaround so far has been to open the csv file by pretending it is a en_US file. However, with  libreoffice 3.4.2 (fr), while the numbers show up properly in file preview (0.992 is displayed as 0,992), they are broken on file open (0.992 is interpreted now as 992)

Needless to say that wreaks havoc, since numbers are now off by several orders of magnitude
Comment 1 m_a_riosv 2011-08-04 15:52:48 UTC

*** This bug has been marked as a duplicate of bug 39830 ***
Comment 2 Nicolas Mailhot 2011-08-05 10:30:27 UTC
This is not a duplicate of  bug #39830 

Bug #39830 requests a discoverable way to select the decimal separator

This bug points out the fact, that even though the correct decimal separator was selected via UI hacks, the numbers that displayed correctly in the csv preview are wrong when the file is finaly opened (mismatch between preview and actual file open)
Comment 3 Eike Rathke 2012-04-03 09:26:51 UTC
Could you please elaborate what exactly you mean by "correct decimal
separator was selected via UI hacks"?

Reading a CSV file that contains 0.992 in a LibO that runs with the
French (France) locale works if in the import dialog either
a) Language is set to English (USA), or
b) column type is set to US English

Both methods for me yield the numeric value 0,992
Also, the preview always displays the raw 0.992 value, not 0,992
This in LibO 3.5.0 and 3.4.5
Comment 4 Nicolas Mailhot 2012-04-03 12:21:20 UTC
(In reply to comment #3)
> Could you please elaborate what exactly you mean by "correct decimal
> separator was selected via UI hacks"?

The methods you describe
Pretending a document is in English when it is not just to get numbers right is an hack only technical persons understand.
Comment 5 Joel Madero 2014-11-06 20:33:46 UTC
Never confirmed by QA - moving to UNCONFIRMED.
Comment 6 V Stuart Foote 2014-11-16 23:14:55 UTC
As in comment 3, functional workarounds already exist for handling decimal marker of CSV (or other separators) during multiple language text import to Calc. 

However, it would be a reasonable L10n enhancement of the Calc "Text Import" dialog to add support to specify the resulting number formats on import--decimal separator and thousands separator--and display sample in the Preview area.

Enhancement would be to provide additional control of the imported number format independent of the User Interface Language setting (Tools -> Options -> Language Settings -> Language) or the Language setting picked for the source CSV text file.

From UX perspective could either specify the resulting Language setting, or possibly could define output field for decimal separator and thousands separators. Or maybe both.

Closing 39830 as duplicate of this enhancement request.
Comment 7 V Stuart Foote 2014-11-16 23:21:07 UTC
*** Bug 39830 has been marked as a duplicate of this bug. ***
Comment 8 Robinson Tryon (qubit) 2016-08-25 05:39:19 UTC Comment hidden (obsolete)
Comment 9 Buovjaga 2017-12-26 11:44:54 UTC
*** Bug 114459 has been marked as a duplicate of this bug. ***
Comment 10 Heiko Tietze 2018-06-17 10:31:55 UTC
Think we are talking about 1,2;1,3;1,4 vs. 1.1,1.2,1.3 and both should be read as numerical values independently from the locale. I'm running en_US and 1,2 is taken as a string. So what we could do UI wise is to enhance the Detect special number function and also convert comma-delimited strings to numbers.
Comment 11 Heiko Tietze 2018-06-21 08:53:14 UTC
We discussed the topic in the design session.

While Excel provides an "Advanced..." dialog where the user can define the delimiters, the supposed workflow in LibreOffice is to select it via Language. That means in case of 1,2;1,3 it should be "German", and that reads the values as numbers 1.1, 1.2... even when locale setting is US English. 

So the request is a WF.

But we should update the preview to make it clear what language means.
Bug 118298 - Update preview in CSV import dialog when the language is changed