HTML/CSV export corrupts UTF-8 characters outside of Basic Multilingual Pane (BMP) ie code point >10000 #1197

Yuutakasan · 2017-06-06T14:59:48Z

OpenRefine 2.7 rc2

After reading UTF 8 file and executing export as UTF 8 file, garbled characters occurred.

displayed characters

有限会社なべ茶屋あさ𡌛

Exported garbled characters

有限会社なべ茶屋あさ��

other garbled export charactor sample
𣘺𣳾

thadguidry · 2017-06-06T15:17:11Z

@Yuutakasan hmm, that's weird. What is interesting is that it is showing 4 bytes (4 question marks) to hold just 1 character. I see that the code for that last character is actually 6 bytes however (which is the maximum that UTF-8 can hold per character.

𡌛 = \x0A\xF0\xA1\x8C\x9B\x0A

Further interesting is that when I copy and paste your last character into a single OpenRefine cell, I actually get a different character...

ጛ = \xE1\x8C\x9B

instead of

𡌛 = \xF0\xA1\x8C\x9B

@jackyq2015 Can you debug this ?

Yuutakasan · 2017-06-06T15:35:54Z

I will attach a sample file for reference.

import file
import.txt
export file
export.txt

There is a sense that this letter is actually used in the name of the corporation registered in Japan.

有限会社なべ茶屋あさ𡌛
 株式会社石𣘺組
 有限会社𣳾新商事
 𣳾幸１合同会社

thadguidry · 2017-06-06T15:43:08Z

@Yuutakasan When I export your import.txt file... I get

有限会社なべ茶屋あさ𡌛

You are probably not using a viewer like Notepad++ or similar that can show that last character as being \xED\xA1\x84\xED\xBC\x9B ?

But regardless... its a bug somewhere because somehow during export we change the bytes...

from
\xF0\xA1\x8C\x9B

to
\xED\xA1\x84\xED\xBC\x9B

Yuutakasan · 2017-06-06T15:45:30Z

I currently use EmEditor, I will try using Notpad ++.
EmEditor
https://www.emeditor.com/

Yuutakasan · 2017-06-06T15:47:27Z

I was able to reproduce the same phenomenon.

Yuutakasan · 2017-06-06T16:23:28Z

thank you. @thadguidry.
I tried testing with multiple character codes.
It seems that a character with a code point of 10000 or more will be garbled.

import txt
import-test-sample.txt

export txt
export-test-sample-txt.txt

thadguidry · 2017-06-06T16:34:47Z

@Yuutakasan Thanks, we'll have to let @jackyq2015 look into this specifically. My hunch is that we might not actually be storing it correctly in cell and so this https://github.com/OpenRefine/OpenRefine/blob/master/main/src/com/google/refine/exporters/CsvExporter.java#L108 might be giving back the wrong data in the first place. Otherwise its an issue in csvwriter itself here https://github.com/OpenRefine/OpenRefine/blob/master/main/src/com/google/refine/exporters/CsvExporter.java#L114

jackyq2015 · 2017-06-10T03:12:35Z

can you try to add -Dfile.encoding=UTF-8 to java command options to enforce the encoding?

Yuutakasan · 2017-06-10T08:33:27Z

OK.I will try it!

Yuutakasan · 2017-06-10T08:56:29Z

@jackyq2015
Can I test by adding "-Dfile.encoding = UTF-8" setting to the openrefine.l4j.ini file?

thadguidry · 2017-06-10T12:17:56Z

@Yuutakasan Yes, but you can also test it by adding it to the refine.ini file and starting refine.bat or refine.sh if your on Linux. Just uncomment the JAVA_OPTIONS= line

Yuutakasan · 2017-06-13T03:07:44Z

Since there is no pervert, I was worried whether the setting really worked.
it is executed once, I will share the result later.

jackyq2015 · 2017-06-15T01:24:17Z

