Koha Quick tip: Removing the dot from salutation like “Ms.” after a bulk patron import

A quick-n-dirty way to remove the trailing dot from salutation field after a bulk patron import into Koha.

Earlier today the deputy librarian at our client partner Gouri Devi Institute of Medical Sciences and Hospital (GIMSH) called us wanting to figure out how to fix a small, but crucial problem. The employee list from GIMSH’s HR department was used to create the patron bulk import CSV [1] file. All 143 records got imported without a glitch and each individual records looked perfectly OK when viewing them. However, when she tried to edit these patron records, she noticed that the salutation (e.g. Mr, Mrs, Ms etc.) would not show up in the salutation drop-down during edit! 🙁

missing salutation
Missing salutation even though salutation was entered into the database

Explanation

About 141 out of the total 143 records had a period.” after the salutation and two didn’t. These two records which did not have the period at the end of salutation were displaying the stored value correctly in the drop-down, rather than showing as being blank. As it happens, Koha uses the BorrowersTitles system preference to store the options to display. The default options are Mr|Mrs|Miss|Ms. As you can see, none of these have a period at their end. So, while the salutation field value was stored in GIMSH’s borrowers table, it was not being displayed during the edit as the value didn’t match with the values stored in BorrowersTitles syspref.

The “Fix”

***WARNING*** The following step requires you to directly execute a SQL on the Koha database. This is usually not recommended, but some times, it is required to quickly fix a problem.

Using the TRIM() string function of MySQL in tandem with the specifier trailing [2] we can take out the “.(period) at the end of the salutation from all the records in the borrowers table in the following manner:

UPDATE `borrowers` SET `title` = TRIM(TRAILING '.' FROM `title`);
salutation drop down is working now
salutation drop down is showing the stored value correctly now

References

[1] See the entry Comma separated values from Wikipedia.

[2] String Functions in MySQL 5.5 – http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_trim

[3] Sample patron data file – patron_import.csv NB. Those of you who wish to study how the HR data was prepared for import into Koha, can look here for a sample CSV file with dummy data (do not worry about privacy as the data is fake).