There are so many methods are available to to import CSV file into MySQL database. Here I am going to tell you how to import Asterisk CDR (Call Detail Record) file into MySQL database. To complete this task i am going to use PHP script to read CSV records and insert these records into MySQL database.
First of all we have to create the MySQL database and CDR table.
Setting up the CDR Database/Table
IMPORTANT adaptive_odbc information: The tables below will cause you problems with the calldate field. In cdr_adaptive_odbc, call setup, answer, and teardown are stored in each of three different fields; start, answer, and end, and the calldate field is not used. The “default now()” will of course auto-populate the calldate and unless you are using usegmtime, you won’t notice it until something really breaks. Additionally, “end” is a keyword to some databases (namely PostgreSQL) and the adaptive code does not quote it, causing the query to die and tearing down the entire odbc connection. The work-around for this is to alias end to calldate, which you will want to do anyway if you want to maintain the functionality of the old cdr_odbc.
Table Setup for Specific DB’s:
Postgres:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | CREATE TABLE cdr ( 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 bigint NOT NULL default '0', billsec bigint NOT NULL default '0', disposition varchar(45) NOT NULL default '', amaflags bigint NOT NULL default '0', accountcode varchar(20) NOT NULL default '', uniqueid varchar(32) NOT NULL default '', userfield varchar(255) NOT NULL default '' ); |
MySQL:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | CREATE TABLE cdr ( calldate datetime NOT NULL default '0000-00-00 00:00:00', 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 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', accountcode varchar(20) NOT NULL default '', uniqueid varchar(32) NOT NULL default '', userfield varchar(255) NOT NULL default '' ); |
Microsoft SQL:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | CREATE TABLE cdr ( [calldate] [datetime] NOT NULL , [clid] [varchar] (80) NOT NULL , [src] [varchar] (80) NOT NULL , [dst] [varchar] (80) NOT NULL , [dcontext] [varchar] (80) NOT NULL , [channel] [varchar] (80) NOT NULL , [dstchannel] [varchar] (80) NOT NULL , [lastapp] [varchar] (80) NOT NULL , [lastdata] [varchar] (80) NOT NULL , [duration] [int] NOT NULL , [billsec] [int] NOT NULL , [disposition] [varchar] (45) NOT NULL , [amaflags] [int] NOT NULL , [accountcode] [varchar] (20) NOT NULL , [uniqueid] [varchar] (32) NOT NULL , [userfield] [varchar] (255) NOT NULL ); |
DB2:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | CREATE TABLE cdr ( calldate timestamp NOT NULL default current timestamp, 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 bigint NOT NULL default 0, billsec bigint NOT NULL default 0, disposition varchar(45) NOT NULL default '', amaflags bigint NOT NULL default 0, accountcode varchar(20) NOT NULL default '', uniqueid varchar(32) NOT NULL default '', userfield varchar(255) NOT NULL default '' ); |
Now, Create a PHP script file “import_CSVCDR2MySQL.php” and copy this code in this file. This script is based on MySQL Database, so if you are using any other database then please change the database connection string and SQL syntax in this script. Otherwise it won’t work for for.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 | <?php $locale_db_host = 'localhost'; $locale_db_name = 'put_your_db_name_here'; $locale_db_login = 'put_your_db_user_here'; $locale_db_pass = 'put_your_db_password_here'; if($argc == 2) { $logfile = $argv[1]; } else { print("Usage ".$argv[0]." <filename>\n"); print("Where filename is the path to the Asterisk csv file to import (Master.csv)\n"); print("This script is safe to run multiple times on a growing log file as it only imports records that are newer than the database\n"); exit(0); } // connect to db $linkmb = mysql_connect($locale_db_host, $locale_db_login, $locale_db_pass) or die("Could not connect : " . mysql_error()); //echo "Connected successfully\n"; mysql_select_db($locale_db_name, $linkmb) or die("Could not select database $locale_db_name"); /** 1) Find the last log entry **/ // look in cdr table to see when the last entry was made. // this establishes the starting point for the asterisk data. $sql="SELECT UNIX_TIMESTAMP(calldate) as calldate". " FROM cdr". " ORDER BY calldate DESC". " LIMIT 1"; if(!($result = mysql_query($sql, $linkmb))) { print("Invalid query: " . mysql_error()."\n"); print("SQL: $sql\n"); die(); } $result_array = mysql_fetch_array($result); //$lasttimestamp = date("Y-m-d H:i:s", $result_array['voip_stamp']); $lasttimestamp = $result_array['calldate']; //** 2) Find new records in the asterisk log file. ** $rows = 0; $handle = fopen($logfile, "r"); while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { // NOTE: the fields in Master.csv can vary. This should work by default on all installations but you may have to edit the next line to match your configuration list($accountcode,$src, $dst, $dcontext, $clid, $channel, $dstchannel, $lastapp, $lastdata, $start, $answer, $end, $duration, $billsec, $disposition, $amaflags ) = $data; // 3) parse each row and add to the database if(strtotime($end) > $lasttimestamp) { // we found a new record so add it to the DB $sql = "INSERT INTO cdr (calldate, clid, src, dst, dcontext, channel, dstchannel, lastapp, lastdata, duration, billsec, disposition, amaflags, accountcode) VALUES('$end', '".mysql_real_escape_string($clid)."', '$src', '$dst', '$dcontext', '$channel', '$dstchannel', '$lastapp', '$lastdata', '$duration', '$billsec', '$disposition', '$amaflags', '$accountcode')"; if(!($result2 = mysql_query($sql, $linkmb))) { print("Invalid query: " . mysql_error()."\n"); print("SQL: $sql\n"); die(); } $rows++; } } fclose($handle); print("$rows imported\n"); ?> |
Now, You can run this script and it will import all your CDR into MySQL database.
1 2 3 | $ php import_CSVCDR2MySQL.php /usr/log/asterisk/cdr-csv/Master.csv |
If you want to run this script again and again on a specific time interval the it is better to setup cronjob to automate this task. For doing that create cronjob and use the syntax given below.
1 2 3 | $ crontab -e |
Add this line to your cronjob.
1 2 3 | 0 09-21 * * * php /root/import_CSVCDR2MySQL.php /var/log/asterisk/cdr-csv/Master.csv 2>&1 >> /root/CSVCDR2MySQL.log |
If you want to learn about cronjob use man command.
1 2 3 | $ man crontab |
Links: Asterisk CDR MySQL
