[Koha-devel] Data Transfer to MySQL

[Larry Currie]

Good morning,

I have the following form of patron data file, of which you are seeing
the
first three records, that I would like to insert into the koha MySQL
database:
(Note that not every field tag is present in every record.)

**START
OLD_SYSTEM_ID>AAA-0012
BORROWERID>21223009001754
NAMETITLE>Mr.
FIRSTNAME>Raymond
MIDDLENAME>F.
LASTNAME>Sebastian
MASK>N
STREET1>P.O.Box 1347
CITY>Ross
COUNTY>075
STATE>CA
ZIPCODE>94957
HOMELOCATION>020100
REGISTRATIONBRANCH>020100
LANGUAGE>NU
REGISTEREDTOVOTE>N
OWNORRENTHOME>N
TELNOT_1>N
TELNOT_2>N
TELNOT_3>N
TELNOT_HLD>N
TELNOT_FINE>N
REGISTRATIONDATE>19940817
REGISTRATIONQUALIFIER6>NU
REGISTRATIONQUALIFIER7>0
REGISTRATIONQUALIFIER8>0
CARDEXPIRATIONDATE>19980529
MANUALDELINQUENT>N
VERIFIED>Y
PRIME_LIBRARY>02
PATRONCATEGORY>NC
CLAIMEDRETURNED>0
BADCHECKINS>0
OVERRIDES>0
ACTIVE>Y
FINEEXEMPT>N
FILMENABLE>N
NOTICEEXEMPT>N
TRANSACTIONSTOTAL>0
READINGCATEGORY>A
**STORE
**START
OLD_SYSTEM_ID>AAA-0692
BORROWERID>21223012679067
FIRSTNAME>ILL
LASTNAME>Bedford Inst.
COMPANY>Bedford Institute/Oceanography
MASK>N
STREET1>P.O. Box 1006
CITY>Dartmoutn, N.S.
COUNTY>075
STATE>CANADA
ZIPCODE>B2Y 4A2
HOMELOCATION>020100
REGISTRATIONBRANCH>020100
LANGUAGE>NU
REGISTEREDTOVOTE>N
OWNORRENTHOME>N
STAFFNOTE>Need to wrap in brown paper and use USPC form 2966-A.
TELNOT_1>N
TELNOT_2>N
TELNOT_3>N
TELNOT_HLD>N
TELNOT_FINE>N
REGISTRATIONDATE>19950727
REGISTRATIONQUALIFIER6>G
REGISTRATIONQUALIFIER7>0
REGISTRATIONQUALIFIER8>0
CARDEXPIRATIONDATE>20030522
MANUALDELINQUENT>N
VERIFIED>Y
PRIME_LIBRARY>02
PATRONCATEGORY>IL
CLAIMEDRETURNED>0
BADCHECKINS>0
OVERRIDES>0
ACTIVE>Y
FINEEXEMPT>N
FILMENABLE>N
NOTICEEXEMPT>N
TRANSACTIONSTOTAL>0
READINGCATEGORY>A
**STORE
**START
OLD_SYSTEM_ID>AAA-0693
BORROWERID>21223012679075
NAMETITLE>R
FIRSTNAME>Project
LASTNAME>Sea Turtle Rest.
COMPANY>Earth Island Institute
MASK>N
STREET1>300 Broadway
STREET2>Suite 28
CITY>San Francisco
COUNTY>075
STATE>CA
ZIPCODE>94933
PHONE>4154880370
HOMELOCATION>020100
REGISTRATIONBRANCH>020100
LANGUAGE>NU
REGISTEREDTOVOTE>N
OWNORRENTHOME>N
TELNOT_1>N
TELNOT_2>N
TELNOT_3>N
TELNOT_HLD>N
TELNOT_FINE>N
REGISTRATIONDATE>19950728
REGISTRATIONQUALIFIER6>NU
REGISTRATIONQUALIFIER7>0
REGISTRATIONQUALIFIER8>0
CARDEXPIRATIONDATE>19991218
MANUALDELINQUENT>N
VERIFIED>Y
PRIME_LIBRARY>02
PATRONCATEGORY>IL
CLAIMEDRETURNED>0
BADCHECKINS>0
OVERRIDES>0
ACTIVE>Y
FINEEXEMPT>N
FILMENABLE>N
NOTICEEXEMPT>N
TRANSACTIONSTOTAL>2
TRANSACTIONSYTD>2
LASTTRANSACTIONDATE>19950728
READINGCATEGORY>A
**STORE

