{"id":38,"date":"2009-06-04T11:54:03","date_gmt":"2009-06-04T15:54:03","guid":{"rendered":"http:\/\/ewart.homelinux.net\/?p=38"},"modified":"2010-08-14T12:52:34","modified_gmt":"2010-08-14T16:52:34","slug":"converting-the-dbtext-borrower-database","status":"publish","type":"post","link":"https:\/\/www.bobsown.net\/?p=38","title":{"rendered":"Converting the DBText Borrower Database"},"content":{"rendered":"

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.<\/p>\n

DBTextMembers is patterned after DBTextCheck.\u00a0 It can be downloaded here.<\/a> It does some formatting checks on phone numbers and dates.<\/p>\n

<\/p>\n

Conversion Steps<\/strong><\/p>\n

    \n
  1. Modify the code.\u00a0 In addition to the @check_blank array of fields, you will need to go through the code to select and format the output fields.\u00a0 See the program details below.<\/li>\n
  2. Run DBTextMember on the exported borrower file.\u00a0 The output defaults to patron.csv<\/em>.<\/li>\n
  3. Take a look at paterr.csv<\/em>.\u00a0 It will have some additional errors found in formatting the output.\u00a0 You may want to go back to DBText, fix them, and re-run step 2.<\/li>\n
  4. Go to the Tools menu in Koha and click on Import Patrons.\u00a0 It will ask you for the file to be imported.<\/li>\n
  5. This may also find some errors which you will want to fix.<\/li>\n<\/ol>\n

    Field Mapping<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    DBText<\/th>\nKoha<\/th>\nProcessing<\/th>\n<\/tr>\n
    Borrower Number<\/td>\nborrowernumber
    \ncardnumber
    \nuserid<\/td>\n    		set_field<\/td>\n<\/tr>\n
    Last Name<\/td>\nsurname<\/td>\nuse Company if blank, comma or period<\/td>\n<\/tr>\n
    First Name<\/td>\nfirstname<\/td>\nset_field<\/td>\n<\/tr>\n
    Address<\/td>\naddress<\/td>\nset_field<\/td>\n<\/tr>\n
    Company<\/td>\naddress2<\/td>\nset_field<\/td>\n<\/tr>\n
    City
    \nState
    \nCountry<\/td>\n    		city<\/td>\ninclude country if not USA or blank<\/td>\n<\/tr>\n
    Zip<\/td>\nzipcode<\/td>\nset_field use 33300 as default<\/td>\n<\/tr>\n
    EMail<\/td>\nemail<\/td>\nset_field<\/td>\n<\/tr>\n
    Phone<\/td>\nphone<\/td>\nset_phone<\/td>\n<\/tr>\n
    Cell Phone<\/td>\nmobile<\/td>\nset_phone<\/td>\n<\/tr>\n
    <\/td>\nbranchcode<\/td>\nSLA<\/td>\n<\/tr>\n
    Record Created<\/td>\ndateenrolled<\/td>\nset_date<\/td>\n<\/tr>\n
    ExpirationDate<\/td>\ndateexpiry<\/td>\nset_date<\/td>\n<\/tr>\n
    Notes<\/td>\nborrowernotes<\/td>\nset_field<\/td>\n<\/tr>\n
    Zipcode<\/td>\npassword<\/td>\nfirst 5 characters<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    Patron Attributes<\/strong><\/p>\n

    If MailingList<\/em> is set, MAIL is set to True or False as appropriate.\u00a0 PUBLISH is set similarly.<\/p>\n

    The SELECT attributes will be set with BOD, BUS, FAM, IND, CULT and PR.\u00a0 All other Select Codes<\/em> will be dropped.<\/p>\n

    Patron Category<\/strong><\/p>\n

    The patron category is set with FAM, IND, BUS or PR in that order, if one of those codes is present in Select Codes. <\/em>If none of those are present and there is an expiration date, the category is set to IND.\u00a0 If all else fails, the category is OTHER.<\/p>\n

    Program Description<\/strong><\/p>\n

    DBTextMembers is written in Perl.\u00a0 Time::Local, Getopt::Long and Date::Manip are required Perl modules.<\/p>\n

    The first part of the program reads in the first record to set up the field names and number, very much as DBTextCheck did.\u00a0 It opens two output files which default to patron.csv<\/em> and paterr.csv<\/em>.\u00a0 The name of the input file is the required parameter.\u00a0 The error file contains the borrower number, name and a list of errors for each record with errors.<\/p>\n

    The main loop reads in each record and cleans up the fields.\u00a0 The record is checked to see if it is blank.\u00a0 Then the fields are mapped as shown in the table above and the patron attributes are set.\u00a0 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.<\/p>\n

    At the end of the program, various statistics are written out to the console.<\/p>\n

    Subroutines<\/strong><\/p>\n