[Koha] Importing barrowing information - answer
arm at hanover.ca
Tue Nov 17 06:28:50 NZDT 2009
Agnes et Bernardo,
Because I so greatly appreciated your help getting this off the ground,
I decided to write up the steps I took to import the currently borrowed
book information from my old filemaker database into issues.
First step: The only useful information I had from my Filemaker database
was the borrower's First Name and the Barcode number for the book he has
checked out. (BTW, if you are working with Linux, you can install the
30 day trial of Filemaker 10 no problem with wine to export all your
data into CSV files.) Once I exported the data, I opened up the file in
a OOCalc, fixed the formating of names, looked for different versions of
the same person etc. I saved it as a tab file, and opened it in a
simple text editor. Then I made sure to find/replace and remove any
enclosing " or ' around string data, and saved it with tab delimiters.
Second step: open mysql (mysql -uroot koha -p)
Create a tempory table that will contain data agglomerated from your old
database and tables: issues, items, and borrowers:
mysql> create table borrowtemp ( barcode int(11), firstname text,
Import your tab delimited values file into the table:
mysql> load data local infile 'borrower.tab' into table borrowtemp;
Now, update borrowers with any borrower names that have checked out
books but haven't been added to "Patrons":
mysql> insert into borrowers (firstname, categorycode, branchcode)
select distinct borrowtemp.firstname, 'FRERE' as categorycode, 'RIM' as
branchcode from borrowers inner join borrowtemp on borrowers.firstname
!= borrowtemp.firstname order by borrowtemp.firstname;
(note, 'FRERE' is a category of Patrons that must already exist, and
'RIM' is the library code for a library which must already be created)
Now that the Patrons (borrowers) is populated and have all been assigned
a borrowernumber, you can update your borrowtemp with that information:
mysql> update borrowtemp, borrowers set borrowtemp.borrowernumber =
borrowers.borrowernumber where borrowtemp.firstname = borrowers.firstname;
Now, before we can get the barcodes of the books checked out to
borrowernumber, we have to grab the itemnumber corresponding to the
barcode in items:
mysql> update borrowtemp, items set borrowtemp.itemnumber =
items.itemnumber where borrowtemp.barcode = items.barcode;
Finally, the issues table can be populated with the borrowed books:
mysql> insert into issues (borrowernumber, itemnumber, date_due,
branchcode) select borrowtemp.borrowernumber, borrowtemp.itemnumber,
date_add(CURDATE(), interval 8 month) as date_due, 'RIM' as branchcode
(As you can see, I set the date_due to eight months from the current
date, which you can change as you see fit - our old system did not have
due dates! And of course, once again 'RIM' is the Library code.)
Thanks again for your help! I hope this can help someone who finds
themselves in a similar migration situation!
On Tue, Nov 10, 2009 at 5:21 AM, Agnes Rivers-Moore <arm at hanover.ca
<mailto:arm at hanover.ca>> wrote:
I'm afraid I can't help with MySQL. This all depends so much on what
data you have in your old system, and how it is stored.
There is a summary at http://www.kohadocs.org/migrating_to_koha.html,
for a much older version of Koha but the principles and main tables are
still relevant. Many more useful notes at http://wiki.koha.org/doku.php
A diagram of the tables with the field names on
http://wiki.koha.org/doku.php?id=en:development:dbschema:3.01 and see
others on the wiki.
However, it will be up to you how to decide which piece of data from
your old system should be mapped to which field of the new one. When you
have all your borrowers and books in Koha, you will need to identify
borrower by barcode, extract the borrowernumber, identify the book,
extract the itemnumber, and insert those to the issues table with some
kind of due date.
Br. Francis Therese Krautter wrote:
Thank you for the reply...
The only information I need is to attach a patron's name, or the inverse
to a barcode to show that they currently have the book. You are talking
about the different tables - the idea seems ok conceptually, but I have
no idea how to go about doing it - as I am brand new to koha and have
very limited experience with mysql. I am just starting to get
comfortable with the marc system, though I still seem to be importing
values for something (other than state) which cause books to become
"missing" or lost, when they are not.
Maybe you could point me to the references I need in order to make some
progress with the tables thing.
Your aide is greatly appreciated!
On Mon, Nov 9, 2009 at 12:43 AM, Agnes Rivers-Moore <arm at hanover.ca
<mailto:arm at hanover.ca> <mailto:arm at hanover.ca <mailto:arm at hanover.ca>>>
This is a fairly complicated process, it depends how much of the
circulation data you hope to transfer.
You can add some of it to the 952 field in a MARC record - this
can accommodate fields such as $l (letter l) number of times
borrowed, and $q due date of item if currently checked out. If you
can tell where this information resides in your old system, you
can write a script to insert it to the MARC records and them the
bulk MARC import process will include that in the item details.
However this does not tell you who has each item. That would need
to be imported to the issues table, as Chris says, and link to the
borrowers table for the details of the person who has the item.
Issues link to the borrower table through 'borrowernumber' and to
the items table through 'itemnumber', and needs data such as
branchcode, issuingbranch, date_due, issuedate, and timestamp.
There is also oldissues, which holds the history of all completed
Br. Francis Therese Krautter wrote:
> I am still working on importing data into koha. We have a rather
> large database and I am trying to move it over, most of it has moved
> so far, but one major importation I can't figure out how do is all the
> currently borrowed books. I can't find a marc field or a koha
> variable that looks like it contains this information. There are over
> 1000 books currently checked out, I really don't want to have to do it
> by hand!
> Koha mailing list
> Koha at lists.katipo.co.nz
> This email was Anti Virus checked by Astaro Security Gateway. http://www.astaro.com
Hanover Public Library
Hanover Public Library
More information about the Koha