GNOME Bugzilla – Bug 571129
CSV text field with leading minus (-) doesn't display when imported
Last modified: 2009-02-19 04:13:04 UTC
Please describe the problem: Stock data exported from the Fidelity brokerage website designates stock option symbols with a leading minus sign "-". ie "-IBMXX". This data, when exported in CSV format, and imported into Gnumeric does not display correctly, or at all. There doesn't seem to be a way to force it to format as plain text. Here is a truncated version of an export file: Trade Date,Action,Symbol,Quantity,Price ($),Commission ($),Amount 10/15/2008, SELL, -FCXJG,-2.000,2.35, 12.45,457.54 10/15/2008, SELL, MSFT,-2000,2.35, 12.45,457.54 10/13/2008, BUY, -BTUKI,1.000,1.70, 10.95,-180.95 Steps to reproduce: 1. Create a file from the above snippet. 2. Import it into Gnumeric. or... 3. Name the file ~.csv and open it with Gnumeric Actual results: The fields display correctly except for the ones starting with "-". Those are invisible, or substituted with #NAME?. I don't see an obvious way to coerce it into text format. Expected results: I would hope to see the symbols as they appear in the csv file: "-FCXJG". Or I would expect to find a way to define the field, either during or after import, so that it displays as plain text. Does this happen every time? Yes. Other information:
This is going to be painful. The underlying problem is that there is no formal specification of what a csv file means. Get two people in a room and there will be two opinions. Or three. What you are hitting here is that some people like to have expressions in csv files. "=DATE(2009,2,10)" for example. Since there are commas in there, the quotes are mandatory. It is the leading "=" (or "-" or "+") that signifies a formula. "-FCXJG", for example, is considered a formula with an undefined name. That formula evaluates to #NAME? until and unless someone defines the name. I see no good solution beyond "don't use csv!". I could disallow undefined names in csv files without hurting anyone, I think. That would handle the case you show here, but not more tricky cases like "-TRUE". Suggestions most welcome.
I forgot to mention that the configurable text importer in the gui can handle the file just fine, but you will have to select "Text" as format for that column.
Are there really ever formulae in csv files that do not start with "="?
> Are there really ever formulae in csv files that do not start with "="? Excellent question -- let me grab the spec and have a look. Oh, right. We could certainly crudely declare that we refuse to accept such formulae as anything but text. Let's try that. This problem has been fixed in the development version. The fix will be available in the next major software release. Thank you for your bug report.
Sorry to wait so long to check in , since I entered the bug. What would fix it for me is to be able to select the problem cells, define them as text and see them as text. CSV files are often a mess, but if I can fix it after it's loaded, no problem. I tried the configurable text importer, but didn't see a way to define things on a per-column basis. So, I defined all columns as text, but no noticeable result.
On the last page of the importer you can click on the column and then select "text" in the formatting widget in the middle of the page. THe word "General" above the column should then change to "Text". When you click on finish then, the expressions such as -FCXJG are being imported as text.
The "Text" thing used to work, but it isn't working right for me now. At least I cannot get the numbers to import as text. Reopening for a closer look.
I had previously checked for the "-FCXJG" and just checked with respect to numbers and it works as it is intended (ie. numbers import as text if the column is set to text). This is svn as of February 7th.
Ok, I'm blind. It does indeed work. (I was expecting to see a single quote for numbers as but that does not happen in "text" cells.)
Yay, it works! I didn't see how to select a single column at first. Also, these fields ie.-FCXJG can be converted to text even when loaded as "general" by 1)formatting the cells as text and 2)editing them in the formula bar to remove the '='. It is odd that when imported from a CSV they appear invisible instead of as #NAME?, but that's a minor quirk.