Re: Imported text is converted to date

    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

Attachment: CSV-demo.gnumeric
Description: application/gnumeric

Attachment: CSV-demo.csv
Description: Text document

[Date Prev][Date Next]   [Thread Prev][Thread Next]   [Thread Index] [Date Index] [Author Index]