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

 
 

bug #62246: [octave forge](io) xlsread miscalculates serial date number using UNO interface

Submitter:  None
Submitted:  Fri 01 Apr 2022 12:30:09 PM UTC
   
 
Category:  Octave Package Severity:  3 - Normal
Priority:  5 - Normal Item Group:  Incorrect Result
Status:  Invalid / Not an Octave Bug Assigned to:  philipnienhuis
Originator Name:  Originator Email:  -email is unavailable-
Open/Closed:  * Closed Release:  * 6.3.0
Operating System:  * Microsoft Windows Fixed Release:  None
Planned Release:  None
* Mandatory Fields

Add a New Comment Rich Markup
   

Jump to the original submission

Sun 10 Apr 2022 10:02:14 PM UTC, comment #6: 

Thank you for reporting back.
And no need to apologize for thinking you hit a bug, I'd rather have such "false positives" than real bugs that are noted but remain unreported.

That said, all those different spreadsheet date formats and epochs do constitute a nasty situation; little wonder you & colleagues got confused.

Philip Nienhuis <philipnienhuis>
Group Member
Sun 10 Apr 2022 09:12:15 AM UTC, comment #5: 

First - sorry for the late reply. It was a bit hectic this week.

I unzipped the .ods as you told me (thank you!) and I think I've got the problem in the original file (which I can't share, sorry) down:

<table:table-cell table:style-name="ce811" office:value-type="string" calcext:value-type="string">
<text:p>19.08.2008</text:p>

So what happened, was:
- the .ods (exported from the .xls) contains a date-column in mixed format (dots, dashes, German, American) that is only partially formatted as dates, the rest are strings; it used to be only human-readable, so not a problem for 10+ years
- I saw that xlsread() has an optional rawdata output, which contained the correct serial date numbers (Linux, Octave 6.3, UNO-interface for xlsread), I happily used that rawdata to export the dates to our SQL database
- someone else ran the same script (Windows10, Octave 6.3, UNO-interface), the string-formatted DD.MM.YYYY """date""" did not get recognised as a date, thus the epoch difference wasn't added
- when we tried to reproduce this we discovered that a different interface, like OCT, recognises the DD.MM.YYYY-string as a date on both systems

... I agree that this isn't actually a bug (I was too quick to post this, I should have checked the content.xml first), but it's a rather nice feature of the interfaces? The note in the io-package you quoted should cover that hiccup.
Again, sorry that I didn't check it more thoroughly before posting.

Anonymous
Mon 04 Apr 2022 05:38:09 PM UTC, comment #4: 

Opening the supplied file in both Excel 2016 and LibreOffice 7.0.1.2 gives a value of 39681 for cell A2. I must say I didn't know that LibreOffice's DATEVALUE function returns Excel datenums, i.e. with epoch 31 Dec 1899. I tried on Linux and Windows and got the exact same datenum in LibreOffice, so I'm at loss as to why you + colleagues get different results.

It's easy to unzip the .ods file you supplied (.ods is a zipped-up directory structure).
If you do and inspect content.xml, you'll see:

:
<table:table-cell table:formula="of:=DATEVALUE("2008-08-21")" office:value-type="float" office:value="39681" calcext:value-type="float">
<text:p>39681</text:p>
</table:table-cell>
:


(line wrap!)
Now, if the office:value-type tag has the value "float", Octave (i.e., the io package) expects the office:value tag to have a float value and that value (39681) is what you'll get; correctly IMO.
And, this is independent of operating system; there's no code in the UNO interface functions depending on Windows or *nix or whatever.

Just FYI, some other .ods file I used for testing has date cells formatted as follows:

:
<table:table-cell office:value-type="date" office:date-value="1978-11-03">
<text:p>November 3, 1978</text:p>
</table:table-cell>
:


Again, given your description in comment #2 I think the whole issue might be related to .xls files being interpreted and converted w/o proper, or desired, or unduly expected, interpretation of date cell formatting.
I do have an itchy feeling about your description in comment #2; I can't reproduce some of the things you wrote there so I wonder if it is the complete story.
However, AFAICS the io package / UNO interface just works as designed.

A workaround for your workflow could be to include a stanza in your Octave script that checks if date values (datenums) are smaller than 693960 and if so, add that number. If the dates are in a certain column it's just one vectorized statement.

Closing report as "Invalid".
If you insist there's still a bug this report can be reopened.

Philip Nienhuis <philipnienhuis>
Group Member
Mon 04 Apr 2022 12:17:26 PM UTC, comment #3: 

Thanks for reporting back, and yeah what you describe is what I suspected.

At issue is that the old Excel .xls (BIFF8 and older BIFF5) formats don't support dates internally. Only doubles, strings, booleans and maybe formulas are supported natively, all other "types" are distinguished by the cell formats. Date and time values belong to those.

