bugGNU Octave - Bugs: bug #50224, [octave forge] (io) csv2cell()...

 
 

bug #50224: [octave forge] (io) csv2cell() returns error when linefeed(s) are present in text fields

Submitter:  ralf <rapete>
Submitted:  Fri 03 Feb 2017 10:58:51 PM UTC
   
 
Category:  Octave Package Severity:  2 - Minor
Priority:  3 - Low Item Group:  Unexpected Error or Warning
Status:  Wont Fix Assigned to:  None
Originator Name:  rapete Open/Closed:  * Closed
Release:  * other Operating System:  * Any
Fixed Release:  None Planned Release:  None
* Mandatory Fields

Add a New Comment Rich Markup
   

Jump to the original submission

Thu 21 Dec 2023 09:58:09 PM UTC, comment #6: 

This is a fairly old bug report.
AFAICS the underlying issue is low-level routines used by csv2cell will never be able to distinguish an "\n" character (linefeed) embedded in text from a "real" EOL.
There's no way this can be overcome w/o unreasonable efforts, if possible at all, and it'll lead to hard-to-maintain code.

Closing this with "Wont fix".

Philip Nienhuis <philipnienhuis>
Group Member
Sat 04 Feb 2017 08:40:53 PM UTC, comment #5: 

AFAIK csvread() in Matlab and Octave cannot read mixed-type csv files at all; only numeric .csv files.  csv2cell() is a solution for that but with some unfortunate limitations - although I see this multiline one as a corner case.
Again, rather than instruct LO to write .csv files, you can read LO spreadsheet files directly with odsread/xlsread; even using LO itself using the "UNO" interface (if you have Java installed).
Why go the difficult route through a .csv file?


Back to csv2cell():

(I think you know) csv2cell() has an option to prescribe the cell protection character (default '"' - double quote) and separator (default = comma).

But no matter how you turn it, the one character that screws up in text strings is "\n" because that is also interpreted by one of csv2cell's low level routines. To change that would invoke a rewrite of half of csv2cell's code. Quite unlikely at this point.


Maybe you could have a look at textscan(); that is much more flexible than csv2cell. You might need to enter
"delimiter", ",", "whitespace", ""
as input options but even then I'm afraid that EOL chars embedded in fields will screw up.

Philip Nienhuis <philipnienhuis>
Group Member
Sat 04 Feb 2017 03:53:13 PM UTC, comment #4: 

I am not sure, what the standard is on csv-files, that Octave needs to support.

In csv-files,

  • numbers are either all quoted or all unquoted
  • text strings are either all quoted or unquoted
  • number formats may equal the format of IP addresses, which should not be read-in as numbers, but as text strings that preserve the meaningful position of the IP adresses' dots '.'
  • spreadsheet row separators and linefeeds within spreadsheet cells share the same separator "\n"
  • column separators may equal the fractional part separators of numbers


It seems that the original spreadsheet can only then be recovered properly, if the csv-file format was chosen carefully.

One way to preserve original spreadsheet content is to format the csv-file

  • (A) to embrace text in quotes '"'
  • (B) to separate spreadsheet columns by ';'


(A) allows to distinguish between QUOTED text cells and UNQUOTED number cells in situations where it is not clear, whether cell content is numerical or not (e.g. IP addresses vs. numbers)
(A) allows to distinguish between spreadsheet row ends and text-cell-internal linefeeds both encoded by the same newline character "\n"
(B) forces number cells to remain UNQUOTED (as an option in LibreOffice) making sure that the QUOTED text strings can be distinguished from UNQUOTED number strings

csv-files that fail to comply with (A) or (B), I think, cannot fully be recovered by any software read-in, including Octave's csv2cell(), because important information is lost during the encoding to csv.

So, I guess, if csv2cell() is supposed to support the case, where text cells are NOT embraced by quotes, then a fix wont be possible for the lack of information within the chosen csv-format.

In other words, the chosen csv-format determines, whether there is a way to read-in text-cell-internal linefeeds unambiguously / correctly or not.

Chose quotes to surround your text cells in the csv-output and then correct Octave-read-in of text-cell-internal linefeeds becomes feasible!

For this case of quoted, potentially linefeed-containing spreadsheet text cells in the csv-file, I wrote hhcsv2cell(), see attached file hhcsv2cell.m below.

hhcsv2cell() adapts to fractional part separators in numbers, that may vary according to the chosen number format, usually either ',' or '.' as in "123,456" or "123.456".

Code example for csv using column separator ';', quotation '"' and fractional part separator ',' (see attached cde.csv and cde.ods):

[S,St] = hhcsv2cell( 'cde.csv', cSep=';', tSep='"', fSep=',' );