@Yuutakasan Given your description, your file is not properly decoded as utf8. that's why I asked you to enforce it. Please note that system cannot 100% accurate to detect the encoding of random stream. There is some library like icu4j can help to improve the accuracy. Actually there is a PR(not merged yet) to introduce it. If you want to wet your hand, you can create your own branch and merge the PR to your own branch and have a try.

Yuutakasan · 2017-11-21T19:30:25Z

@jackyq2015 @thadguidry
Sorry for being late. I tried the settings I got the other day.

I downloaded openrefine - 2.8.
It changed to the following setting.
openrefine.l4j.zip
I imported garbled data before.
import-test-sample.zip

It is displayed normally
①

②

4.The exported file is garbled.
export-test-sample-txt.zip

Yuutakasan · 2017-11-21T19:34:21Z

I think that it is a character string conversion mistake at export timing, not an encoding discrimination bug at import timing.

Yuutakasan · 2017-11-21T19:40:45Z

other export pattern
Excel
import-test-sample-xlsx.zip

HTML

import-test-sample-html.zip

jackyq2015 · 2017-11-21T21:27:10Z

can you please add the encoding switch I provided above and try again?

Yuutakasan · 2017-11-22T01:06:08Z

@jackyq2015
The above processing is executed with the following settings.

configfile
https://github.com/OpenRefine/OpenRefine/files/1492661/openrefine.l4j.zip

openrefine.l4j.ini
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
-Xms256M
-Xmx1024M
-Djava.net.useSystemProxies=true
-Dfile.encoding="UTF-8"

tfmorris · 2020-06-11T18:22:08Z

Hopefully this has been fixed, but we should confirm for the 3.4 release.

tfmorris · 2020-06-12T18:47:57Z

From my limited testing, it looks like XLSX export is OK (at least for Numbers on my Mac), CSV is totally corrupted, and HTML broken for the higher code points as shown above.

tfmorris · 2020-06-12T20:40:18Z

@Yuutakasan Sorry for the long delay. The fix for this should make it into 3.4.

Fixes OpenRefine#1197. Previously we were using a funky ContentType to attempt to force a file download rather than display in browser, but this conflicted with attempts to save UTF-8 which was outside the Basic Multilingual Plane (BMP). By switching to ContentDisposition: attachment, which has been the preferred method for a number of years, we can avoid this conflict. As part of this, switch to using the "preview" param consistently to control preview vs download rather than the content type.

Yuutakasan · 2020-06-14T20:21:53Z

thank you. I'll test.

) * Use ContentDisposition instead of ContentType to control download Fixes #1197. Previously we were using a funky ContentType to attempt to force a file download rather than display in browser, but this conflicted with attempts to save UTF-8 which was outside the Basic Multilingual Plane (BMP). By switching to ContentDisposition: attachment, which has been the preferred method for a number of years, we can avoid this conflict. As part of this, switch to using the "preview" param consistently to control preview vs download rather than the content type. * Switch content type to text/plain Now that we don't need to use ContentType to control download behavior, we can use something more reasonable.

Yuutakasan · 2020-06-18T21:34:31Z

@tfmorris
I've tested it and it still seems to cause garbled text.
@wetneb Could you please open this issue?

USE OpenRefine 3.4 beta

1.import-test-sample.txt import ( No more problems than before. )
import-test-sample.txt

2. Configure Parsing Options ( No more problems than before. )

3.create project ( No more problems than before. )

4.export( Garbled characters in some formats. )
I used to test for HTML and CSV, but I also tested for other formats.
export openrefine 3.4.zip

①export tsv (Garbled characters)

②export csv (Garbled characters)

③export html (Garbled characters)
View in a text editor

View in a chrome

④export excel (NOT Garbled characters)

⑤export excel2007+ (NOT Garbled characters)

⑥export ODF SpreadSheet (NOT Garbled characters)

⑦export SQL (NOT Garbled characters)

⑧export SpreadSheet (NOT Garbled characters)

