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

 
 

bug #57812: [octave forge] (io) xlsread imports accounting negative numbers as NaN

Submitted by:  Ben Stanley <benstanley>
Submitted on:  Fri 14 Feb 2020 10:22:10 PM UTC  
 
Category:  Octave Forge Package Severity:  3 - Normal
Priority:  5 - Normal Item Group:  Incorrect Result
Status:  Fixed Assigned to:  Philip Nienhuis <philipnienhuis>
Originator Name:  Ben Stanley Open/Closed:  Closed
Release:  other Operating System:  Any

Add a New Comment (Rich Markup)
   

You are not logged in

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

 

( Jump to the original submission)

Sun 16 Feb 2020 06:40:51 PM UTC, comment #9: 

In the course of polishing many functions for a new io release I've pushed a number of additional fixes for __OCT_ods2oct__.m .
Currencies and percentages are now also read from the main cell office:value tag.

@Ben:
If you want you can grab the newest __OCT_ods2oct__.m version and try it out.

Thank you very much for your cooperation and quick answers.

Closing report.

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Sun 16 Feb 2020 02:22:28 PM UTC, comment #8: 

Oopsie, you're absolutely right. I fixed it in my local installation and copied the code it over in the local repo but forgot the str2double.
I took a cursory look at percentage and currency formats, but those do not seem to have '(0.00)' formats.

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Sun 16 Feb 2020 01:26:00 AM UTC, comment #7: 

comment #6:

> I pushed such a fix here:
> http://hg.code.sf.net/p/octave/io/rev/804c6f9e680f
>

I tested that mod. It produces an empty numarr.

>> pkg load io; [numarr,txtarr,rawarr,limits]=xlsread('Example.ods')

numarr = [](0x0)
txtarr =
{
  [1,1] = -1234
}
rawarr =
{
  [1,1] = -1234
}
limits =

  scalar structure containing the fields:

    numlimits = [](0x0)
    txtlimits =

      1   1
      1   1

    rawlimits =

      1   1
      1   1

>>


I believe the patch is missing a call to str2double, as follows:
                case "float"
                  rawarr{irow, icol} = str2double (getxmlattv (tcell, "office:value"));

With this change, I can now read numarr:

>> pkg load io; [numarr,txtarr,rawarr,limits]=xlsread('Example.ods')

numarr = -1234
txtarr = {}(0x0)
rawarr =
{
  [1,1] = -1234
}
limits =

  scalar structure containing the fields:

    numlimits =

       1   1
       1   1

    txtlimits = [](0x0)
    rawlimits =

       1   1
       1   1

>>


Attempting to read a file with an error produces the following result:

>> pkg load io; [numarr,txtarr,rawarr,limits]=xlsread('Example2.ods')


OpenJDK 64-Bit Server VM warning: Archived non-system classes are disabled because the java.system.class.loader property is specified (value = "org.octave.OctClassLoader"). To use archived non-system classes, this property must be not be set
Detected XLS interfaces: numarr =

  -1234
    NaN
      3

txtarr =
{
  [1,1] = #DIV/0!
}
rawarr =
{
  [1,1] = -1234
  [2,1] = #DIV/0!
  [3,1] =  3
}
limits =

  scalar structure containing the fields:

    numlimits =

       1   1
       1   3

    txtlimits =

       1   1
       2   2

    rawlimits =

       1   1
       1   3

>>


I believe this result is correct.
Please add str2double to the patch and it should be good.

