Hi, On Sun, 16 Jul 2006, denis_boudreau@videotron.ca wrote:

One could also import directly the CVS file into Koha MySQL database with phpMyAdmin. This is what I do when I wish to import numeros records.

Certainly, this works nicely. What we need to do is maintain a synchronicity between our current students and koha borrowers, updated automatically, adding new students as necessary and updating students information as it changed in the college management system. A nightly report is run out of the management system which produces a CSV file with many columns. The script below is run with that CSV file as parameter. New borrowers are added as necessary, existing borrowers data is updated. A further feature we needed was to group the borrowers in categorys (for borrower rules) based on their course. A two-column CSV spreadsheet of CourseCode,BorrowerCategory mappings is maintained by the librarian and read in from /path/to/course2borrowertype.csv. The student is then assigned to the category which corresponds to their course. To use the script you probably need to modify the CONSTS area, create the data files and possibly change how the while(<>) loop reads in the data if your CSV columns are different. Currently a default Example lines from course2borrowertype.csv look like: AC3D,FT BAAF-F-1,FT An example input line (we call the file students.csv) is: 6473289,18/01/1943,"Dave","Davidson","M","BAAF-F-1","7 Quality Street","Walkinstown","Dublin 12","","daved@yahoo.ouch","","166 Acacia Road","London","Paul Davidson","Paul D","","F","Business & Management Studies" Gavin #!/usr/bin/perl ########################################################################### # loadborrowers.pl # Copyright 2006 Griffith College Dublin # Author: Gavin McCullagh <gmccullagh@gmail.com> # # A script to load a bunch of students from CSV files into Koha. # # This script is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General # Public License for more details. # # You should have received a copy of the GNU General Public License along # with Koha; if not, write to the Free Software Foundation, Inc., 59 # Temple Place, Suite 330, Boston, MA 02111-1307 USA # ################################################################# # # If you don't understand all of the ? stuff in the queries, you should read # this article: http://www.stonehenge.com/merlyn/UnixReview/col58.html # # Change History: # Thu Jun 29 22:37:46 IST 2006 gmccullagh@gmail.com # Work on initial development # ################################################################# use strict; use warnings 'all'; use DBI; use Digest::MD5 qw(md5 md5_hex md5_base64); use utf8; use vars ('%course2borrowertype'); ########################## CONSTS ################################## my $DBHOST = "localhost"; my $DBPORT = "3306"; my $DBNAME = "Koha"; my $DBUSER = "imports"; my $DBPASS = "XXXXXXXXXXXXXXXX"; my $DBSOCK = "/var/run/mysqld/mysqld.sock"; my $COURSE2BORROWERMAP = "/path/to/course2borrowertype.csv"; ############################################################################ # Connect to Database my $DB = DBI->connect("DBI:mysql:mysql_socket=$DBSOCK;database=$DBNAME",$DBUSER,$DBPASS); # # Check if a borrower is in Koha # sub borrowerExists($$) { my ($DB,$studentnumber) = @_; my $id; my $query = $DB->prepare("SELECT borrowernumber FROM borrowers WHERE studentnumber=?;"); $query->execute($studentnumber) || warn "\nQuery Failed: $!\n"; $query->bind_columns(\$id); if(!$query->fetch()) { return 0; } return 1; } # # Update existing details of a borrower # (generally speaking we don't update the password as this causes confusion). # sub updateBorrower($$$$$$$$$$$$$$$$$) { my ($DB,$studentNumber,$dob,$firstname,$surname,$email,$faculty, $gender,$add11,$add12,$add13,$add1_phone,$add21,$add22,$add23_a, $add2_phone,$categorycode) = @_; my $query = $DB->prepare("UPDATE borrowers SET surname = ?, firstname = ?, emailaddress = ?, dateofbirth = ?, school = ?, sex = ?, streetaddress = ?, suburb = ?, city = ?, phone = ?, altstreetaddress = ?, altsuburb = ?, altcity = ?, altphone = ?, categorycode = ?, debarred = ? WHERE studentnumber=?;"); $query->execute($surname,$firstname, $email,$dob,$faculty,$gender, $add11,$add12,$add13,$add1_phone, $add21,$add22,$add23_a,$add2_phone, $categorycode,0,$studentNumber) || warn "$!\n"; } # # Insert a new borrower # sub insertBorrower($$$$$$$$$$$$$$$$$) { my ($DB,$studentNumber,$dob,$firstname,$surname,$email,$faculty, $gender,$add11,$add12,$add13,$add1_phone,$add21,$add22,$add23_a, $add2_phone,$categorycode) = @_; my $query = $DB->prepare("INSERT INTO borrowers (cardnumber,surname,firstname, emailaddress,dateofbirth,studentnumber, school,password,sex, streetaddress,suburb,city,phone, altstreetaddress,altsuburb,altcity,altphone, categorycode) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);"); $query->execute($studentNumber,$surname,$firstname, $email,$dob,$studentNumber, $faculty,md5_hex($dob),$gender, $add11,$add12,$add13,$add1_phone, $add21,$add22,$add23_a,$add2_phone, $categorycode) || warn "$!\n"; } sub readcourse2borrowermap($) { my %course2borrowertype = (); my ($filename) = $_[0]; open LINES, "<$filename" || die "Couldn't open $filename\n$!\n"; while(<LINES>) { chomp $_; if($_ =~ m/([A-Z0-9 -]+),([a-z0-9-]+)/i) { $course2borrowertype{$1} = $2; } } close LINES; return %course2borrowertype; } sub course2borrowercategory($) { if( defined($course2borrowertype{$_[0]}) ) { return $course2borrowertype{$_[0]}; } return "PT"; } %course2borrowertype = readcourse2borrowermap($COURSE2BORROWERMAP); # loop over input file: while(<>) { # check it's a valid line chomp $_; if($_ =~ m/^([0-9]{7}),([0-9]{2}\/[0-9]{2}\/[0-9]{4}),(.*)$/i) { my ($studentnumber,$dob,$other) = ($1,$2,$3); # because Foxpro doesn't escape the comma charater within text fields we # have to split text fields on the 3-char string "," my @textfields = split /","/, $other; my ($surname,$firstname,$gender,$coursecode,$add11,$add12,$add13,$add14, $email,$add1_phone,$add21,$add22,$add23_a,$add23_b, $add2_phone,$study_mode,$faculty) = @textfields; # strip two remaining inverted commas $surname =~ s/^"//; $faculty =~ s/"$//; # merge the last two address fields $add13 .= ", $add14" if($add14 !~ m/^\s*$/); $add23_a .= ", $add23_b" if($add23_b !~ m/^\s*$/); my $categorycode = course2borrowercategory($coursecode); if(!borrowerExists($DB,$studentnumber)) { insertBorrower($DB,$studentnumber,$dob,$firstname,$surname, $email,$faculty,$gender,$add11,$add12,$add13,$add1_phone, $add21,$add22,$add23_a,$add2_phone,$categorycode); } else { updateBorrower($DB,$studentnumber,$dob,$firstname,$surname, $email,$faculty,$gender,$add11,$add12,$add13,$add1_phone, $add21,$add22,$add23_a,$add2_phone,$categorycode); } } else { warn "skipping invalid line: $_"; next; } }