bugGNU Octave - Bugs: bug #44511, [octave forge] (io) xlsread cannot...

 
 

bug #44511: [octave forge] (io) xlsread cannot read a csv file

Submitter:  Avinoam Kalma <avinoam>
Submitted:  Wed 11 Mar 2015 06:01:11 PM UTC
   
 
Category:  Octave Package Severity:  1 - Wish
Priority:  3 - Low Item Group:  Matlab Compatibility
Status:  Wont Fix Assigned to:  philipnienhuis
Originator Name:  Avinoam 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

Sun 06 Dec 2020 05:26:49 PM UTC, comment #16: 

(revisiting)

With the following file:

a,b,c,d,e,f
11,12,13,"14",15,16
,22
31,32,"33",34
41,"42",,44,45
51,52,53


Matlab r2020b returns:

>> xlsread ('asc.csv')
ans =
    11    12    13    14    15    16
   NaN    22   NaN   NaN   NaN   NaN
    31    32    33    34   NaN   NaN
    41    42   NaN    44    45   NaN
    51    52    53   NaN   NaN   NaN
>>


which I don't think you want to get. The values between double quotes are supposed to be strings. csv2cell does that right.
Moreover,

>> [a, b, c] = xlsread ('asc.csv')
a =
    11    12    13    14    15    16
   NaN    22   NaN   NaN   NaN   NaN
    31    32    33    34   NaN   NaN
    41    42   NaN    44    45   NaN
    51    52    53   NaN   NaN   NaN
b =
  1×6 cell array
    {'a'}    {'b'}    {'c'}    {'d'}    {'e'}    {'f'}
c =
  6×6 cell array
    {'a'  }    {'b' }    {'c'  }    {'d'  }    {'e'  }    {'f'  }
    {[ 11]}    {[12]}    {[ 13]}    {[ 14]}    {[ 15]}    {[ 16]}
    {[NaN]}    {[22]}    {[NaN]}    {[NaN]}    {[NaN]}    {[NaN]}
    {[ 31]}    {[32]}    {[ 33]}    {[ 34]}    {[NaN]}    {[NaN]}
    {[ 41]}    {[42]}    {[NaN]}    {[ 44]}    {[ 45]}    {[NaN]}
    {[ 51]}    {[52]}    {[ 53]}    {[NaN]}    {[NaN]}    {[NaN]}


which IMO is an unusable mess. Matlab doen't separate the text and numeric fields, it simply copies all over and you may sort out yourself if the original field was numeric or text.

Also, Matlab reads a file like:

,,,
1,2,3,
1,2,3,4


as

>> xlsread ('test.csv')
ans =
     1     2     3   NaN
     1     2     3     4


so implicitly throws away the header lines (hmm, maybe acceptable) and the line with empty fields (which I would like to know about explicitly).

All in all more reasons for me to not try to implement reading .csv, as making this behavior Matlab-compatible is going to be a nightmare.

Closing report as "won't fix".

Philip Nienhuis <philipnienhuis>
Group Member
Fri 11 Oct 2019 10:58:01 AM UTC, comment #15: 

Severity ==> "wish"

After a next io release I want to overhaul the scripts to get rid of duplicate code. I.e., for .xls(x) and .ods there are two largely equal sets of m-functions that can be folded into one set that does the work and another set just comprising deprecated wrappers.
In the course of this I'll see if somehow csv2cell can be invoked.

In the mean time the idea of moving xlsread and friends to core Octave seems to have been largely abandoned.

Philip Nienhuis <philipnienhuis>
Group Member
Sun 03 Sep 2017 01:14:12 PM UTC, comment #14: 

convey the separator ... (from xlsread.m)

Philip Nienhuis <philipnienhuis>
Group Member
Sun 03 Sep 2017 01:13:29 PM UTC, comment #13: 

In the mean time I have adapted csv2cell() to accept a spreadsheet-style range argument.
But the issue of how to convey the separator (and for that matter, the cell protection character, default '"' ) to csv2cell() remains.

Philip Nienhuis <philipnienhuis>
Group Member
Fri 27 Mar 2015 09:28:07 PM UTC, comment #12: 