After modifying the field length of borrowernumber to int(14), I would
like to move this data into the borrowers table as indicated below.  Can
anyone help me devise a Perl script to accomplish this transfer of data?

Thanks.

Larry Currie

mysql> describe borrowers;
+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| borrowernumber   | int(14)      |      | MUL | 0       |
|                          BORROWERID
| cardnumber       | varchar(9)   |      | MUL |         |
|                            OLD_SYSTEM_ID
| surname          | text         |      |     |         |
|                                        LASTNAME
| firstname        | text         |      |     |         |
|                                         FIRSTNAME
| title            | text         | YES  |     | NULL    |
|                                   NAMETITLE
| othernames       | text         | YES  |     | NULL    |       |
| initials         | text         |      |     |         |
|                                             MIDDLENAME
| streetaddress    | text         |      |     |         |
|                                       STREET1
| suburb           | text         | YES  |     | NULL    |
|                               ZIPCODE
| city             | text         |      |     |         |
|                                             CITY
| phone            | text         |      |     |         |
|                                          PHONE
| emailaddress     | text         | YES  |     | NULL    |
|                            STAFFNOTE
| faxnumber        | text         | YES  |     | NULL    |       |
| altstreetaddress | text         | YES  |     | NULL    |       |
| altsuburb        | text         | YES  |     | NULL    |       |
| altcity          | text         | YES  |     | NULL    |       |
| altphone         | text         | YES  |     | NULL    |       |
| dateofbirth      | date         | YES  |     | NULL    |       |
| branchcode       | varchar(4)   |      |     |         |       |
| categorycode     | char(2)      | YES  |     | NULL    |
|                       PATRONCATEGORY
| dateenrolled     | date         | YES  |     | NULL    |
|                           REGISTRATIONDATE
| gonenoaddress    | tinyint(1)   | YES  |     | NULL    |       |
| lost             | tinyint(1)   | YES  |     | NULL    |       |
| debarred         | tinyint(1)   | YES  |     | NULL    |       |
| studentnumber    | text         | YES  |     | NULL    |       |
| school           | text         | YES  |     | NULL    |
|                              COMPANY
| contactname      | text         | YES  |     | NULL    |       |
| borrowernotes    | text         | YES  |     | NULL    |       |
| guarantor        | int(11)      | YES  |     | NULL    |       |
| area             | char(2)      | YES  |     | NULL    |
|                              STATE
| ethnicity        | varchar(50)  | YES  |     | NULL    |       |
| ethnotes         | varchar(255) | YES  |     | NULL    |       |
| sex              | char(1)      | YES  |     | NULL    |       |
| expiry           | date         | YES  |     | NULL    |
|                              CARDEXPIRATIONDATE
| altnotes         | varchar(255) | YES  |     | NULL    |       |
| altrelationship  | varchar(100) | YES  |     | NULL    |       |
| streetcity       | text         | YES  |     | NULL    |       |
| phoneday         | varchar(50)  | YES  |     | NULL    |       |
| preferredcont    | char(1)      | YES  |     | NULL    |       |
| physstreet       | varchar(100) | YES  |     | NULL    |       |
| password         | varchar(30)  | YES  |     | NULL    |       |
| flags            | int(11)      | YES  |     | NULL    |       |
| userid           | varchar(30)  | YES  |     | NULL    |       |
+------------------+--------------+------+-----+---------+-------+
43 rows in set (0.00 sec)



--
Lawrence W. Currie
User Services Librarian
California Academy of Sciences
Golden Gate Park
San Francisco, CA 94118
address@hidden
(415) 750-7108
(415) 750-7106 fax
http://www.calacademy.org/research/library/