bugGNU Octave - Bugs: bug #59203, [octave forge] (io) Problem with...

 
 

bug #59203: [octave forge] (io) Problem with xlsread importing accent marks

Submitter:  None
Submitted:  Tue 29 Sep 2020 08:37:32 AM UTC
   
 
Category:  Octave Package Severity:  3 - Normal
Priority:  5 - Normal Item Group:  Matlab Compatibility
Status:  Fixed Assigned to:  None
Originator Name:  David Originator Email:  -email is unavailable-
Open/Closed:  * Closed Release:  * 5.2.0
Operating System:  * Microsoft Windows Fixed Release:  None
Planned Release:  None
* Mandatory Fields

Add a New Comment Rich Markup
   

Jump to the original submission

Fri 09 Oct 2020 09:39:08 PM UTC, comment #32: 

Thank YOU Markus.

I've deleted the tidyxml.m removal part from your patch, applied it, pushed some more fixes, made an io-2.6.2 release, tested it in Linux and Windows 10 (OK, with dev Octave only) and uploaded the tarballs to the release tracker.

@OP (David):
You are right that info on how spreadsheet I/O interfaces work is obscure.
I've put some more info about it in the xlsread/xlswrite Texinfo help headers. Hopefully it is a bit more clear now.

Closing report.

Philip Nienhuis <philipnienhuis>
Group Member
Fri 09 Oct 2020 07:17:33 PM UTC, comment #31: 

Thanks.
This is what I meant. :-)

Hm. So the POI interface seems to work correctly now also for codepoints outside the BMP (different from your screenshot "After_patch#2_and-OF-windows_fixes.png".


The UNO interface seems to be broken for characters outside the BMP. There is probably not much we can do about this (apart from maybe reporting the bug upstream and wait for a fix).
All currently used scripts are encoded in the BMP. So it is probably ok to leave that as it is for now.

Markus Mützel <mmuetzel>
Group administrator
Fri 09 Oct 2020 06:59:03 PM UTC, comment #30: 

I did the following:

>> [~, unotxt] = xlsread ("Unicode.xlsx", "", "", "uno")
unotxt =
{
  [1,1] = a
  [2,1] = ��
  [3,1] = ��
  [4,1] = ��
  [5,1] = ���
  [6,1] = ������
}
>> unotxt16 = cellfun ("uint16", (unotxt), "uni", 0)
unotxt16 =
{
  [1,1] = 97
  [2,1] =
    195  132
  [3,1] =
    195  159
  [4,1] =
    195  151
  [5,1] =
    225  185  151
  [6,1] =
    237  160  180  237  180  158
}


and the same for poi:

:
>> poitxt16 = cellfun ("uint16", (poitxt), "uni", 0)
poitxt16 =
{
  [1,1] = 97
  [1,2] =
    195  132
  [1,3] =
    195  159
  [1,4] =
    195  151
  [1,5] =
    225  185  151
  [1,6] =
    240  157  132  158
}


Is this what you meant?
The numbers in comment #29 match what I see. uint8 or uint16 makes no difference.

Philip Nienhuis <philipnienhuis>
Group Member
Fri 09 Oct 2020 01:25:49 PM UTC, comment #29: 

I am seeing the following:

>> load Unicode.xlsx-rsults_binary.mat
>> uint8 (poitxt{6})
ans =

  240  157  132  158

>> uint8 (comtxt{6})
ans =

  240  157  132  158

>> uint8 (unotxt{6})
ans =

  237  160  180  237  180  158

>> uint8 (octtxt{6})
ans =

  240  157  132  158


Yet on your screenshot, poitxt{6} seems to be incorrect...

Could you please read the files again with the POI and UNO interfaces and send what you see for `uint8 (poitxt{6})` and `uint8 (unotxt{6})` (without saving to a mat file!).

Markus Mützel <mmuetzel>
Group administrator
Fri 09 Oct 2020 01:11:17 PM UTC, comment #28: 

There you go.I gave you some choice :-)  hopefully it works out OK

