Damn, perl is ugly by today’s standards, but I found this old script kicking about so I decided to dust it off and make it useable. This happens alot with asterisk code posted on the various wikis.
Using the newer Text::CSV_XS library and the latest asterisk column structure I’ve made it work as below to import about 4500 rows from cdr_csv on a production system. Enjoy! Glad to be back.
Pre-requisites:
DBD::Pg Text::CSV_XS
#!/usr/bin/perl -w # Asterisk 1.6 CDR_CSV import to PGSQL # Herbert Molenda <me at herbertm.ca> - 2009-12-09 # updated from original by Stéphane HENRY stephane.henry (=at=) heberge.net - 2007-03-20 use strict; use DBI; use Text::CSV_XS; # 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]; my $csv = Text::CSV_XS->new({ quote_char => '"', always_quote => 1 }); &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\n"; open cdr_log, "< $cdr_log_file" or die "Cannot open cdr_log_file\n"; while (<cdr_log>) { if ($csv->parse($_)) { my (@fields) = $csv->fields(); my $insert_str = "insert into $pg_table (src, dst, dcontext, clid, channel, dstchannel, lastapp, lastdata, calldate, duration, billsec, disposition, uniqueid, userfield) 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[16]."\', \'".$fields[17]."\');"; print $insert_str."\n"; my $sth = $dbh->prepare($insert_str); $sth->execute(); $sth->finish(); } else { my $err = $csv->error_input; print "Failed to parse line: $err"; } } $dbh->disconnect(); close (cdr_log); } else{ die("Problem connecting to : $dsn\n"); } print "\n\nEnd.\n"; exit; sub usage() { print_header(); print "\nUsage: perl cdr_csv2pgsql.pl <cdr_log_file> <pg_hostname> <database> <table> <username> <password>\n"; die; }; sub print_header() { print "\ncdr_csv2pgsql.pl - Asterisk 1.6 CDR_CSV import to PGSQL\n"; } </password></username></table></database></pg_hostname></cdr_log_file></me>