Asterisk CDR csv mysql Perl import
Created by: Andre_C10002,Last modification on Wed 22 of Jun, 2005 [16:49 UTC]
Trying the PHP importing script available at this site gave me some erros
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 usefull 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 if (!$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~/np~
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";
}
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 usefull 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 if (!$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~/np~
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";
}


Comments
333some optimizations to consider
1) it rebuilds the insert statement for every line. Instead consider using something like this before the loop:
my @fieldnames = qw/src dst dcontext clid channel dstchannel lastapp
lastdata calldate duration billsec disposition amaflags/;
my $insert = "insert into $mysql_table (".
(join ",",@fieldnames).
") values (".
(join ",", map { "?" } @fieldnames)). ")";
my $sth = $dbh->prepare($insert);
DBI will properly quote anything replacing the ? placeholders in the nsert.
2) it will use huge amounts of memory with a large cdr file
Instead of
@cdr = <cdr_log>;
close (cdr_log);
Try
while (<cdr_log>) {
chomp;
my @data = split ",";
$sth->execute(@data);
}
close cdr_log;