Sorry for the long delay, but this was not easy.
I think I finally have this figured out, so I can import text data
correctly and _automatically_ from .csv files. This is a great
convenience for exchanging data with other programs, since manual import
procedures are time-consuming and error-prone. I haven't been able to
get any other spreadsheet to do this.
The rules are absurdly simple. The key is that for some text cells it is necessary to use both quotation marks and an initial apostrophe. Quotation marks apparently delimit a cell, but contrary to what you might think, they do _not_ indicate a text field.
Here are the rules as I understand them.
1. Use quotes "" as a cell delimiter and initial apostrophe ' as text
indicator.
2. Use BOTH as needed to indicate text. Use quotes for numbers
formatted with internal commas.
3. ALWAYS use double quotes to indicate empty cells, or the results may
be highly unpredictable.
4. Just open the csv file just like any other spreadsheet file, and it
should import correctly into Gnumeric.
There are examples in the attached .csv and gnumeric files have examples. I've been using the first two rules for some time now with no problems. The examples have been tested with versions 1.10.17 for Linux and 1.10.9 for Windows.
Regardless of any ambiguities in the csv specification, I think there are still some bugs, however. If you export the the attached .gnumeric file to a csv file, Gnumeric will not retain the required apostrophe text indicator. Consequently, it does not correctly import its own (csv) file, and will interpret text as numbers. The same problem also applies to export of a (configurable) text file. Also, it sometimes badly misparses a line if commas are used to indicate an empty cell.
Unfortunately, the attached csv file will not generally import correctly into other spreadsheets. I can understand it if no one wants to support csv files.
Rex Couture
CSV-demo.gnumeric
Description: application/gnumeric
"", "Suggestions for constructing comma-separated value (.CSV) files for input to Gnumeric" "", "(Results may be highly unpredictable if these rules are not followed.)" "Rules" "", "Use quotes "" as a cell delimiter and initial apostrophe ' as text indicator." "", "Use BOTH as needed to indicate text. Use quotes for numbers formatted with a comma." "", "ALWAYS use double quotes for empty cells, or the results may be highly unpredictable." "Notes" "", """ does not indicate text!" "", "An apostrophe ' is strongly recommended for text, but is not required if the cell entry could only be text." "Examples" "Number, number:", 5678, 6789 "Number, text, text:", "1,234", "'1,234", "'1 234" "Number (date), text:", 10.24.2, '10.24.2 "Text, text:", '10.24.2, "10.24.2, 2" "", "(Note selective omission of quotes """" and apostrophe ' in .csv file)"
_______________________________________________ gnumeric-list mailing list gnumeric-list@gnome.org https://mail.gnome.org/mailman/listinfo/gnumeric-list
