Issue 82687 - CSV import treats $.nn as text
Summary: CSV import treats $.nn as text
Status: CONFIRMED
Alias: None
Product: Calc
Classification: Application
Component: open-import (show other issues)
Version: OOo 2.2.1
Hardware: All All
: P4 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2007-10-17 06:13 UTC by cheath
Modified: 2017-05-20 11:01 UTC (History)
3 users (show)

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


Attachments

Note You need to log in before you can comment on or make changes to this issue.
Description cheath 2007-10-17 06:13:18 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.
Comment 1 frank 2007-12-12 15:06:43 UTC
Hi Eike,

please have a look at this one.

Frank
Comment 2 ooo 2007-12-13 12:32:50 UTC
Indeed.. same with manual input. Accepted.
Comment 3 damjan 2016-04-03 05:32:24 UTC
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.
Comment 4 orcmid 2016-04-03 15:00:44 UTC
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?
Comment 5 orcmid 2016-04-03 15:02:46 UTC
(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.
Comment 6 orcmid 2016-04-03 15:36:12 UTC
(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.
Comment 7 Marcus 2017-05-20 11:01:03 UTC
Reset assigne to the default "issues@openoffice.apache.org".