'the separator might be a ";"' => ' the decimal separator might be a ";"'

Philip Nienhuis <philipnienhuis>
Group Member
Fri 27 Mar 2015 09:26:15 PM UTC, comment #11: 

OK I understand.
There are a few issues with what you want:

1. It isn't guaranteed to work unless Excel itself (COM interface) or Libre/OpenOffice (UNO interface) is present. Those interfaces are the only ones that support .csv I/O.

Excel/COM
-----------
xlsread.m is soon going to core Octave; I'm sure no core Octave developer wants to have links to MS-Excel in core Octave.
Excel/COM is out of the question.

Libre/OpenOffice
-----------------
Support for LO/OOo in the io package is a bit wonky due to the arcane way the UNO-Java bridge is set up on the LO/OOo side. I doubt if the UNO interface can make it to core Octave.

Maybe there can be some remnant interfaces in the future io package that can overload (shadow) core spreadsheet functions. But the very discussion about how the spreadsheet io is to be transferred to core Octave hasn't started yet - our priority is a new 4.0.0 release.

  • Alternatives

Of course we can invoke parsecell.m (io pkg) after the call to csvread.m to separate numerics and strings (just like it is done for the regular spreadsheet functions). But there's no point in that as neither csvread nor dlmread can read mixed-type delimited text files whatsoever.

In the io package there's csv2cell that can (and returns a cell array that can be parsed by parsecell.m). But, it can only read comma-delimited files when invoked through xlsread as there's no way to convey the delimiter through xlsread.
I also fear that csv2cell will break on csv files made with Excel/<whatever> on systems with e.g., a Dutch, German or Swiss locale - then the separator might be a ";" rather than a comma.
In addition there's bug #44493 for Octave compiled for 64-bit systems.

Again, I'd like top repeat what I stated in bug #40993:
Use the right tools. IMO reading a csv file with Excel / xlsread is pure overkill.

The only option is to invoke csv2cell() but first it needs to be tested if that works properly and reliably on systems with locales that have a "," as decimal separator.

I'll reopen the bug report and set Status to "Need info", "Priority" to "Low".

Philip Nienhuis <philipnienhuis>
Group Member
Thu 26 Mar 2015 07:22:50 PM UTC, comment #10: 


csvread interface is A=csvread (file),
While xlsread is [A,B,C] = xlsread(file), so if the file is

x,y,z
1,2,3
4,5,6
7,8,9

So csvread will store in A

  1. 0 0

1 2 3
4 5 6
7 8 9

While xlsread will separate the input.

About 2: it seems right.

Avinoam Kalma <avinoam>
Group Member
Wed 25 Mar 2015 07:34:47 PM UTC, comment #9: 

A few questions:

1. What exactly do you mean with 'better interface' ?

2. Comment #8: actually it should print "none\n" or s/th like that.


Philip Nienhuis <philipnienhuis>
Group Member
Mon 16 Mar 2015 09:15:17 PM UTC, comment #8: 

I prefer the previous version, because xlsread has better
interface than csvread, so the routine should first
try to  find if there is XLS interface. If no, then
it check if this is a csv file and read it. If there is
a XLS interface it uses it, even if it is csv.

Please review the attached code, which uses the changes you
have made in the last version, and put them in the previous version.