(file #49950, file #49951, file #49952)

Philip Nienhuis <philipnienhuis>
Group Member
Thu 08 Oct 2020 03:55:38 PM UTC, comment #27: 

That looks good to me, too.

IIRC, the "-v7" .mat files do some encoding conversions for character arrays (that is for Matlab compatibility). That might lead to unexpected side effect if the character arrays contain invalid UTF-8.
E.g., poitxt{6} looks correct to me when I load the file you attached. I don't know how to best describe unotxt{6}. But it looks like it is "twice encoded" in UTF-8.

Could you read the files again with these toolboxes and send what you see for `uint8 (poitxt{6})` and `uint8 (unotxt{6})`.

Markus Mützel <mmuetzel>
Group administrator
Thu 08 Oct 2020 02:52:51 PM UTC, comment #26: 

hmm I don't see all files mentioned in my last comment but they are in the list below.

Philip Nienhuis <philipnienhuis>
Group Member
Thu 08 Oct 2020 02:51:30 PM UTC, comment #25: 

@Markus:
Applied the patch, updated % rebuilt OF-windows, and run some tests, results attached as png's <Before.....> and <After....>.

The results show that before the patch, as far as encoding goes:

  • COM has issues,
  • the Java-based interfaces POI and UNO work but not perfect,
  • OCT works wonderfully,

and after the patches for both io and windows:

  • the Java-based interfaces POI and UNO work but not perfect,
  • COM and OCT work wonderfully.


Q1: Are you satisfied with the results so far? (I am).

Q2: There maybe little we can do for the Java-based interfaces, can we?
I've attached a v7 mat-file with the ???txt made with the patched OF packages, so you can inspect the results from these interfaces.



file #49939,

Philip Nienhuis <philipnienhuis>
Group Member
Thu 08 Oct 2020 08:49:27 AM UTC, comment #24: 

My company's network is down this morning (was already wonky all day yesterday) so I can have a look now.

The patch look good, nice.
The only thing is that I'd prefer to leave tidyxml.m in the package for now, w/o being called from any of the spreadsheet functions. It offers a way to interpret raw XML, which may come in handy in some cases.

I'm crossbuilding a new dev version and in the meantime a dev windows package ("1.5.1') + io-2.6.2, once done I'll do some testing and if all is well, push and make a io-2.6.2 release.
No idea if I'll have time for all that today, we'll see.

Philip Nienhuis <philipnienhuis>
Group Member
Thu 08 Oct 2020 07:14:48 AM UTC, comment #23: 

The last patch is the only one that is still "valid". It replaces the other, previous ones.

Markus Mützel <mmuetzel>
Group administrator
Wed 07 Oct 2020 09:35:22 PM UTC, comment #22: 

Thanks,
I'll have a better look into it probably early next week at the earliest. Supposedly it'll be the last fix for a new io package version that Kai seems eager to release.
FTR, is it just one comprehensive patch to be applied, or do I need to apply some others in what order?

Upon reading just comment #21, good that you thought of and prepared for older Windows packages. Hopefully that doesn't make it too complicated or less performant. AFAICS the Java + OCT interfaces have no problems with character conversions ("Just Works" as you say), this bug is exclusively about COM.

