Asterisk CDR csv mysql Perl import

Trying the PHP importing script available at this site gave me some errors
about libmm and libmhash, so I decided to give myself an hour and write
a quick and dirty Perl script to make the job for me. I post it here, as is, in
the hope it will help others who would prefer using Perl. Its ugly, but
works for me. Give it a try with no arguments to have usage information.
Drop me a line if you find it useful or get in trouble.

Note that this script doesn’t care if there are old records in the database.
It is better to run it in an empty table.

load_cdr_data.pl

#!/usr/bin/perl

use DBI();

$cdr_log_file = $ARGV[0];
$mysql_host = $ARGV[1];
$mysql_db = $ARGV[2];
$mysql_table = $ARGV[3];
$mysql_user = $ARGV[4];
$mysql_pwd = $ARGV[5];


&dbformat if $ARGV[0] eq "database_format";
&usage unless $ARGV[5];

open cdr_log, "<$cdr_log_file" || die "Cannot open cdr_lod_file\n";
        @cdr = <cdr_log>;
close (cdr_log);

 # Connect to database
print "Connecting to database...\n\n";
my $dbh = DBI->connect("DBI:mysql:database=$mysql_db;host=$mysql_host","$mysql_user","$mysql_pwd",{'RaiseError' => 1});

foreach (@cdr){
        $_ =~ s/\n//iog;
        $_ =~ s/\"//iog;
        (@fields) = split(/\,/, $_);
        $insert_str = "insert into $mysql_table (src, dst, dcontext, clid, channel, dstchannel, lastapp, lastdata, calldate, duration, billsec, disposition, amaflags) values (\"".$fields[1]."\", \"".$fields[2]."\", \"".$fields[3]."\", \"".$fields[4]."\", \"".$fields[5]."\", \"".$fields[6]."\", \"".$fields[7]."\", \"".$fields[8]."\", \"".$fields[9]."\", \"".$fields[12]."\", \"".$fields[13]."\", \"".$fields[14]."\", \"".$fields[15]."a\");\n";
        $sth = $dbh->prepare($insert_str);
        $sth->execute();
        print ".";
}

print "\n\nOK.\n";

$sth->finish();
$dbh->disconnect();
exit;


sub usage() {
        print_header();
        print "\nUsage: perl load_cdr_data.pl <cdr_log_file> <mysql_hostname> <database> <table> <username> <password>";
        print "\n\nTo see the expected database format run perl laod_cdr_data.pl database_format\n\nEnd.\n";
        die;
};

sub dbformat() {
        print_header();
        print "\nload_cdr_data exprects a table contaming the following fields:\n
   uniqueid varchar(32) NOT NULL default '',
   userfield varchar(255) NOT NULL default '',
   accountcode varchar(20) NOT NULL default '',
   src varchar(80) NOT NULL default '',
   dst varchar(80) NOT NULL default '',
   dcontext varchar(80) NOT NULL default '',
   clid varchar(80) NOT NULL default '',
   channel varchar(80) NOT NULL default '',
   dstchannel varchar(80) NOT NULL default '',
   lastapp varchar(80) NOT NULL default '',
   lastdata varchar(80) NOT NULL default '',
   calldate datetime NOT NULL default '0000-00-00 00:00:00',
   duration int(11) NOT NULL default '0',
   billsec int(11) NOT NULL default '0',
   disposition varchar(45) NOT NULL default '',
   amaflags int(11) NOT NULL default '0'

";
        die;
};

sub print_header() {
        print "\nload_cdr_data.pl - Load Asterisk CDR data to MySQL database\n";
        print "written by andre.correa(at)pobox.com - 06/01/2005\n";
}

The code below allows commas in most fields, preserves the quotes inside of all fields.

#!/usr/bin/perl

# Copyright (c) 2010 by Precise Networks, Inc.  All rights reserved.  http://precisenetworksinc.com

#   This program is free software: you can redistribute it and/or modify
#   it under the terms of the GNU General Public License as published by
#   the Free Software Foundation, either version 3 of the License, or
#   (at your option) any later version.
#
#   This program 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 this program.  If not, see <http://www.gnu.org/licenses/>.

# 2010-01-30 by Patrick Bennett Hagen: original version.

use strict;
use DBI();

# main
    !$ARGV[6] && die "Required arguments: <cdr_log_file> <mysql_hostname> <database> <table> <username> <password> <preview|import>\n";

    open(cdr_csv, "<$ARGV[0]" || die "Unable to open $ARGV[0]\n");
    my @cdr = <cdr_csv>;
    close(cdr_csv);

    my $dbh = DBI->connect("DBI:mysql:database=$ARGV[2];host=$ARGV[1]","$ARGV[4]","$ARGV[5]");
    my $sth = undef;
    my ($accountcode, $src, $dst, $dcontext, $clid, $channel, $dstchannel, $lastapp, $lastdata, $start, $answer, $end, $duration, $billsec, $disposition, $amastr, $amaflags, $uniqueid, $userfield) = undef;

    foreach (@cdr) {
        ($accountcode, $_) = getNextField($_);
        ($src        , $_) = getNextField($_);
        ($dst        , $_) = getNextField($_);
        ($dcontext   , $_) = getNextField($_);
        ($clid       , $_) = getNextField($_);
        $clid =~ s/\\\"\\\"/\\\"/g;
        ($channel    , $_) = getNextField($_);
        ($dstchannel , $_) = getNextField($_);
        ($lastapp    , $_) = getNextField($_);
        ($lastdata   , $_) = getNextField($_);
        ($start      , $_) = getNextField($_);
        ($answer     , $_) = getNextField($_);
        ($end        , $_) = getNextField($_, ",");
        ($duration   , $_) = getNextField($_, ",");
        ($billsec    , $_) = getNextField($_, ",");
        ($disposition, $_) = getNextField($_);
        ($amastr     , $_) = getNextField($_);
        $amastr eq "'DEFAULT'"       and $amaflags="'0'";
        $amastr eq "'OMIT'"          and $amaflags="'1'";
        $amastr eq "'BILLING'"       and $amaflags="'2'";
        $amastr eq "'DOCUMENTATION'" and $amaflags="'3'";
        ($uniqueid   , $_) = getNextField($_);
        ($userfield  , $_) = getNextField($_, "\n");

        my $s = "insert into $ARGV[3] (accountcode, src, dst, dcontext, clid, channel, dstchannel, lastapp, lastdata, calldate, duration, billsec, disposition, amaflags, uniqueid, userfield) values ($accountcode, $src, $dst, $dcontext, $clid, $channel, $dstchannel, $lastapp, $lastdata, $start, $duration, $billsec, $disposition, $amaflags, $uniqueid, $userfield)";
        $sth = $dbh->prepare($s);
        if ($ARGV[6] eq "import") {
            $sth->execute();
            print ".";
        }
        $ARGV[6] eq "preview" and print "$s\n\n";
    }
    $dbh->disconnect(); print "done.\n";
# main

sub getNextField {
    my $s = shift;
    my $delimiter = shift;
    $delimiter or $delimiter = "\",\"";
    my $endPos = index $s, "$delimiter";
    $delimiter eq ","     and $endPos++;
    $delimiter eq "\n"    and $endPos++;
    $delimiter eq "\",\"" and $endPos+=2;
    my $field = substr $s, 0, $endPos, "";
    $field = substr $field, 0, -1;
    (substr $field, -1) eq "\""   and $field = substr $field, 0 - 1;
    (substr $field, 0, 1) eq "\"" and $field = substr $field, 1;
    $field = $dbh->quote($field);
    return $field, $s;
} # getNextField