[Koha-bugs] [Bug 23668] Manual on patron import: Information on syntax for patron attributes

[bugzilla-daemon at bugs.koha-community.org]

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23668

--- Comment #3 from Philip Orr <philip.orr at lmscloud.de> ---
Thanks Danielle!
Doing some testing of my own, not with cli but with the actual Patron import
tool.
I should mention I'm using Excel to edit the standard .csv file that the patron
import tool gives you. These tests were done before converting in Excel to csv
format:

Sample data from the .csv file:
cardnumber,surname,branchcode,categorycode,patron_attributes
23529000035676,Acevedo,MPL,S,COLOR:Red,FAMILY:John Doe,SHOW_BCODE:1
23529001000463,Acosta,MPL,PT,COLOR:Blue,FAMILY:Jane Doe,SHOW_BCODE:0

(you will need to first set up two new patron attributes, "COLOR" and "FAMILY"
if you want to test with this data!)

with no quotes as above:
COLOR:Red,FAMILY:John Doe,SHOW_BCODE:1
Not OK. Only loads "COLOR:Red".

with simple quotes:
"COLOR:Red,FAMILY:John Doe,SHOW_BCODE:1"
Not OK; the entire string is apparently not recognized.

Error line:
    Line 2 Critical field "surname" missing (borrowernumber: UNDEF; surname:
UNDEF).
    "23529000035676,Acevedo,MPL,S,""COLOR:Red,FAMILY:John Doe,SHOW_BCODE:1"""


With ”:

Also not OK, doesn't recognize the patron attributes.

Error analysis

    Patron attribute COLOR is invalid for patron with borrowernumber 19.
    Patron attribute COLOR is invalid for patron with borrowernumber 5.


>From there I actually converted the data in Excel to .csv; I followed these
steps: Data –> Get External Data –> From Text/CSV; selected the .csv file and
imported.
Excel then adds a column for each new patron_attributes value (column names are
left empty).
The fix I did here was just to put all the patron_attributes data in the first
patron_attributes column and then delete the two unnecessary new columns.
That unfortunately didn't work at all.

Error analysis

    Line 2 could not be parsed!
    2,3529E+13;Acevedo;MPL;S;"""COLOR:Red,FAMILY:John Doe,SHOW_BCODE:1"""
    Line 2 Critical field "categorycode" missing (borrowernumber: UNDEF;
surname: UNDEF). 


At this point I stopped using Excel and just made a new .csv sample file in
Notepad++ by entering the following lines in a new file:
cardnumber,surname,branchcode,categorycode,patron_attributes
23529000035676,Acevedo,MPL,S,"COLOR:Blue,FAMILY:John Doe,SHOW_BCODE:1"
and then saving as "new2.csv" with file extension dropdown set to "All files".
That worked!
Loaded:
attribute string : COLOR:Blue,FAMILY:John Doe,SHOW_BCODE:1 
All attributes were loaded correctly.
So I was thinking the problem is if you're using Excel.

Anyway next test was adding several values to an attribute:
FAMILY:John Doe,Jane Doe
Since the manual says in this case you should wrap each field in double quotes,
that's what I did:
23529000035676,Acevedo,MPL,S,"COLOR:Blue","FAMILY:John Doe,Jane
Doe","SHOW_BCODE:1"
Not OK However - that only loaded COLOR:Blue.

Next test was just double quotes around everything:
23529000035676,Acevedo,MPL,S,"COLOR:Blue,FAMILY:John Doe,Jane Doe,SHOW_BCODE:1"
Not OK - 
Error analysis

    Patron attribute Jane Doe is invalid for patron with borrowernumber 19.

Next I tried "double wrapping"; that is, the whole string of patron_attributes
as well as each field.
23529000035676,Acevedo,MPL,S,""COLOR:Blue","FAMILY:John Doe,Jane
Doe","SHOW_BCODE:1""

Not OK - again Line 2 could not be parsed!

Next was only wrapping the field that contains a comma:
23529000035676,Acevedo,MPL,S,COLOR:Blue,"FAMILY:John Doe,Jane Doe",SHOW_BCODE:1
As expected Not OK - only loaded: COLOR:Blue

Next I tried wrapping everything in double and the problematic field that
contains a comma I wrapped in single quotes. First wrapping only the values:
23529000035676,Acevedo,MPL,S,"COLOR:Blue,FAMILY:'John Doe,Jane
Doe',SHOW_BCODE:1"
Didn't work - Patron Attribute "Jane Doe'" is invalid
And then after that wrapping the whole field in single quotes:
23529000035676,Acevedo,MPL,S,"COLOR:Blue,'FAMILY:John Doe,Jane
Doe',SHOW_BCODE:1"
Also didn't work: Patron attribute 'FAMILY is invalid
So single quotes will not work.

Well, at this point I was out of ideas.
If anybody has any ideas, feel free to comment them!

---
TL,DR as I understand it so far:
1. If on Windows: Don't use Excel, use Notepad++ (Or Editor) instead.
2. Don't try to import patron attributes that contain commas in their value
field.

-- 
You are receiving this mail because:
You are the assignee for the bug.
You are watching all bug changes.