Created attachment 142475 [details] 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;
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.
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.
Created attachment 145461 [details] [review] 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.
Created attachment 148166 [details] [review] 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>
Works as described, thanks for the patch.
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?