https://docs.google.com/spreadsheets/d/12zaOy_Mh9d-85Cv7pVXYV_TTc9gJJALR8gbXi-aAvuQ/edit?usp=sharing

wetneb · 2020-06-18T21:39:35Z

@Yuutakasan this has not been fixed in 3.4 beta - that version was released before this fix. For a version that we expect not to have the issue, try this one:
https://github.com/OpenRefine/OpenRefine-nightly-releases/releases/tag/3.4-beta-148-gf88c0e3

) * Use ContentDisposition instead of ContentType to control download Fixes #1197. Previously we were using a funky ContentType to attempt to force a file download rather than display in browser, but this conflicted with attempts to save UTF-8 which was outside the Basic Multilingual Plane (BMP). By switching to ContentDisposition: attachment, which has been the preferred method for a number of years, we can avoid this conflict. As part of this, switch to using the "preview" param consistently to control preview vs download rather than the content type. * Switch content type to text/plain Now that we don't need to use ContentType to control download behavior, we can use something more reasonable.

Yuutakasan · 2020-06-18T21:51:29Z

@wetneb Thanx. I'll re-test.

Yuutakasan · 2020-06-18T22:03:22Z

@wetneb @tfmorris
I have confirmed that the garbling has been resolved. Thank you very much.

USE openrefine-win-3.4-beta-148-gf88c0e3
https://github.com/OpenRefine/OpenRefine-nightly-releases/releases/tag/3.4-beta-148-gf88c0e3

export openrefine 3.4-beta-148-gf88c0e3.zip

export tsv

export csv

export html

export excel

export excel2007+

export odf

export spreadsheet

https://docs.google.com/spreadsheets/d/1TFTVPR-H-CDrGDC7yw252qc3QoBmOJVS9Uztu9VD7Eo/edit?usp=sharing

tfmorris · 2020-06-18T22:19:20Z

Excellent. Thank you very much for testing @Yuutakasan

Yuutakasan changed the title ~~Using export garbled characters.~~ After reading UTF 8 file and executing export as UTF 8 file, garbled characters occurred. Jun 6, 2017

thadguidry added the Type: Bug Issues related to software defects or unexpected behavior, which require resolution. label Jun 6, 2017

wetneb added encoding Selection of encoding at import time, or encoding issues in data cleaning import About importers in general - add a label for the data format if available export Exporting a project to some format. Use the format-specific sub-label if available labels Aug 2, 2017

wetneb mentioned this issue Aug 17, 2017

#486 add encoding guesser based on icu4j #1094

Closed

wetneb added CSV/TSV About the CSV/TSV import or export and removed import About importers in general - add a label for the data format if available labels Sep 18, 2017

jackyq2015 added the Priority: High Denotes issues that require urgent attention and may be blocking progress. label Oct 25, 2017

wetneb added the import About importers in general - add a label for the data format if available label Dec 22, 2019

tfmorris self-assigned this Jun 11, 2020

tfmorris added this to the 3.4 milestone Jun 11, 2020

tfmorris mentioned this issue Jun 12, 2020

Fixed HTML & CSV export of non-BMP UTF-8 #2712

Closed

tfmorris mentioned this issue Jun 12, 2020

Clean up i18n support for import / export #2713

Closed

tfmorris changed the title ~~After reading UTF 8 file and executing export as UTF 8 file, garbled characters occurred.~~ Export corrupts UTF-8 characters outside of Basic Multilingual Pane (BMP) ie code point >10000 Jun 13, 2020

tfmorris changed the title ~~Export corrupts UTF-8 characters outside of Basic Multilingual Pane (BMP) ie code point >10000~~ HTML/CSV export corrupts UTF-8 characters outside of Basic Multilingual Pane (BMP) ie code point >10000 Jun 13, 2020

tfmorris mentioned this issue Jun 14, 2020

Use ContentDisposition instead of ContentType to control download #2722

Merged

wetneb closed this as completed in #2722 Jun 16, 2020

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

