Bug 23668 - Manual on patron import: Information on syntax for patron attributes
Summary: Manual on patron import: Information on syntax for patron attributes
Status: Needs documenting
Alias: None
Product: Koha
Classification: Unclassified
Component: Documentation (show other bugs)
Version: 19.05
Hardware: All All
: P5 - low enhancement (vote)
Assignee: Bugs List
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2019-09-23 08:39 UTC by Anke Bruns
Modified: 2024-04-08 12:39 UTC (History)
3 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact: Philip Orr
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Anke Bruns 2019-09-23 08:39:49 UTC
In the manual at
https://koha-community.org/manual//19.05/en/html/tools.html#patron-import-label
the syntax for multiple patron attributes which, to complicate things, consist of multiple words separated by blanks isn't described correctly.

It says:

<citation>
"Note

If loading patron attributes, the ‘patron_attributes’ field should contain a comma-separated list of attribute types and values.

    The attribute type code and a colon should precede each value.
        For example: “INSTID:12345,BASEBALL:Cubs”
        This field must be wrapped in quotes if multiple values are defined.
        Since values can contain spaces, additional doubled-quotes may be required:
            “INSTID:12345,BASEBALL:Cubs,””BASEBALL:White Sox””
"
</citation>

After trying this, and all other possible combinations of attributes and quotes I could come up with, I found that import only works if the attributes are entered in the most simple way, namely in single quotes. The blanks can be which, for the above example, would read:

“INSTID:12345,BASEBALL:Cubs,BASEBALL:White Sox”

I didn't test exactly this combination of attributes, but with similar ones it worked. If this can be confirmed, maybe the manual can be updated accordingly?
Comment 1 Anke Bruns 2019-10-09 13:24:39 UTC
Meanwhile I saw that the help page in the Koha staff client describes it differently and probably correctly (probably as I didn't test all the varieties described).
Comment 2 Danielle Elder 2019-10-29 01:06:26 UTC
I found that importing two patron attributes using command line patron import tool at once does not work if there is a comman in the attribute value. 

Two attributes, as the patron import tools recommends.Not OK.

"ATTR1:BLUE","ATTR2:RED" 

Loads
ATTR1:BLUE
ATTR2: 
_________________________________
Two no space, no comma OK

"ATTR1:BLUE,ATTR2:Red" 

Loads
ATTR1:BLUE
ATTR2: Red

Two with spaces OK:
"ATTR1:BLUE DOG, ATTR2:Red FISH" 

Loads
ATTR1:BLUE DOG
ATTR2: Red FISH
__________________

Two with a comma - not OK
"ATTR1:BLUE DOG,ATTR2:Red, FISH" 

Loads
ATTR1:BLUE DOG
ATTR2: Red
______________________

Two with a comma - not OK
"ATTR1:BLUE, DOG","ATTR2:Red FISH" 

Loads
ATTR1:BLUE
ATTR2: 
_____________________

"ATTR1:BLUE, Fish,ATTR2:Red Dog"
Loads
ATTR1:BLUE
ATTR2: Red Dog


_____________

One with comma does not work:

"ATTR1: Value, 1"

Loads
ATTR1: Value

The commas seem to be the problem in attributes unless separating attributes
Comment 3 Philip Orr 2024-02-27 09:26:34 UTC
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.
Comment 4 Owen Leonard 2024-02-27 12:51:29 UTC
Perhaps we should offer the option to select a different delimiter.
Comment 5 Philip Orr 2024-02-28 20:09:23 UTC
Sounds good to me. I'll add a bug for that. The section in the manual needs an update even without talking about comma-separated patron attribute values so I can do that already (and the help text in the tool itself also needs an update... I'll add a bug, maybe even a patch for that.)
Comment 6 Philip Orr 2024-04-08 12:39:42 UTC
New bug for delimiter selection:
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36544