IMPORT EXISTING ASTERISK CDR CSV INTO POSTGRES

I created this script so that I could use the Asterisk CDR Stat project (http://www.areski.net/asterisk-stat-v2/about.php).

I didn’t want to change asterisk’s default cdr to go directly into a postgres database. Instead I wanted to copy over the csv file and look at it using the asterisk stat project, which needs to view the cdr info from a database.

Also, if you already have an existing cdr csv file and want to import it into postgres and then change asterisk to use the database from that point forward for cdr, you can do that.

This script will take a csv file and import all the information into a postgresql database, and render it available for correct asterisk stat viewing.

Please feel free to email at [email protected] if you need any assistance with the script or have any contributions to it.

Note the postgresql table schema used was taken from the asterisk source, located in /usr/src/asterisk/contrib/scripts/ and slightly modified.
The only thing I changed was the data type for amaflags, on my machine amaflags come in as DESCRIPTION, which is not an integer, so I changed it to a VARCHAR(45), feel free to change that line if your amaflag is an integer (use one of the existing integer lines).

Here is a copy of the schema:

CREATE TABLE cdr (
AcctId BIGSERIAL PRIMARY KEY,
calldate TIMESTAMP with time zone NOT NULL DEFAULT now(),
clid VARCHAR(80) NOT NULL default ”,
src VARCHAR(80) NOT NULL default ”,
dst VARCHAR(80) NOT NULL default ”,
dcontext 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 ”,
duration INTEGER NOT NULL default ‘0’,
billsec INTEGER NOT NULL default ‘0’,
disposition VARCHAR(45) NOT NULL default ”,
amaflags VARCHAR(45) NOT NULL default ”,
accountcode VARCHAR(20) NOT NULL default ”,
uniqueid VARCHAR(32) NOT NULL default ”,
userfield VARCHAR(255) NOT NULL default ”
);

The script for PostgreSQL can be downloaded from http://bit.ly/18gRsuI