[OT]
In the mean time I fixed my Office installation (see comment #17). Uninstalled Office 2013 and after several failed reinstallation attempts, clean-ups of stale registry entries and of left-behind .dlls all over the place, etc., I installed an Office 2016 I had lying around. Guess what: same issues (couldn't open a worksheet).
Out of ideas, I was about to reinstall Windows 10 at large when I discovered that the whole issue was merely some obscure ActiveX security setting (%$#&*@%). Must have been set behind my back by Avira antivirus (that I had deinstalled earlier on because it consistently quarantined conftest.exe; the latter is needed for installing OF packages that use autotools).
Sigh.
I'll probably put a note in the wiki about this ActiveX setting. ISTR I have hit this before, several years ago, but forgot about it.

Philip Nienhuis <philipnienhuis>
Group Member
Tue 06 Oct 2020 07:36:17 PM UTC, comment #21: 

John accepted the patch in bug #59214 and pushed it to the repo.

When that change will be released, it will no longer be necessary to convert anything when using the COM interface.

The attached patch makes the according preparations for that change. And it also tries to recover/write as much as possible with older versions of the windows package.

Like already written before it also removes the (optional but incorrect) character conversions with the OCT interface.

Wrt to the Java interfaces or LibreOffice: I don't have Java, the interfaces or LibreOffice installed. But if you are interested, you could try to read "Unicode.xlsx" or write a file with "tst_unicode_xlsx.m" with each of them.
My hopes are that they are cross-platform enough to assume UTF-8 and everything Just Works (TM). ;-)


(file #49929)

Markus Mützel <mmuetzel>
Group administrator
Sun 04 Oct 2020 01:05:27 PM UTC, comment #20: 

It may be wise to make this bug report dependent on bug #59214 then.

Philip Nienhuis <philipnienhuis>
Group Member
Fri 02 Oct 2020 11:31:10 AM UTC, comment #19: 

I decided to open bug #59214 with an un-polished patch.
Let's wait for John's opinion.

Markus Mützel <mmuetzel>
Group administrator
Fri 02 Oct 2020 11:18:26 AM UTC, comment #18: 

Sorry, to interrupt again.
I had a quick look at the windows package. And it seems easy enough to have it return and accept UTF-8 encoded strings.
That would probably be the best way to go forward.

I'll open a separate bug report when I have sorted my changes a little bit. Let's wait for John D's opinion on that.

If he agrees to change this in the windows package, the fix for the bug here would lead to simpler code and allow to read and write any character with the COM interface.

Markus Mützel <mmuetzel>
Group administrator
Fri 02 Oct 2020 06:59:36 AM UTC, comment #17: 

OK, I'm convinced :-)

I'll try your patches later this weekend.
On my side there's a bit of a problem as Office 2013 on a fresh Win10 install on my main development box is somehow borked, it doesn't do ActiveX/COM very well.
On my other dev box the very same code works fine but I need that box for my day job.

Reading back the discussion in bug #49222 it seems that I deliberately left the COM interface out of the encoding conversions, but I can't remember why. IIRC Qt5 vs. Qt4 played a role, Octave's internal encoding, etc.
That bug report is 4 years ago and TTBOMK this bug report is the first since.
So it needs a bit more careful thinking over, at least on my side :-)

Philip Nienhuis <philipnienhuis>
Group Member
Fri 02 Oct 2020 06:41:55 AM UTC, comment #16: 

I used the attached script to try and write non-ASCII characters to .xlsx files. It contains some ASCII, Latin-1, and two more characters inside and outside the BMP. It is encoded in UTF-8. So you might have to change your character encoding settings in Octave if you want to run tests with it.

With the attached patch, it is possible to write and read all of these characters with the OCT interface. (Excel 2013 seems to have problems displaying the character outside the BMP though. Not an Octave bug.)
The COM interface can only write and read characters from the Latin-1 subset of Unicode (codepoints 0-255). It looks like the windows package is limited by Octave's `char` type (only 8-bit) in this respect. So not much, we can do about this here. I thought the best thing would be to replace unencodable characters with a "?" (question mark).

I also changed the docstring of `utf82unicode` and `unicode2utf8` to make clearer what they do and which limitations they have. With that change, it's probably ok to keep them around for a bit longer if that's your preference.

That patch replaces the previous patches here.

I did all my tests on Windows 10. IIUC, the command window in Octave is broken for UTF-8 on Windows 7. You shouldn't try and work around the broken command window (e.g. by using the "wrong" encoding for character arrays). That would break lots of other things. The variable editor should work though to check the results.

(file #49908, file #49909)

Markus Mützel <mmuetzel>
Group administrator
Thu 01 Oct 2020 09:59:06 PM UTC, comment #15: 

@Philip: I cannot emphasize enough how wrong the current implementation is wrt character encoding. This isn't a question of preference. It's just wrong.
Please, delete those functions.
I'll provide alternative implementations when I come around to it.

Markus Mützel <mmuetzel>
Group administrator
Thu 01 Oct 2020 09:48:39 PM UTC, comment #14: 

@Markus:
Thanks very much for the patches and the unicode test spreadsheet. I'll check it out asap.

> Please, remove "unicode2utf8" and "utf82unicode"

Hmmm, while unicode2native couldn't do the job here, unicode2utf8 did.
Anyway I'd like to keep them around a little longer.
Did you try reading the spreadsheet uploaded by the OP? (its XML is encoded in UTF8, I checked earlier tonight.) I couldn't get meaningful results with unicode2native.

Anyway, makes me wonder how the Java based interfaces do their job as regards encoding conversion. They're all OSS but each of them is a daunting pile of code to dig though.

Philip Nienhuis <philipnienhuis>
Group Member
Thu 01 Oct 2020 08:46:25 PM UTC, comment #13: 

And another one which doesn't break reading numerical values.

I shouldn't do this while I'm tired...

(file #49905)

Markus Mützel <mmuetzel>
Group administrator
Thu 01 Oct 2020 08:29:03 PM UTC, comment #12: 

Oops. Forgot to copy the last version back to the repository.
Updated version attached.

(file #49904)

Markus Mützel <mmuetzel>
Group administrator
Thu 01 Oct 2020 08:17:56 PM UTC, comment #11: 

Please, remove "unicode2utf8" and "utf82unicode". I wrote those functions when I didn't understand how encoding works.
They break the encoding when invoked for the OCT interface and they are de-activated for the COM interface.

The attached patch does that change for "xls2oct.m" and implements the correct conversion for the COM interface (by default now).

The COM interface can't read characters above Unicode code point 255 (see the attached example that correctly reads with OCT but not with COM). That might be an issue with the windows package. The characters are already lost in line 122 in "__COM_spsh2oct__.m".
I added a FIXME note in "xls2oct.m" that the conversion might need revisiting when/if something should change there.

I've only tested the OCT and COM interfaces.

(file #49902, file #49903)

Markus Mützel <mmuetzel>
Group administrator
Thu 01 Oct 2020 07:50:06 PM UTC, comment #10: 

help xlsread ?
help subsrefs ?

Philip Nienhuis <philipnienhuis>
Group Member
Thu 01 Oct 2020 07:11:12 PM UTC, comment #9: 

thanks for your help.
I was making some tests on my own and if I run the "xlsread" command before loading the "windows" package, all the next calls to that command import the accent marks well (maybe is because Octave choose the correct interface).

********
pkg load io
[numeros1, texto1] = xlsread("./Excel_xls.xlsx", 'page1');
pkg windows
[numeros2, texto2] = xlsread("./Excel_xls.xlsx", 'page1');
********


P.S. where can i find the documentation to know how is the interface that Octave use and more advanced thing, like subrefs.

Anonymous
Thu 01 Oct 2020 06:47:09 PM UTC, comment #8: 

David, I can confirm your issue with the file you uploaded.
ISTR similar issues and yes: this heavily smells of bug #49222.

As to your issue, it's only an issue with the COM interface; all other interfaces that can read .xlsx, i.e. OCT (built-in), POI (Apache POI - Java based) and UNO LibreOffice, using Java-based UNO bridge), do the right thing.

Anyway xlsread using COM does read the strings OK but doesn't convert them into UTF8. Easily seen in the Variable Editor, see your own attached pic. A related issue is that in Windows, Octave's command window still cannot show unicode characters.

As a provisional workaround you can do the following after invoking xlsread:

texto = cellfun (@(x) char (unicode2utf8 (x)), texto, "uni", 0)

(unicode2utf8.m is included in the io package.)

I have to think about how to get this fixed; maybe not in time for a next io release. Reading back bug #49222 from 4 years back, it looks like character encoding in Octave is still a bit of a minefield.
cc'íng our encoding guru:
@Markus: what do you think, can / should we also unconditionally apply unicode2utf8 to strings when reading with the COM interface? IIRC in the end you weren't enthusiastic about this.

BTW: I hit & fixed a separate bug in xlsread, it should recognize options structs but I forgot to implement that when I restructured the .ods/.xls for io-2.6.0.
I'll think about how along that way xls2oct.m could invoke unicode2utf8 rather than unicode2native, as is done now near the end of xls2oct.m if the "convert_utf" option is set to true.

Philip Nienhuis <philipnienhuis>
Group Member
Thu 01 Oct 2020 04:57:14 PM UTC, comment #7: 

As to the windows package, your lat observations are helpful, thanks. I think what happens is as follows:

Spreadsheet I/O in Octave happens with so-called "interfaces", which comprises external software to do the actual I/O.
There is one built-in interface called "OCT" which is invoked if no other interfaces are found on your system.
Now the windows package supplies an external interface called "COM" which invokes Windows system libraries (ActiveX) to invoke MS-Excel itself.
If you don't specify an interface yourself, Octave will select one automatically with a preference for external interfaces because those are often better in some respects.
So, if you load the window package, Octave selects behind your back the COM interface. If you don't load it, it selects OCT. At least on your system they give different results.

I'll have a look. If it's a bug in the COM interface I hope I can fix it before a new io release is due (next week I hope).

Philip Nienhuis <philipnienhuis>
Group Member
Thu 01 Oct 2020 12:35:22 PM UTC, comment #6: 

Okay, I check that the issue come from "windows" package.
Because if you execute this other code the accent marks does not appear:

********
pkg load io
pkg windows
[numeros, texto] = xlsread("./Excel_xls.xlsx", 'page1');
********

Anonymous
Thu 01 Oct 2020 12:02:57 PM UTC, comment #5: 

And vice versa, when I run the program that does not recognise the accent marks first, the "xlsread_Example.m" does not recognise the accent marks either.

Anonymous
Thu 01 Oct 2020 11:50:17 AM UTC, comment #4: 

This is very strange when I execute this piece of code:

********
pkg load io
[numeros, texto] = xlsread("./Excel_xls.xlsx", 'page1');
********

Everything goes fine (“xlsread” recognize accents marks), and without closing Octave I open the program, where I have the problem, “xlsread” recognize accents marks.
But if I open Octave clicking directly the program or if I open Octave from his “.exe” and then I go to the program and execute it, “xlsread” command does not import the accents marks.

(file #49899, file #49900)

Anonymous
Tue 29 Sep 2020 04:10:43 PM UTC, comment #3: 

@OP: Can you attach a small Excel file that demonstrates the problem?

Also, does it fail only for xlsread or for csvread as well?

Anonymous
Tue 29 Sep 2020 08:46:01 AM UTC, comment #2: 

I'm sorry for the spelling mistakes, I have tried to edit the post but I don't know how to do it

Anonymous
Tue 29 Sep 2020 08:44:07 AM UTC, comment #1: 


original submission:

> Hello,
>
> When I used “xlsread” command to import an excel in Matlab execution import the accent mark symbols but no in Octave.
>
> I have been changing de import codification but without success. Even so, with the default system encoder Octave “see” accent marks on the scripts.

Anonymous
Tue 29 Sep 2020 08:37:32 AM UTC, original submission:  

Hello,

When I used “xlsread” command to import an excel in Matlab execution import the accent mark symbols but no in Octave.

I have been changing de import codification but without success. Even so, with the default system encoder Octave “see” accent marks on the scripts.

Anonymous

 

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

Attach Files:
   
   
Comment:
   

Attached Files
file #49950:  Unicode.xlsx-rsults_binary.mat added by philipnienhuis (1KiB - application/octet-stream)
file #49951:  Unicode.xlsx-rsults_v4.mat added by philipnienhuis (108B - application/octet-stream)
file #49952:  Unicode.xlsx-rsults_v6.mat added by philipnienhuis (2KiB - application/octet-stream)
file #49939:  Unicode.xlsx-results.mat added by philipnienhuis (506B - application/octet-stream)
file #49929:  bug59203_xls_unicode_v2.patch added by mmuetzel (10KiB - application/octet-stream)
file #49908:  bug59203_xls_unicode.patch added by mmuetzel (10KiB - application/octet-stream)
file #49909:  tst_unicode_xlsx.m added by mmuetzel (114B - text/plain)
file #49905:  bug59203_xlsread_unicode_v3.patch added by mmuetzel (5KiB - application/octet-stream)
file #49904:  bug59203_xlsread_unicode_v2.patch added by mmuetzel (5KiB - application/octet-stream)
file #49902:  bug59203_xlsread_unicode.patch added by mmuetzel (5KiB - application/octet-stream)
file #49903:  Unicode.xlsx added by mmuetzel (9KiB - application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
file #49900:  xlsread_Example.m added by None (256B - application/octet-stream)
file #49899:  Excel_xls_Example.xlsx added by None (10KiB - application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
file #49883:  OctaveVariableImport.PNG added by None (20KiB - image/png)
file #49884:  DefaultOctaveEncoding.PNG added by None (41KiB - image/png)
file #49882:  MatlabVariableImport.PNG added by None (40KiB - image/png)

 

Digest:
   bug dependencies.

Items that depend on this one: None found

 

Carbon-Copy List
  • -email is unavailable- added by mmuetzel (Updated the item)
  • -email is unavailable- added by philipnienhuis
  • -email is unavailable- added by philipnienhuis
  • -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 25 latest changes.

    Date Changed by Updated Field Previous Value => Replaced by
    2020-10-09 philipnienhuis StatusPatch Submitted Fixed
        Open/ClosedOpen Closed
    2020-10-09 philipnienhuis Attached File- Added Unicode.xlsx-rsults_binary.mat, #49950
        Attached File- Added Unicode.xlsx-rsults_v4.mat, #49951
        Attached File- Added Unicode.xlsx-rsults_v6.mat, #49952
    2020-10-08 philipnienhuis Attached File- Added Before_patch#2_and-OF-windows_fixes.png, #49938
        Attached File- Added Unicode.xlsx-results.mat, #49939
        Attached File- Added After_patch#2_and-OF-windows_fixes.png, #49940
    2020-10-06 mmuetzel Attached File- Added bug59203_xls_unicode_v2.patch, #49929
        StatusConfirmed Patch Submitted
        Dependencies- Depends on bugs #59214
    2020-10-02 mmuetzel Attached File- Added bug59203_xls_unicode.patch, #49908
        Attached File- Added tst_unicode_xlsx.m, #49909
    2020-10-01 mmuetzel Attached File- Added bug59203_xlsread_unicode_v3.patch, #49905
    2020-10-01 mmuetzel Attached File- Added bug59203_xlsread_unicode_v2.patch, #49904
    2020-10-01 mmuetzel Attached File- Added bug59203_xlsread_unicode.patch, #49902
        Attached File- Added Unicode.xlsx, #49903
    2020-10-01 philipnienhuis StatusNone Confirmed
        Carbon-Copy- Added mmuetzel
        Carbon-Copy- Added philipnienhuis
    2020-10-01 None Attached File- Added Excel_xls_Example.xlsx, #49899
        Attached File- Added xlsread_Example.m, #49900
    2020-09-29 None Attached File- Added MatlabVariableImport.PNG, #49882
        Attached File- Added OctaveVariableImport.PNG, #49883
        Attached File- Added DefaultOctaveEncoding.PNG, #49884

    Back to the top

    Powered by Savane 3.13-d3ae.
    Corresponding source code