[Bug 31933] New: import patrons fails when patron attribute value contains a comma
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31933 Bug ID: 31933 Summary: import patrons fails when patron attribute value contains a comma Change sponsored?: --- Product: Koha Version: 21.11 Hardware: All OS: All Status: NEW Severity: enhancement Priority: P5 - low Component: Tools Assignee: koha-bugs@lists.koha-community.org Reporter: bernard.scaife@ptfs-europe.com QA Contact: testopia@bugs.koha-community.org Created attachment 142475 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=142475&action=edit patron upload file with the comma If patron attribute value contains a comma, the record is not imported Test: 1. create test.csv with unique cardnumber, mandatory fields surname, branchcode and categorycode and patron attributes 2. Patron attributes to contain a value which includes a comma each key/value pair surrounded by double-quotes "COURSE:Government, Economics and Commerce","CSCODE:ABC1234" 3. Ensure patron attribute types COURSE and CSCODE exist 4. Import with default values via patron import tool (cardnumber match) 5. If fails to import Import results : 0 imported records 0 overwritten 0 not imported because already in borrowers table and overwrite disabled 1 not imported because they are not in the expected format 1 records parsed Back to Tools Feedback: headerrow : cardnumber, surname, branchcode, categorycode, patron_attributes attribute string : COURSE:Government, Economics and Commerce Content-Type : text/csv Content-Disposition : form-data; name="uploadborrowers"; filename="patron_import_test.csv" Parsing upload file patron_import_test.csv Error analysis: Patron attribute Economics and Commerce is invalid for patron A12364162383. Repeat above but with file that doesn't have the comma: "COURSE:Government Economics and Commerce","CSCODE:ABC1234" Import succeeds comment: I was able to resolve as follows $string =~ s/,//g; on line 563 within sub generate_patron_attributes of /usr/share/koha/lib/Koha/Patrons/Import.pm # Fixup double quotes in case we are passed smart quotes $string =~ s/\xe2\x80\x9c/"/g; $string =~ s/\xe2\x80\x9d/"/g; +$string =~ s/,//g; -- 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=31933 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Severity|enhancement |minor Summary|import patrons fails when |Import patrons fails when |patron attribute value |patron attribute value |contains a comma |contains a comma -- 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=31933 David Roberts <david.roberts@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |david.roberts@ptfs-europe.c | |om -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31933 Janet McGowan <janet.mcgowan@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |janet.mcgowan@ptfs-europe.c | |om -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31933 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |martin.renvoize@ptfs-europe | |.com Assignee|koha-bugs@lists.koha-commun |jacob.omara@ptfs-europe.com |ity.org | -- 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=31933 --- Comment #1 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- We investigated this a little further last week.. It's a bit strange to allow more fields in the rows than in the header that describes which rows are available. Jacob is working on refining that to give clear errors when we find such cases in the input data and also add a bit more documentation around full quoting with escaped quotes the attributes field. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31933 Jacob O'Mara <jacob.omara@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |ASSIGNED --- Comment #2 from Jacob O'Mara <jacob.omara@ptfs-europe.com> --- After some more extensive searching it appears that the issue lies in how smart quotes are being handled within the spreadsheet editor itself prior to uploading to the koha db as I was getting some weird results once saving the csv in LibreOffice and opening in text editor. The easiest way we discussed to solve this is possibly just to disable smart quotes entirely in the spreadsheet editor and amend the documentation notes to specify the issues between escape quotes and smart quotes. Commas etc then seem to work as expected once disabled and in the format you specified below, bounded within one cell: "COURSE:Government, Economics and Commerce","CSCODE:ABC1234" we could probably do some kind of regex fix for the smart quotes issue as you suggested but Martin believes this is something we should discuss further first. It appears the error handling does in fact catch the mismatch of columns:values and probably won't need any additional tinkering. Let me know what you think. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31933 Jacob O'Mara <jacob.omara@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|ASSIGNED |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31933 --- Comment #3 from Jacob O'Mara <jacob.omara@ptfs-europe.com> --- Created attachment 145461 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=145461&action=edit Bug 31933: Import patrons fails when patron attribute value contains a comma Following on from our chat I have amended the documentation to reflect the required format for csv files and the issues with smart quotes. I have also included a reference csv download so users have a dummy file they can refer to for the correct format. Test Plan: 1. Navigate to the Import Patrons tool page 2. Observe the incorrect information in the notes box to the right (blue background) 3. Apply Patch 4. Observe that the documentation has now changed to reflect the above. 5. Test the CSV file download and check is in correct format. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31933 PTFS Europe Sandboxes <sandboxes@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #145461|0 |1 is obsolete| | --- Comment #4 from PTFS Europe Sandboxes <sandboxes@ptfs-europe.com> --- Created attachment 148166 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=148166&action=edit Bug 31933: Import patrons fails when patron attribute value contains a comma Following on from our chat I have amended the documentation to reflect the required format for csv files and the issues with smart quotes. I have also included a reference csv download so users have a dummy file they can refer to for the correct format. Test Plan: 1. Navigate to the Import Patrons tool page 2. Observe the incorrect information in the notes box to the right (blue background) 3. Apply Patch 4. Observe that the documentation has now changed to reflect the above. 5. Test the CSV file download and check is in correct format. Signed-off-by: Sally <sally.healey@cheshiresharedservices.gov.uk> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31933 Sally <sally.healey@cheshiresharedservices.gov.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |sally.healey@cheshireshared | |services.gov.uk --- Comment #5 from Sally <sally.healey@cheshiresharedservices.gov.uk> --- Works as described, thanks for the patch. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31933 Sally <sally.healey@cheshiresharedservices.gov.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31933 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Failed QA CC| |nick@bywatersolutions.com --- Comment #6 from Nick Clemens <nick@bywatersolutions.com> --- The sample attributes is missing a new line when testing in Firefox (on Ubuntu 22.04) We originally had this section: The attribute type code and a colon should precede each value. For example: INSTID:12345,LANG:fr or STARTDATE:January 1 2010,TRACK:Day. If an input record has more than one attribute, the fields should either be entered as an unquoted string (previous examples) Unquoted strings do work in my testing - are you removing this because quoted strings are safer? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31933 Martin Renvoize (ashimema) <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Failed QA |RESOLVED Resolution|--- |WONTFIX -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org