Re: [Koha] Importing members from CSV file

19 Jul 2006

      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;
  }

}