[Bug 33635] New: CSV export display broken diacritics in Excel
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33635 Bug ID: 33635 Summary: CSV export display broken diacritics in Excel Change sponsored?: --- Product: Koha Version: master Hardware: All OS: All Status: NEW Severity: normal Priority: P5 - low Component: Staff interface Assignee: koha-bugs@lists.koha-community.org Reporter: katrin.fischer@bsz-bw.de QA Contact: testopia@bugs.koha-community.org CC: clemens.tubach@kit.edu, gmcharlt@gmail.com, michaela.sieber@kit.edu This has been a re-occurring support issue: When using one of Koha's many CSV download options, the exported CSV file doesn't open as UTF-8 in Excel, resulting in broken diacritics. LibreOffice doesn't show the issue. The only way to fix it right now is not to open the file directly, but to use the import functionality in Excel as it lets you pick the encoding and separators. This means a lot of extra steps and clicks, which make the CSV export functionalities in Koha hard to use. It turns out that Excel will import the CSV correctly, if we convert the encoding of the exported CSV file from UTF-8 to UTF-8-BOM. Problematic CSV exports include: * Exporting a basket summary page as CSV * Using CSV profile based exports in general, including: lists, basket, lost items report * ... Can we 'fix' the encoding to use BOM? -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33635 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=23685 CC| |martin.renvoize@ptfs-europe | |.com -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33635 --- Comment #1 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- I started thinking about this after looking at bug 23685 too. I'm not entirely sure if we want to add a BOM to our CSV exports or not.. there are still programs out there written to work with ascii only and they struggle with BOM's. That said, excel is the most likely target so adding a BOM for that would be sensible. We could do with checking that adding said BOM doesn't break other popular tools like OpenOffice or LibreOffice however. Text::CSV::Encoded doesn't have any native BOM support, but it appears we're just dealing with strings. Perhaps we could use File::BOM -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33635 --- Comment #2 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Created attachment 171650 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=171650&action=edit Bug 33635: Add BOM to report exports as CSV When you export a report as CSV excel will not recognise that it's encoding is utf-8 unless there is a correct Byte Order Mark prepended to the file. This patch adds such a BOM to the output file allowing Excel to treat the file correctly. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33635 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|koha-bugs@lists.koha-commun |martin.renvoize@ptfs-europe |ity.org |.com -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33635 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |Needs Signoff --- Comment #3 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Please test this -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33635 Michaela Sieber <michaela.sieber@kit.edu> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off --- Comment #4 from Michaela Sieber <michaela.sieber@kit.edu> --- Test plan: 1.) search for a title with one item and edit the item 2.) add the internal note: "Test the diacritics ä ö ü é è ÄÖÜ" 3.) copy the biblionumber of the title 4.) create an sql report: SELECT itemnotes_nonpublic from items where biblionumber=<copied biblionumber> 5.) run report 6.) download as csv file 7.) open in excel 8.) Check that the diacritics do not look like above (internal note) 9.) Apply Patch 10.) repeat steps 5-7 11.) Check that the diacritics look like above (internal note) -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33635 PTFS Europe Sandboxes <sandboxes@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #171650|0 |1 is obsolete| | --- Comment #5 from PTFS Europe Sandboxes <sandboxes@ptfs-europe.com> --- Created attachment 171677 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=171677&action=edit Bug 33635: Add BOM to report exports as CSV When you export a report as CSV excel will not recognise that it's encoding is utf-8 unless there is a correct Byte Order Mark prepended to the file. This patch adds such a BOM to the output file allowing Excel to treat the file correctly. Signed-off-by: Michaela Sieber <michaela.sieber@kit.edu> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33635 --- Comment #6 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- You're a star Micheal, thanks for testing ☺️ -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33635 Lucas Gass <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33635 Lucas Gass <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #171677|0 |1 is obsolete| | --- Comment #7 from Lucas Gass <lucas@bywatersolutions.com> --- Created attachment 171837 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=171837&action=edit Bug 33635: Add BOM to report exports as CSV When you export a report as CSV excel will not recognise that it's encoding is utf-8 unless there is a correct Byte Order Mark prepended to the file. This patch adds such a BOM to the output file allowing Excel to treat the file correctly. Signed-off-by: Michaela Sieber <michaela.sieber@kit.edu> Signed-off-by: Lucas Gass <lucas@bywatersolutions.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33635 Martin Renvoize (ashimema) <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- QA Contact|testopia@bugs.koha-communit |lucas@bywatersolutions.com |y.org | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33635 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |dcook@prosentient.com.au --- Comment #8 from David Cook <dcook@prosentient.com.au> --- Nice one. This kind of thing has driven me nuts elsewhere in the past... -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33635 Martin Renvoize (ashimema) <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)| |24.11.00 released in| | Status|Passed QA |Pushed to main -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33635 --- Comment #9 from Martin Renvoize (ashimema) <martin.renvoize@ptfs-europe.com> --- Thanks for all the hard work! Pushed to main for the next 24.11.00 release as RM Assistant -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33635 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Resolution|--- |FIXED Status|Pushed to main |RESOLVED --- Comment #10 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- Doesn't apply to 24.05.x, rebase if needed. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33635 Mathieu Saby <mathsabypro@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |mathsabypro@gmail.com --- Comment #11 from Mathieu Saby <mathsabypro@gmail.com> --- Oh it seems duplicate of https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39197 but I think I still have this issue (no BOM) in my 21.11 Koha. I am going to check again -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33635 --- Comment #12 from Mathieu Saby <mathsabypro@gmail.com> --- I contact my support provider to check if is not working in our Koha and see why. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33635 --- Comment #13 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- *** Bug 39197 has been marked as a duplicate of this bug. *** -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33635 --- Comment #14 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- (In reply to Mathieu Saby from comment #12)
I contact my support provider to check if is not working in our Koha and see why.
This is only fixed in 24.11 - see "versions released in". -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33635 --- Comment #15 from Mathieu Saby <mathsabypro@gmail.com> --- Oh of course, I need sleep ;-) and my Koha is 23.11... It could be nice if this patch could be applied to 24.05 and 23.11 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33635 --- Comment #16 from David Cook <dcook@prosentient.com.au> --- I'm working on a similar problem in labels/label-create-csv.pl and I noticed adding the BOM isn't enough in all cases. With certain data, for some reason, it doesn't come out UTF-8 encoded. It comes out Latin-1 encoded instead. I think because it bumps into things like \xF6 which are actually Unicode codepoints and not UTF-8 bytes. But if I explicitly state the output is UTF-8 under the BOM print then all is well: binmode STDOUT, ':encoding(UTF-8)'; Text::CSV_XS explains this a bit https://metacpan.org/pod/Text::CSV_XS#Unicode And... looking at reports/guided_reports.pl it looks like we get around that problem because we're using Text::CSV::Encoded. But first decoding and scrubbing the string for the header? Interesting... Looks like Text::CSV::Encoded was added back in 2014 and can still use Text::CSV_XS under the hood, so that's good... -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33635 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=39327 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33635 --- Comment #17 from David Cook <dcook@prosentient.com.au> --- (In reply to David Cook from comment #8)
Nice one. This kind of thing has driven me nuts elsewhere in the past...
PS Bug 39327 - "UTF-8 BOM missing from label creator CSV and some UTF-8 output broken" is ready for testing :D -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org