Raven - MySQL Data Import

Each year I have to import new student details into a custom web app I maintain for a school. Data is supplied as a TAB delimited text file with a single record per line. Nothing complicated, but a drag to do by hand. This script parses the file and inserts into a users table in MySQL.

#!/usr/local/bin/raven

# comment to run!
bye

# get input file
ARGS 2 get as $infile

$infile NULL =
if    'missing input file' print bye

# pull in student data, removing title row
$infile read LF split as $kids
$kids shift as $titles

# all dates must be the same, so do this outside the loop
time '%Y-%m-%d %H:%M:%S' date as $today

# somewhere to save the new data with passwords attached
new list as $out

# connect to the database
'mysql://username:password@localhost/dbname' open as mysql

# insert each student
$kids each as $kid

    # source data was tab delimited text
    $kid "\t" split into $id , $name , $handle , $email

    # build an 8 character random password
    8 each as $i
        26 choose 'a' ord +
    "%c%c%c%c%c%c%c%c" as $pass

    # clean up the handle
    $handle r/[^a-z.]/_/g as $ok_handle

    # build and escape data set.
    # note that we copy the hash before escaping. this protects
    # the original variables.
    group
        'name'     $name
        'handle'   $ok_handle
        'email'    $email
        'regdate'  $today
        'password' $pass md5
    hash copy mysql escape as $data

    # insert data set
    $data keys '`,`' join as $fields
    $data      "','" join as $values
    "INSERT INTO users (`%($fields)s`) VALUES ('%($values)s')"
    mysql query as $rs

    # output a line to reflect data changes
    [ $name $ok_handle $email $pass ] "\t" join $out push

# build a tab delimited text file for updated data
$out LF join 'students_2006_done.txt' write

# disconnect from database, not strictly required
mysql close

eof