May be duplicate of #39829 or not. I'll try to file something reproducible. Open the following plain text file on a Windows(64) box: 1.234,98 123,98 -123,98 -1.234,98 Language: German(Germany) Encoding: Latin-1(Windows) or some other Separators: any or none Special Numbers: ON (this makes a difference) The first and the last value with thousands separator import as text. I can not reproduce this on my Linux system (Ubuntu 12.4)
Cannot reproduce with LO 4.3.5.2 (French UI and locale) with the same settings (German and special numbers on). Cannot reproduce also with 5.0.0 alpha (English UI and French locale). May be related to Date acceptance pattern (as Special numbers is on). With FR locale D.M.Y is not a valid date. (No dot separator for dates) If German accept dot in date, it is surely the cause. Setting back to unconfirmed waiting for German date acceptance.
OK. Then LibreOffice imports wrong numbers because date acceptance patterns interfer with locale settings in text import options. However, I can delete the date acceptance patterns and I can use English patterns. No matter what I do, LO 4.4.3 fails to import French/German numbers with thousands separators. I can confirm that the problem occurs with French(France) locale too, interestingly under Windows and Linux. Also interestingly, the "special numbers" option makes no difference when used in French context. Importing 1.234,98 from text file with German base locale and English date acceptance pattern [D/;D/M;D/M/;D/M/Y]: OS + Import Lang + Special_Number --> Result ---------------------------------------------------- Windows + French + SpeciaNum=1 --> Text Windows + French + SpeciaNum=0 --> Text Windows + German + SpeciaNum=0 --> Number Windows + German + SpeciaNum=1 --> Text Linux + French + SpeciaNum=0 --> Text Linux + French + SpeciaNum=1 --> Text Linux + German + SpeciaNum=0 --> Number Linux + German + SpeciaNum=1 --> Number Hundreds of support requests confirm that the whole text import is incomprehensible and I think it is broken as well. Revert to the clear and comprehensible behaviour of OpenOffice.org 2 but eliminate any acceptance of D-M dates. Nobody is ever confronted with any csv files where "1-2" or "1/2" is meant to be a date value. "1-2" or "1/2" is only meant to be a date value when the string comes from a keyboard.
Sorry, the above test matrix is not exactly correct. In fact I can not reproduce the exact same findings as yesterday. Windows + German + SpeciaNum=1 --> Number therefore the results are the same in Linux and Windows but not in French(France) and German(Germany). The date acceptance pattern makes no difference. Meanwhile I tested the same with D.;D.M;D.M.;D.M.Y and get the same results as with D/;D/M;D/M/;D/M/Y
Created attachment 116852 [details] Import dialog for German data import
Created attachment 116853 [details] Results of text import.
The same happens with portuguese numbers on Libreoffice 5 Calc on MAC The sttings are - Locale setting: Portuguese (Portugal) - Decimal separator key: Tried both options 1234,0 translates to number 1 234,0 translates to number 1.234,0 translates to text
What irritates me most is the NEEDINFO flag for this obvious bug. What irritates me second most is the fact that the same version of LO4.4.6 with the same locale and language options loading the same input file gives different results. LO4.4.6 (non-PPA) on Linux with German (de-DE) locale 1.234,99 is a number, on Windows it is text. LO on Windows marks the imported value as a text (leading apostrophe tag in front of the numeric text) which means that the text is recognized as a number but tagged as a text even though I specified all options to evaluate the text as a number. @Antonio, comment #6: For Portuguese language the non-breaking space (Unicode 0x00A0) is defined as thousands separator. This macro dumps all locale settings into a spreadsheet and applies the locale's default format to each row so you can test the locale in its respective row. Sub printAllLocalesToNewSpreadSheet() Dim oDoc, oSheet, i18n, oInfo, a(), b(),i%, oItem, nf& oDoc = StarDesktop.loadComponentFromURL("private:factory/scalc","_default",0,Array()) oSheet = oDoc.getSheets().getByIndex(0) i18n = createUnoService("com.sun.star.i18n.LocaleData") a() = i18n.getAllInstalledLocaleNames() dim r(uBound(a()) +1) Const cCols = 7 r(0) = Array("Locale","Language","Country","Decimal","Date","Time","1000","List") For i = 0 to uBound(a()) oInfo = i18n.getLanguageCountryInfo(a(i)) oItem = i18n.getLocaleItem(a(i)) b() = Array( _ getLocaleString(a(i)), _ oInfo.LanguageDefaultName, _ oInfo.CountryDefaultName, _ oItem.decimalSeparator, _ oItem.dateSeparator, _ oItem.timeSeparator, _ oItem.thousandSeparator, _ oItem.listSeparator _ ) r(i +1) = b() nf = oDoc.NumberFormats.getStandardIndex(a(i)) oSheet.getCellRangeByPosition(0, i+1, 255, i+1).NumberFormat = nf next oSheet.getCellRangeByPosition(0, 0, cCols, uBound(r())).setDataArray(r()) End Sub
Seems to be fixed in latest "still" 4.4.7.2
If it doesn't occur anymore then let's resolve the bug.