Importing members from CSV file
G'day subject says it all. Our school has entered the new pupils details into the school admin system. I can export a CSV file from this, which we would like to use to enter the new pupils into koha. Is there an inport feature for members? -- Regards Martin Woolley ICT Support Handsworth Grammar School Isis Astarte Diana Hecate Demeter Kali Inanna ************************************************************* This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify postmaster@bgfl.org The views expressed within this email are those of the individual, and not necessarily those of the organisation *************************************************************
Hi, On Fri, 14 Jul 2006, sysadmin wrote:
subject says it all. Our school has entered the new pupils details into the school admin system. I can export a CSV file from this, which we would like to use to enter the new pupils into koha. Is there an inport feature for members?
As I wasn't aware of a tool to do this, I have recently written a perl script to achieve this task. If there isn't a better one, I'll submit it with a GPL license. Gavin
Bonjour ! 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. Denis Boudreau ----- Message d'origine ----- De: Gavin McCullagh <gmccullagh@gmail.com> Date: Dimanche, Juillet 16, 2006 5:21 am Objet: Re: [Koha] Importing members from CSV file À: koha@lists.katipo.co.nz
Hi,
On Fri, 14 Jul 2006, sysadmin wrote:
subject says it all. Our school has entered the new pupils details into the school admin system. I can export a CSV file from this, which we would like to use to enter the new pupils into koha. Is there an inport feature for members?
As I wasn't aware of a tool to do this, I have recently written a perl script to achieve this task. If there isn't a better one, I'll submit it with a GPL license.
Gavin
_______________________________________________ Koha mailing list Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
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; } }
On Saturday 15 Jul 2006 00:49, you wrote:
I dunno if this answers your question, but I took an excel spreadsheet, edited it to match the koha borrowers table, exported it as a tab delimited file (CVS could work also) and loaded it directly into the mysql table using LOAD DATA INFILE.
Thanks for everyones replies. This worked a treat -- Regards Martin Woolley ICT Support Handsworth Grammar School Isis Astarte Diana Hecate Demeter Kali Inanna ************************************************************* This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify postmaster@bgfl.org The views expressed within this email are those of the individual, and not necessarily those of the organisation *************************************************************
participants (4)
-
denis_boudreau@videotron.ca -
Gavin McCullagh -
Martin Woolley -
sysadmin