(file #48418)

Ben Stanley <benstanley>
Sat 15 Feb 2020 10:21:38 PM UTC, comment #6: 

I pushed such a fix here:
http://hg.code.sf.net/p/octave/io/rev/804c6f9e680f

Marking as ready for test.
Please swap the _OCT_ods2oct_.m in your Octave installation with the one from the repo (get it here:
http://hg.code.sf.net/p/octave/io/file/804c6f9e680f/inst/__OCT_ods2oct__.m )
and report back if your issue has been solved.

(Nevertheless jOpendocument is probably still a better choice :-) )

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Sat 15 Feb 2020 09:51:33 PM UTC, comment #5: 

Rather than applying a regexp to the ctvalue, it's easier to just get the value of the "office:value" tag.
For io-2.6.0 I'll make that change but it may be that for percentages and currencies a similar trick must be applied.

Until io-2.6.0 is out you can use the jOpendocument Java library as a workaround; actually it's even much faster. Get it from jOpendocument.org, add the library to the javaclasspath:

javaaddpath ('/full/path/to/jOpenDocument-1.4rc2.jar')

(it seems they never made it beyond a 1.4-rc2 release but it works fine)

and specify the "JOD" interface when using xlsread:

[numarr, txtarr, rawarr, limits] = xlsread ('Example.ods', 1, '', 'jod')

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Sat 15 Feb 2020 11:53:56 AM UTC, comment #4: 

Yeah, I suppose this is one of the deficiencies of trying to interpret XML with regular expressions.
Just to be clear, am I right that you are trying to read an .ods file?
Anyway, can you please upload a (small) spreadsheet file portraying this bug to this bug report?

And please try other interfaces. AKAIK jOpendocument is the fastest for .ods files.

I'm about to upload a new io package release to the release tracker, no guarantees a fix for this will make it but this weeekend I have some time to look into this issue.

As to the Java server error, I also noted those, but AFAICS it doesn't affect spreadsheet I/O. Other Java errors refer to octave.jar' class helper, but I haven't been able to pinpoint it sufficiently for a separate bug report.

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Sat 15 Feb 2020 12:28:47 AM UTC, comment #3: 

Digging deeper the problem appears to be in _OCT_ods2oct_.m .

The following lines fail to make the correct conversion.

                case "float"
                  ## Watch out for error values. If so, <text> has #VALUE and office:value = 0
                  if (isfinite (str2double (ctvalue)))
                    rawarr{irow, icol} = str2double (getxmlattv (tcell, "office:value"));
                  else
                    rawarr{irow, icol} = NaN;
                  endif

For Example.ods, ctvalue="(1,234)"
str2double fails
Note however that the XML attribute office:value="-1234" and str2double would succeed.

I changed the test line to use regexp to look for the error values, instead of trying to convert the ctvalue to a number using str2double :
                  if isempty(regexp(ctvalue,'^#[A-Z]+$'))

After this change, my test case appears to work for me:

>> pkg load io;
>> [numarr,txtarr,rawarr,limits]=xlsread('Example.ods')


OpenJDK 64-Bit Server VM warning: Archived non-system classes are disabled because the java.system.class.loader property is specified (value = "org.octave.OctClassLoader"). To use archived non-system classes, this property must be not be set
Detected XLS interfaces: numarr = -1234
txtarr = {}(0x0)
rawarr =
{
  [1,1] = -1234
}
limits =

  scalar structure containing the fields:

    numlimits =

       1   1
       1   1

    txtlimits = [](0x0)
    rawlimits =

       1   1
       1   1

>>


With this change, I am also able to read a cell with the formula
=2/0
and has the text
#DIV0
reads in to octave numarr as
NaN

Ben Stanley <benstanley>
Fri 14 Feb 2020 11:00:31 PM UTC, comment #2: 

>> pkg load io; [numarr,txtarr,rawarr,limits]=xlsread('Example.ods')

numarr = NaN
txtarr = {}(0x0)
rawarr =
{
  [1,1] = NaN
}
limits =

  scalar structure containing the fields:

    numlimits =

       1   1
       1   1

    txtlimits = [](0x0)
    rawlimits =

       1   1
       1   1

>>

(file #48412,

Ben Stanley <benstanley>
Fri 14 Feb 2020 10:48:37 PM UTC, comment #1: 

My plan to work around this problem using rawarr has been thwarted. rawarr also contains NaN in place of the accounting negative numbers.

LibreOffice shows "(724)"
xmlread shows NaN
I hoped to see -724

Ben Stanley <benstanley>
Fri 14 Feb 2020 10:22:10 PM UTC, original submission:  

I have a finance problem that I am working on using GNU Octave.

I am using xlsread to read an Excel spreadsheet provided by a bank.

The file contains negative numbers written using accounting notation "(1234)" -> -1234.

xlsread imports such numbers as NaN.

In my use case, I need to be able to import such numbers as negative numbers.

For the moment I will work around the problem using the rawarr output from xlsread.

I have not tested what Matlab xlsread does in this case.

The problem appears to be in parsecell.m in the following lines:
        ## Watch out for scalar (non-empty) numarr where emptr = 0
        if (sum (emptr(:)) > 0)
          numarr(emptr(irowt:irowb, icoll:icolr)) = NaN;
        endif
        numarr(! emptr(irowt:irowb, icoll:icolr)) = ...
               cell2mat (rawarr(~emptr(irowt:irowb, icoll:icolr)));

The cell2mat function simply cannot handle the accounting negative number format.

I am experiencing the problem using Octave 4.2.2 as installed from the Ubuntu 18.04 repositories. However, I checked the latest parsecell.m in the mercurial repository and the lines of code mentioned above still appear unchanged.

Ben Stanley <benstanley>

 

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

Attach Files:
   
   
Comment:
   

Attached Files
file #48418:  Example2.ods added by benstanley (8KiB - application/vnd.oasis.opendocument.spreadsheet)
file #48412:  Example.ods added by benstanley (8KiB - application/vnd.oasis.opendocument.spreadsheet)
file #48413:  Example.png added by benstanley (127KiB - image/png)

 

Depends on the following items: None found

Items that depend on this one: None found

 

Carbon-Copy List
  • -email is unavailable- added by benstanley (Submitted the item)
  • -email is unavailable- added by benstanley
  •  

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

    Only project members can vote.

     

     

     

    Follow 12 latest changes.

    Date Changed by Updated Field Previous Value => Replaced by
    2020-02-16 philipnienhuis StatusReady For Test => Fixed
        Open/ClosedOpen => Closed
        Release4.2.2 => other
        Operating SystemGNU/Linux => Any
    2020-02-16 benstanley Attached File- => Added Example2.ods, #48418
    2020-02-15 philipnienhuis StatusNeed Info => Ready For Test
        Assigned toNone => philipnienhuis
    2020-02-15 mtmiller Summaryxlsread imports accounting negative numbers as NaN => [octave forge] (io) xlsread imports accounting negative numbers as NaN
    2020-02-15 philipnienhuis StatusNone => Need Info
    2020-02-14 benstanley Attached File- => Added Example.ods, #48412
        Attached File- => Added Example.png, #48413
    2020-02-14 benstanley Carbon-Copy- => Added ben.stanley@gmail.com

    Back to the top


    Powered by Savane 3.5