So you and/or your colleagues used an .xls file where internally the date values are based to be based upon Excel's old BIFF epoch of 31-12-1899 - if they were formatted as dates, that is.
Now, LibreOffice may recognize that and transform it into .ods dates with epoch 1-1-0000, but that doesn't always happen reliably. Are you sure the .xls file with which it all started had those date value cells explicitly formatted as dates?

FYI, in the io package there is an .html document (in <io package install dir>/doc/ where somewhere under the title "MATLAB COMPATIBILITY AND SOME GOTCHAS" you can read this:

> ...

Note that you can get an interesting confusing with regard to dates when reading .ods files and then writing them to disk as .xls or .xlsx files, or vice versa.
...
These days .xlsx (OOXML) support in LibreOffice and Excel itself is much more reliable than when this stanza was written but using .xls / BIFF in workflows still remains a PITA.

I'll have a look if I can reproduce this bug report's issue with the file you uploaded, but I'm afraid the collection of software, let alone mix of operating systems, used to create and process the file is such that I have little hope if I can make a difference.

At the moment my best advice would be to just be wary of epoch issues in spreadsheet files and thoroughly check them if .xls (BIFF8 or BIFF5) is used somewhere in your workflow.
It might help if you can also upload or in some other way share the original .xls file, but I'd understand if that would be undesired for your company.

Philip Nienhuis <philipnienhuis>
Group Member
Mon 04 Apr 2022 07:21:05 AM UTC, comment #2: 

Sorry for the missing info. For clarification, this is how we found the bug:
1. Created an Excel .xls that we exported to an .ods; exporting the data in the date column (mixed format, which is why we import the raw data) into a PSQL database using an Octave script. This script is adapted for both Linux and Windows and runs regularly.
2. We noticed that some dates in the PSQL database are not, in fact, post-2000 as they should be, after the script was run on a Windows machine.
3. Each of us opened LibreOffice Calc to test some stuff with the DATEVALUE() function and noticed that xlsread's rawdata output on Windows equals the DATEVALUE() output, whereas on Linux it's the correct serial date number.

Some version infos:
- LibreOffice 7.2 on both systems
- Octave 6.3 on Linux, Ubuntu 20.04.4
- Octave 6.3 on Windows 10
- database package 2.4.4

The original .ods file was created by some old Excel version and then exported as an .ods, but it can be reproduced by a LibreOffice-created .ods file.

(file #53042)

Anonymous
Sat 02 Apr 2022 09:41:08 PM UTC, comment #1: 

Thanks for your report.
Could you please attach a simple .ods file with a few dates showing this error, used on Windows and Linux? (did you read the same file on those OS-es?).
Then I can try to reproduce it and figure out what goes wrong.

A first observation is that on Windows, Excel's epoch (31-12-1899) isn't converted to Octave's/Matlab's (1-1-0000) by adding 693960. Maybe that happens if a spreadsheet created by Excel has been rewritten by LibreOffice and L.O. didn't recognize Excel's internal date format. Or some other scenario along those lines.
==> So, how have those .ods files originally been created? by LibreOffice or Excel? and if by L.O., in Linux or Windows?

BTW what LibreOffice version(s) do you use on Windows and Ubuntu ?

Philip Nienhuis <philipnienhuis>
Group Member
Fri 01 Apr 2022 12:30:09 PM UTC, original submission:  

Hi,

for reading the raw data of an .ods sheet with a date format, I can see different results in Windows 10 and Ubuntu. The raw data output on the Windows machine is equal to the output of LibreOffice's DateValue function, whereas under Ubuntu it's the actual correct serial date number for further wrangling in Octave.

We noticed that by trying the 21st Aug 2008. The output on the Ubuntu machine is 733641, the output on the Windows machine is 39681, which equals =DATEVALUE("2008-08-21") in LibreOffice.

That bug only shows up using the UNO interface of xlsread, using e.g. OCT is a suitable workaround.

Anonymous

 

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

Attach Files:
   
   
Comment:
   

Attached Files
file #53042:  test_dateformat.ods added by None (8KiB - application/vnd.oasis.opendocument.spreadsheet)

 

Depends on the following items: None found

Items that depend on this one: None found

 

Carbon-Copy List
  • -email is unavailable- added by None (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 6 latest changes.

    Date Changed by Updated Field Previous Value => Replaced by
    2022-04-04 philipnienhuis StatusIn Progress Invalid / Not an Octave Bug
        Assigned toNone philipnienhuis
        Open/ClosedOpen Closed
    2022-04-04 philipnienhuis StatusNeed Info In Progress
    2022-04-04 None Attached File- Added test_dateformat.ods, #53042
    2022-04-02 philipnienhuis StatusNone Need Info

    Back to the top

    Powered by Savane 3.13-4b48.
    Corresponding source code