The DBText borrower database needs to be converted into a comma delimited file in the proper format to be loaded into Koha via Import Patrons in the Tools menu. Most of the fields can be mapped into the corresponding Koha fields. The branch code and an initial password need to be added. We have added a number of fields to DBText that will be needed by Koha.
DBTextMembers is patterned after DBTextCheck. It can be downloaded here. It does some formatting checks on phone numbers and dates.
Conversion Steps
- Modify the code. In addition to the @check_blank array of fields, you will need to go through the code to select and format the output fields. See the program details below.
- Run DBTextMember on the exported borrower file. The output defaults to patron.csv.
- Take a look at paterr.csv. It will have some additional errors found in formatting the output. You may want to go back to DBText, fix them, and re-run step 2.
- Go to the Tools menu in Koha and click on Import Patrons. It will ask you for the file to be imported.
- This may also find some errors which you will want to fix.
Field Mapping
| DBText | Koha | Processing |
|---|---|---|
| Borrower Number | borrowernumber cardnumber userid |
set_field |
| Last Name | surname | use Company if blank, comma or period |
| First Name | firstname | set_field |
| Address | address | set_field |
| Company | address2 | set_field |
| City State Country |
city | include country if not USA or blank |
| Zip | zipcode | set_field use 33300 as default |
| set_field | ||
| Phone | phone | set_phone |
| Cell Phone | mobile | set_phone |
| branchcode | SLA | |
| Record Created | dateenrolled | set_date |
| ExpirationDate | dateexpiry | set_date |
| Notes | borrowernotes | set_field |
| Zipcode | password | first 5 characters |
Patron Attributes
If MailingList is set, MAIL is set to True or False as appropriate. PUBLISH is set similarly.
The SELECT attributes will be set with BOD, BUS, FAM, IND, CULT and PR. All other Select Codes will be dropped.
Patron Category
The patron category is set with FAM, IND, BUS or PR in that order, if one of those codes is present in Select Codes. If none of those are present and there is an expiration date, the category is set to IND. If all else fails, the category is OTHER.
Program Description
DBTextMembers is written in Perl. Time::Local, Getopt::Long and Date::Manip are required Perl modules.
The first part of the program reads in the first record to set up the field names and number, very much as DBTextCheck did. It opens two output files which default to patron.csv and paterr.csv. The name of the input file is the required parameter. The error file contains the borrower number, name and a list of errors for each record with errors.
The main loop reads in each record and cleans up the fields. The record is checked to see if it is blank. Then the fields are mapped as shown in the table above and the patron attributes are set. The patron category is set last because it depends on some of the previous fields. The record is formatted as comma delimited values and writen out.
At the end of the program, various statistics are written out to the console.
Subroutines
- set_phone: This routine looks for 3 digits, 3 digits, 4 digits followed by anything. The digits may be separated by any non-digits. So 954-123-4567 ext 123 or 9541234567 would be valid phone numbers. If it can’t find that pattern, it looks for 3 digits and 4 digits followed by anything. In that case, it has to guess at the area code. If the zip code starts with 334, it is in Palm Beach County and should have 561 as the area code. A zip of 334.. is Miami-Dade county with area code 305. Anything else is 954. We hope that any member outside the three county area has an area code.
Two parameters are required: the input field name and the output field name.
- set_date: This routine tries to format the date to mm/dd/yyyy with leading zeros. One of the errors that occurs is having the day of the month set to 31 in a month that has less than 31 days. In that case, the day of the month is set to 28. If the date is still invalid or missing, it is set to the default date, $dfltDate = ’02/05/2002′. The default is the date that we started using DBText. It is necessary to specify dates rather than let Koha assign one.
Two parameters are required: the input field name and the output field name.
- set_field: This routine gets the input field. Single quotes (‘) are doubled (” that’s 2 characters) to conform with comma-separated-values standards. If the field contains commas or double quotes (” that’s one character), it is enclosed with double quotes.
Two parameters are required: the input field name and the output field name. A third parameter may be used to set a default value when the input field is missing. In that case and error will be noted.
- put_error: This routine collects errors and writes them to paterr.csv. The first output field contains the borrower number. If it is different from the current borrower number, the record is written out. The second output field contains the borrower or company name. The third output field contains a list of errors.
One parameter is required: the error description, usually the name of the input field.