Bug 24330 - When importing patrons from CSV, automatically strip BOM from file if it exists
Summary: When importing patrons from CSV, automatically strip BOM from file if it exists
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Tools (show other bugs)
Version: Main
Hardware: All All
: P5 - low normal (vote)
Assignee: Kyle M Hall
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2020-01-02 15:04 UTC by Kyle M Hall
Modified: 2021-06-14 21:29 UTC (History)
5 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:
20.05.00, 19.11.03, 19.05.08, 18.11.14


Attachments
Example CSV with a BOM (338 bytes, text/csv)
2020-01-02 15:35 UTC, Kyle M Hall
Details
Bug 24330: When importing patrons from CSV, automatically strip BOM from file if it exists (1.64 KB, patch)
2020-01-02 15:36 UTC, Kyle M Hall
Details | Diff | Splinter Review
Bug 24330: When importing patrons from CSV, automatically strip BOM from file if it exists (1.67 KB, patch)
2020-01-03 09:13 UTC, David Nind
Details | Diff | Splinter Review
Bug 24330: When importing patrons from CSV, automatically strip BOM from file if it exists (1.75 KB, patch)
2020-01-30 11:15 UTC, Kyle M Hall
Details | Diff | Splinter Review
Bug 24330: When importing patrons from CSV, automatically strip BOM from file if it exists (1.80 KB, patch)
2020-01-30 15:17 UTC, Martin Renvoize
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Kyle M Hall 2020-01-02 15:04:07 UTC
We have a partner that exports UTF-8 CSV files, and is experiencing the same thing as the author of this article: https://www.freecodecamp.org/news/a-quick-tale-about-feff-the-invisible-character-cd25cd4630e7/

In short, Excel is inserting an invisible UTF-8 character at the start of the file, so that the column name "cardnumber" is actually named "\x{feff}cardnumber", causing "cardnumber" to be blank.

A simple solution is provided here: https://stackoverflow.com/questions/24390034/remove-bom-from-string-with-perl
Comment 1 Kyle M Hall 2020-01-02 15:35:59 UTC
Created attachment 96762 [details]
Example CSV with a BOM
Comment 2 Kyle M Hall 2020-01-02 15:36:32 UTC
Created attachment 96763 [details] [review]
Bug 24330: When importing patrons from CSV, automatically strip BOM from file if it exists

We have a partner that exports UTF-8 CSV files, and is experiencing the same thing as the author of this article: https://www.freecodecamp.org/news/a-quick-tale-about-feff-the-invisible-character-cd25cd4630e7/

In short, Excel is inserting an invisible UTF-8 character at the start of the file, so that the column name "cardnumber" is actually named "\x{feff}cardnumber", causing "cardnumber" to be blank.

A simple solution is provided here: https://stackoverflow.com/questions/24390034/remove-bom-from-string-with-perl

Test Plan:
1) Download the example.csv file
2) Attempt to import it using the patron import tool
3) Note the invalid column name error
4) Apply this patch, restart all the things!
5) Attempt the import again
6) Assuming you have a branchcode MPL and a cataegory code S, the patron should import!
Comment 3 David Nind 2020-01-03 09:13:11 UTC
Created attachment 96789 [details] [review]
Bug 24330: When importing patrons from CSV, automatically strip BOM from file if it exists

We have a partner that exports UTF-8 CSV files, and is experiencing the same thing as the author of this article: https://www.freecodecamp.org/news/a-quick-tale-about-feff-the-invisible-character-cd25cd4630e7/

In short, Excel is inserting an invisible UTF-8 character at the start of the file, so that the column name "cardnumber" is actually named "\x{feff}cardnumber", causing "cardnumber" to be blank.

A simple solution is provided here: https://stackoverflow.com/questions/24390034/remove-bom-from-string-with-perl

Test Plan:
1) Download the example.csv file
2) Attempt to import it using the patron import tool
3) Note the invalid column name error
4) Apply this patch, restart all the things!
5) Attempt the import again
6) Assuming you have a branchcode MPL and a cataegory code S, the patron should import!

Signed-off-by: David Nind <david@davidnind.com>
Comment 4 Jonathan Druart 2020-01-10 09:58:08 UTC
The example CSV file works correctly for me without the patch.
Comment 5 Jonathan Druart 2020-01-10 09:58:33 UTC
Do you get an error in the log?
Comment 6 Kyle M Hall 2020-01-30 11:15:09 UTC
Created attachment 98156 [details] [review]
Bug 24330: When importing patrons from CSV, automatically strip BOM from file if it exists

We have a partner that exports UTF-8 CSV files, and is experiencing the same thing as the author of this article: https://www.freecodecamp.org/news/a-quick-tale-about-feff-the-invisible-character-cd25cd4630e7/

In short, Excel is inserting an invisible UTF-8 character at the start of the file, so that the column name "cardnumber" is actually named "\x{feff}cardnumber", causing "cardnumber" to be blank.

A simple solution is provided here: https://stackoverflow.com/questions/24390034/remove-bom-from-string-with-perl

Test Plan:
1) Download the example.csv file
2) Attempt to import it using the patron import tool
3) Note the invalid column name error
4) Apply this patch, restart all the things!
5) Attempt the import again
6) Assuming you have a branchcode MPL and a cataegory code S, the patron should import!

Signed-off-by: David Nind <david@davidnind.com>

Signed-off-by: Yew Chung College Library <library@yccece.edu.hk>
Comment 7 Kyle M Hall 2020-01-30 12:00:45 UTC
(In reply to Jonathan Druart from comment #4)
> The example CSV file works correctly for me without the patch.

I must admit I can no longer recreate the error, it appears I've lost the specific file that caused the error. The uploaded file no longer has the feff bom.
Comment 8 Martin Renvoize 2020-01-30 15:17:36 UTC
Created attachment 98173 [details] [review]
Bug 24330: When importing patrons from CSV, automatically strip BOM from file if it exists

We have a partner that exports UTF-8 CSV files, and is experiencing the same thing as the author of this article: https://www.freecodecamp.org/news/a-quick-tale-about-feff-the-invisible-character-cd25cd4630e7/

In short, Excel is inserting an invisible UTF-8 character at the start of the file, so that the column name "cardnumber" is actually named "\x{feff}cardnumber", causing "cardnumber" to be blank.

A simple solution is provided here: https://stackoverflow.com/questions/24390034/remove-bom-from-string-with-perl

Test Plan:
1) Download the example.csv file
2) Attempt to import it using the patron import tool
3) Note the invalid column name error
4) Apply this patch, restart all the things!
5) Attempt the import again
6) Assuming you have a branchcode MPL and a cataegory code S, the patron should import!

Signed-off-by: David Nind <david@davidnind.com>
Signed-off-by: Yew Chung College Library <library@yccece.edu.hk>
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 9 Martin Renvoize 2020-01-30 15:18:56 UTC
I've run up against this one a few times myself.. I always wondered if we should be reading the BOM and doing something with it.. but actually I could never find any documentation as to 'what' to do.. so.. removing it makes sense to me.

Passing QA
Comment 10 Martin Renvoize 2020-01-30 15:45:57 UTC
Nice work everyone!

Pushed to master for 20.05
Comment 11 Joy Nelson 2020-02-10 23:30:04 UTC
Pushed to 19.11.x branch for 19.11.03
Comment 12 Lucas Gass 2020-02-14 20:46:33 UTC
backported to 19.05.x for 19.05.08
Comment 13 Hayley Pelham 2020-02-18 02:11:43 UTC
Backported to 18.11.x for 18.11.14.