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

Submitted by:  ralf <rapete>
Submitted on:  Fri 03 Feb 2017 10:58:51 PM UTC  
 
Category: Octave Forge PackageSeverity: 2 - Minor
Priority: 3 - LowItem Group: Unexpected Error
Status: PostponedAssigned to: None
Originator Name: rapeteOpen/Closed: Open
Release: otherOperating System: Any

Add a New Comment(Rich Markup)
   

You are not logged in

Please log in, so followups can be emailed to you.

 

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>
Project 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):

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>
Project Member
Sat 04 Feb 2017 11:18:53 AM UTC, comment #2:

BTW, this bug references bug #50199

Philip Nienhuis <philipnienhuis>
Project 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>
Project 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

Code example for use with the attached file:

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 File(s):
   
   
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
  • -unavailable- added by jwe (Updated the item)
  • -unavailable- added by rapete (Submitted the item)
  •  

    Do you think this task is very important?
    If so, you can click here to add your encouragement to it.
    This task has 0 encouragements so far.

    Only project members can vote.

     

    Please enter the title of George Orwell's famous dystopian book (it's a date):

     

     

    Follow 11 latest changes.

    Date Changed By Updated Field Previous Value => Replaced By
    Sat 12 Aug 2017 07:13:50 PM UTCjweSummarycsv2cell() returns error when linefeed(s) are present in text fields=>[octave forge] (io) csv2cell() returns error when linefeed(s) are present in text fields
    Tue 07 Feb 2017 10:03:39 PM UTCphilipnienhuisPriority5 - Normal=>3 - Low
    Sat 04 Feb 2017 03:53:13 PM UTCrapeteAttached File-=>Added hhcsv2cell.m, #39662
      Attached File-=>Added cde.csv, #39663
      Attached File-=>Added cde.ods, #39664
    Sat 04 Feb 2017 11:17:30 AM UTCphilipnienhuisSeverity3 - 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
    Fri 03 Feb 2017 10:58:51 PM UTCrapeteAttached File-=>Added abc.csv, #39658

    Back to the top


    Powered by Savane 3.1-cleanup1