Asterisk CDR Master.csv postgresql Perl import

Here is a little perl script to import your Master.csv asterisk log file into your cdr postgresql table. Install the package libdbd-pg-perl (on Debian/Ubuntu, name may be different for other distros) first.

import.pl

#!/usr/bin/perl -w
use strict;
use DBI; 

# this script import asterisk cdr log Master.csv into a postgresql table

my $cdr_log_file = $ARGV[0]; 
my $pg_host = $ARGV[1]; 
my $pg_db = $ARGV[2]; 
my $pg_table = $ARGV[3]; 
my $pg_user = $ARGV[4]; 
my $pg_pwd = $ARGV[5]; 


&dbformat if ($ARGV[0] eq "database_format"); 
&usage if (!$ARGV[5]); 


# Connect to database 
print "Connecting to database...\n\n"; 

my $dsn="DBI:Pg:dbname=$pg_db;host=$pg_host;port=5432";
my $dbh=DBI->connect($dsn,$pg_user,$pg_pwd);

if ($dbh) {
	print "Successfully connected to $dsn";
	open cdr_log, "<$cdr_log_file" or die "Cannot open cdr_log_file\n";
	while (<cdr_log>) { 
		$_ =~ s/\n//iog; 
		$_ =~ s/\"//iog; 
		my (@fields) = split(/\,/, $_); 
		my $insert_str = "insert into $pg_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]."\', \'3\');\n";
		print $insert_str."\n";
		my $sth = $dbh->prepare($insert_str); 
		$sth->execute(); 
		$sth->finish(); 
	} 
	$dbh->disconnect(); 
	close (cdr_log); 
}
else{
	die("Problem connecting to : $dsn\n");
}

print "\n\nEnd.\n"; 
exit; 


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

sub dbformat() { 
       print_header(); 
       print "\nimport.pl expects a table containing the following fields:\n
    calldate timestamp with time zone DEFAULT now() NOT NULL,
    clid character varying(80) DEFAULT ''::character varying NOT NULL,
    src character varying(80) DEFAULT ''::character varying NOT NULL,
    dst character varying(80) DEFAULT ''::character varying NOT NULL,
    dcontext character varying(80) DEFAULT ''::character varying NOT NULL,
    channel character varying(80) DEFAULT ''::character varying NOT NULL,
    dstchannel character varying(80) DEFAULT ''::character varying NOT NULL,
    lastapp character varying(80) DEFAULT ''::character varying NOT NULL,
    lastdata character varying(80) DEFAULT ''::character varying NOT NULL,
    duration bigint DEFAULT (0)::bigint NOT NULL,
    billsec bigint DEFAULT (0)::bigint NOT NULL,
    disposition character varying(45) DEFAULT ''::character varying NOT NULL,
    amaflags bigint DEFAULT (0)::bigint NOT NULL,
    accountcode character varying(20) DEFAULT ''::character varying NOT NULL,
    userfield character varying(255) DEFAULT ''::character varying NOT NUL
"; 
       die; 
}; 

sub print_header() { 
       print "\nimport.pl - Load Asterisk CDR datas to PGSQL database\n"; 
       print "written by Stéphane HENRY stephane.henry (=at=) heberge.net - 2007-03-20\n"; 
}