(file #33371)

Avinoam Kalma <avinoam>
Group Member
Sun 15 Mar 2015 10:20:43 PM UTC, comment #7: 


Thanks.

I am using this bug report for a small typo:

>> xlsread ('a.csv')
Detected XLS interfaces: >>


When here is no XLS interface, (windows package is not installed),
there is a missing \n.


Avinoam Kalma <avinoam>
Group Member
Sat 14 Mar 2015 11:59:04 PM UTC, comment #6: 

Of course, but not right away.
If final Octave-4.0.0 comes (maybe a month or so? there have been many new bugs reported) it may be a good time.
A.o., I'm looking into csv2cell() issues on 64-bit (bug #44493).

For a new xlsread.m, just get it from the io repo. I can't get in there through http however (following the link from octave.sf.net | code), so I've attached it to this report just to be sure you can try it.

BTW my stance on xlsread and csv files is in bug #40993



(file #33346)

Philip Nienhuis <philipnienhuis>
Group Member
Sat 14 Mar 2015 08:09:41 PM UTC, comment #5: 

Thanks.
Will you release a new verion of io package?

Avinoam Kalma <avinoam>
Group Member
Fri 13 Mar 2015 10:01:15 PM UTC, comment #4: 
Philip Nienhuis <philipnienhuis>
Group Member
Fri 13 Mar 2015 07:57:36 AM UTC, comment #3: 

Hmmm.... I see that there IS a .csv check in xlsread - I must have put it in years ago an forgot all about it :-)

OK I'll have a look.
The issue is that xlsopen() gets a first chance to run before csvread is even in the picture.
When there's Excel around, the usual ridiculous overhead of invoking that will work properly that way and Excel reads the .csv, exactly like Matlab does (or did, maybe they improved it like intended in our xlsread).
But I never tested w/o Excel. In such a case xlsopen.m doesn't know what to do with .csv and bails out somewhere.

Easy fix at first sight.

=> reopening / in progress

(Thanks for your gentle persistence Avinoam :-) )

Philip Nienhuis <philipnienhuis>
Group Member
Thu 12 Mar 2015 05:11:38 AM UTC, comment #2: 

My question was because xlsread checks the extension of the file,
and if it is ".csv" it should call csvread.
From some reason, csvread alone succeed, but xlsread fails.

Avinoam Kalma <avinoam>
Group Member
Wed 11 Mar 2015 07:09:46 PM UTC, comment #1: 

For .csv there's csvread, dlmread, csv2cell, importdata, .....

Only if you have Excel + OF windows package, or LibreOffice installed, xlsread can read csv files.

A looong time ago xlsread did fall back to csvread, but I deliberately threw it out.
For users who cannot distinguish one file type from another there's importdata.m

The warning could be more informative, agreed. I'll queue that for io-2.8.8

Closing, adapting some fields

Philip Nienhuis <philipnienhuis>
Group Member
Wed 11 Mar 2015 06:01:11 PM UTC, original submission:  


The file a.csv is


1,2,3
4,5,6
7,8,9



>> A=csvread ('a.csv')
A =
   1   2   3
   4   5   6
   7   8   9
>> A=xlsread ('a.csv')
Detected XLS interfaces: warning: called from
    xlsread at line 213 column 7
A = [](0x0)


Avinoam Kalma <avinoam>
Group Member

 

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

Attach Files:
   
   
Comment:
   

Attached Files
file #33371:  xlsread.m added by avinoam (11KiB - application/octet-stream)
file #33346:  xlsread.m added by philipnienhuis (11KiB - text/x-matlab)

 

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 avinoam (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 21 latest changes.

    Date Changed by Updated Field Previous Value => Replaced by
    2020-12-06 philipnienhuis StatusPostponed Wont Fix
        Open/ClosedOpen Closed
    2019-10-11 philipnienhuis Severity3 - Normal 1 - Wish
    2017-08-13 jwe Summary[OF] IO Package: xlsread cannot read a csv file [octave forge] (io) xlsread cannot read a csv file
    2015-04-01 philipnienhuis StatusNeed Info Postponed
    2015-03-27 philipnienhuis Priority5 - Normal 3 - Low
        StatusFixed Need Info
        Assigned toNone philipnienhuis
        Open/ClosedClosed Open
    2015-03-16 avinoam Attached File- Added xlsread.m, #33371
    2015-03-14 philipnienhuis Attached File- Added xlsread.m, #33346
    2015-03-13 philipnienhuis StatusIn Progress Fixed
        Open/ClosedOpen Closed
    2015-03-13 philipnienhuis SummaryIO Package: xlsread cannot read a csv file [OF] IO Package: xlsread cannot read a csv file
    2015-03-13 philipnienhuis StatusWont Fix In Progress
        Open/ClosedClosed Open
    2015-03-11 philipnienhuis Item GroupIncorrect Result Matlab Compatibility
        StatusNone Wont Fix
        Open/ClosedOpen Closed
        Release4.0.0-rc1 other
        Operating SystemMicrosoft Windows Any

    Back to the top

    Powered by Savane 3.13-3230.
    Corresponding source code