HTML/CSV export corrupts UTF-8 characters outside of Basic Multilingual Pane (BMP) ie code point >10000 #1197

HTML/CSV export corrupts UTF-8 characters outside of Basic Multilingual Pane (BMP) ie code point >10000 #1197

Yuutakasan commented Jun 6, 2017 •

edited

Loading

thadguidry commented Jun 6, 2017 •

edited

Loading

Yuutakasan commented Jun 6, 2017 •

edited

Loading

thadguidry commented Jun 6, 2017 •

edited

Loading

Yuutakasan commented Jun 6, 2017

Yuutakasan commented Jun 6, 2017

Yuutakasan commented Jun 6, 2017 •

edited

Loading

thadguidry commented Jun 6, 2017

jackyq2015 commented Jun 10, 2017

Yuutakasan commented Jun 10, 2017

Yuutakasan commented Jun 10, 2017

thadguidry commented Jun 10, 2017

Yuutakasan commented Jun 13, 2017

jackyq2015 commented Jun 15, 2017

Yuutakasan commented Nov 21, 2017

Yuutakasan commented Nov 21, 2017

Yuutakasan commented Nov 21, 2017

jackyq2015 commented Nov 21, 2017

Yuutakasan commented Nov 22, 2017 •

edited

Loading

tfmorris commented Jun 11, 2020

tfmorris commented Jun 12, 2020

tfmorris commented Jun 12, 2020

Yuutakasan commented Jun 14, 2020

Yuutakasan commented Jun 18, 2020 •

edited

Loading

wetneb commented Jun 18, 2020

Yuutakasan commented Jun 18, 2020

Yuutakasan commented Jun 18, 2020 •

edited

Loading

tfmorris commented Jun 18, 2020

HTML/CSV export corrupts UTF-8 characters outside of Basic Multilingual Pane (BMP) ie code point >10000 #1197

HTML/CSV export corrupts UTF-8 characters outside of Basic Multilingual Pane (BMP) ie code point >10000 #1197

Comments

Yuutakasan commented Jun 6, 2017 • edited Loading

thadguidry commented Jun 6, 2017 • edited Loading

Yuutakasan commented Jun 6, 2017 • edited Loading

thadguidry commented Jun 6, 2017 • edited Loading

Yuutakasan commented Jun 6, 2017

Yuutakasan commented Jun 6, 2017

Yuutakasan commented Jun 6, 2017 • edited Loading

thadguidry commented Jun 6, 2017

jackyq2015 commented Jun 10, 2017

Yuutakasan commented Jun 10, 2017

Yuutakasan commented Jun 10, 2017

thadguidry commented Jun 10, 2017

Yuutakasan commented Jun 13, 2017

jackyq2015 commented Jun 15, 2017

Yuutakasan commented Nov 21, 2017

Yuutakasan commented Nov 21, 2017

Yuutakasan commented Nov 21, 2017

jackyq2015 commented Nov 21, 2017

Yuutakasan commented Nov 22, 2017 • edited Loading

tfmorris commented Jun 11, 2020

tfmorris commented Jun 12, 2020

tfmorris commented Jun 12, 2020

Yuutakasan commented Jun 14, 2020

Yuutakasan commented Jun 18, 2020 • edited Loading

wetneb commented Jun 18, 2020

Yuutakasan commented Jun 18, 2020

Yuutakasan commented Jun 18, 2020 • edited Loading

tfmorris commented Jun 18, 2020

Yuutakasan commented Jun 6, 2017 •

edited

Loading

thadguidry commented Jun 6, 2017 •

edited

Loading

Yuutakasan commented Jun 6, 2017 •

edited

Loading

thadguidry commented Jun 6, 2017 •

edited

Loading

Yuutakasan commented Jun 6, 2017 •

edited

Loading

Yuutakasan commented Nov 22, 2017 •

edited

Loading

Yuutakasan commented Jun 18, 2020 •

edited

Loading

Yuutakasan commented Jun 18, 2020 •

edited

Loading