Note, that csv-files may embrace numbers in quotes for the case that the column separator, e.g. ',', was chosen to equal the fractional part separator in numbers. hhcsv2cell() does not convert quote-embraced number strings, e.g. "123,456" to numerical values, but does so for unquoted 123,456. Chose ';' as column separator in LibreOffice's csv output for numerical spreadsheet cells to be formatted as unquoted numerical text strings in the csv-file.

To summarize, in LibreOffice's csv output:

  • good choice: You chose ';' as column separator cSep to have numerical spreadsheet cells be output as UNQUOTED numbers in the csv-file, which ARE converted to numbers by hhcsv2cell().
  • bad choice:  You chose ',' as column separator cSep to have LibreOffice QUOTE all numbers in the csv-file, which then NONE ARE converted to numbers by [S,St]=hhcsv2cell() making its outputs S and St identical.
  • good choice: You chose '"' as text separator tSep so hhcsv2cell() is able to identify numerical spreadsheet cells and convert them to numbers in S (with St encoding all cells as character strings including numerical cells)


If your choice of fSep fails to match the fractional part separators of numbers in the csv-file, then number conversion may fail placing nan in S whereever it applies.
 



(file #39662, file #39663, file #39664)

ralf <rapete>
Sat 04 Feb 2017 11:20:39 AM UTC, comment #3: 

Oops, I meant bug #50200

Philip Nienhuis <philipnienhuis>
Group Member
Sat 04 Feb 2017 11:18:53 AM UTC, comment #2: 

BTW, this bug references bug #50199

Philip Nienhuis <philipnienhuis>
Group Member
Sat 04 Feb 2017 11:17:30 AM UTC, comment #1: 

I'm afraid this isn't gonna be solved soon :-(

csv2cell() invokes fgetl() to scan the file for nr. of lines, and fgetl() doesn't care where it finds a newline, it just assumes it to be a "real" EOL whether it's in a protected (by double quotes) cell or not.
In general it is very hard for lower level code to assess whether a newline means EOL in the file context or is just part of some user text string. Tying to solve that leads without exception to very messy and hard-to-maintain code.

All I'll do for the moment is adding a note in the texinfo help text that the (AFAIK) only non-printable character that cannot be in text cells is "\n".
I am sorry for this probably unsatisfactory solution :-)

A workaround is to turn the .csv into a spreadsheet file and read that using xls/odsread and friends.
Admittedly you'd need to either wait until the next io release, or download the io dev code from the repo yourself and turn it into a package that you can install.

Setting status to "Postponed" and lowering priority.

Philip Nienhuis <philipnienhuis>
Group Member
Fri 03 Feb 2017 10:58:51 PM UTC, original submission:  

If there is one or more linefeeds within spreadsheet cells, then csv2cell() will fail with error message


error: csv2cell: incorrect CSV file, line 2 too short


Code example for use with the attached file:


a = csv2cell('abc.csv', ';', '"');



Operating System is Ubuntu 16.04.
Octave version is 4.0.0.
Version of io package is io-2.4.5.

ralf <rapete>

 

(Note: upload size limit is set to 16384 kB, after insertion of the required escape characters.)

Attach Files:
   
   
Comment:
   

Attached Files
file #39662:  hhcsv2cell.m added by rapete (8KiB - text/x-objcsrc)
file #39663:  cde.csv added by rapete (91B - text/csv)
file #39664:  cde.ods added by rapete (9KiB - application/vnd.oasis.opendocument.spreadsheet)
file #39658:  abc.csv added by rapete (28B - text/csv)

 

Depends on the following items: None found

Items that depend on this one: None found

 

Carbon-Copy List
  • -email is unavailable- added by jwe (Updated the item)
  • -email is unavailable- added by rapete (Submitted the item)
  •  

    There are 0 votes so far. Votes easily highlight which items people would like to see resolved in priority, independently of the priority of the item set by tracker managers.

    Only group members can vote.

     

    Follow 13 latest changes.

    Date Changed by Updated Field Previous Value => Replaced by
    2023-12-21 philipnienhuis StatusPostponed Wont Fix
        Open/ClosedOpen Closed
    2017-08-12 jwe Summarycsv2cell() returns error when linefeed(s) are present in text fields [octave forge] (io) csv2cell() returns error when linefeed(s) are present in text fields
    2017-02-07 philipnienhuis Priority5 - Normal 3 - Low
    2017-02-04 rapete Attached File- Added hhcsv2cell.m, #39662
        Attached File- Added cde.csv, #39663
        Attached File- Added cde.ods, #39664
    2017-02-04 philipnienhuis Severity3 - Normal 2 - Minor
        StatusNone Postponed
        Release4.0.0 other
        Operating SystemGNU/Linux Any
        Summarycsv2cell() returns error when linefeed(s) are present in spreadsheet cells csv2cell() returns error when linefeed(s) are present in text fields
    2017-02-03 rapete Attached File- Added abc.csv, #39658

    Back to the top

    Powered by Savane 3.13-758e.
    Corresponding source code