Apache OpenOffice (AOO) Bugzilla – Issue 82687
CSV import treats $.nn as text
Last modified: 2017-05-20 11:01:03 UTC
CSV import allows numbers to start with a currency symbol ($) or with zero digits before the decimal point, but not both. For example, $.12 would be imported as text, not 12 cents. This is a problem because a) It potentially impacts quite a few people: my bank exports account activity in this format. b) Excel imports it correctly. Small test file: normal,9.84 dollar,$9.84 less than 1,.84 only cents,$.84 I would expect all 4 entries in the second column to be imported as numbers, but OOo 2.2.1 only imports the first 3 entries as numbers.
Hi Eike, please have a look at this one. Frank
Indeed.. same with manual input. Accepted.
Still a bug in 4.2.0-dev. With "Detect special numbers" unchecked it is even worse - it can't deal with currency symbols at all.
What CSV formats allow is not the same aa what happens when a field is marked as currency and the input editor or the cell formatting attaches additional symbols and text when the data is entered manually into a cell. The CSV is not meant to be an input format as much as a means of saving and restoring. It just doesn't carry formatting. We need to be careful and not get these all snarled up. For example, if you look in the ODS, you'll see that numerics are just numerics even though the presentation format may have different text. CSV simply doesn't carry all of that. It is from a simpler time. I think it is best to look at what OpenOffice *saves* into CSV for different kinds of Numeric cells and Text cells and ensure those values are restored on input. If people want the formatted interpretation to be carried, that is what the ODS (and counterparts in Microsoft Office) are for. Also keep in mind that anything more exotic needs to be documented in an understandable way, and then we need to figure out how that all works in interchange between different "CSV-supporting" products. I am surprised that the extraction of the numerical part didn't happen though. Was the option to treat quoted material as text checked?
(In reply to ooo from comment #2) > Indeed.. same with manual input. Accepted. There is an interesting problem here, considering how old this is. It would be useful to know how currency symbols get into CSV, if produced by anyone, and what the interoperabilty is with Excel and LibreOffice.
(In reply to cheath from comment #0) > CSV import allows numbers to start with a currency symbol ($) or with zero > digits before the decimal point, but not both. For example, $.12 would be > imported as text, not 12 cents. [ ... ] > normal,9.84 > dollar,$9.84 > less than 1,.84 > only cents,$.84 > > I would expect all 4 entries in the second column to be imported as numbers, > but > OOo 2.2.1 only imports the first 3 entries as numbers. This suggests there has been a regression. It was pointed out that manual cell entry had a similar problem. I just manually entered the above into two columns (not via CSV) and the described behavior still occurs. $0.84 works. For *INPUT ENTRY* I agree, the $.84 should work. It apparently needs to make it into $0.84 though :). I left the $.84 as text and used the "Save Content as Shown" as well as quoting of text in making a CSV and I got, where the last column shows the values when I opened it in Excel 2016, "normal",9.84 9.84 "dollar",$9.84 $9.84 "less than 1",0.84 0.84 "only cents","$.84" $0.84 "alternative",$0.84 $0.84 with the additional space after the $ ones. The cell values (in the entry field) are without the $. On saving back out from Excel, there are no quotes and the "only cents" value is $0.84 with no quotes. LibreOffice 5.0.0 behaves the same as Apache OpenOffice 4.1.2 on input of the CSV. However, on entry of $.84 for cell input in the Calc UI, LibreOffice presents the value as $0.84 and as numeric. That's what Excel 2016 does too. I suspect one can make some form of remedy, although I wouldn't go so far as to have "$.84" with quotes be treated as anything but text. The Excel hack is odd in this case.
Reset assigne to the default "issues@openoffice.apache.org".