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

 
 

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

Submitter:  Ben Stanley <benstanley>
Submitted:  Fri 14 Feb 2020 10:22:10 PM UTC
   
 
Category:  Octave Package Severity:  3 - Normal
Priority:  5 - Normal Item Group:  Incorrect Result
Status:  Fixed Assigned to:  philipnienhuis
Originator Name:  Ben Stanley 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 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>
Group Member
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>
Group Member
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>
Group Member
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>
Group Member
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>
Group Member
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
  •  

    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 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 -email is unavailable-

    Back to the top

    Powered by Savane 3.13-4448.
    Corresponding source code