Asterisk import Master.csv CDR ot MySQL

asterisk.cdr.sql

USE asterisk;
GRANT ALL PRIVILEGES ON asterisk.* TO 'asterisk'@'localhost' IDENTIFIED BY 'change_me';
FLUSH PRIVILEGES;
CREATE TABLE IF NOT EXISTS cdr (
        id BIGINT(20) NOT NULL AUTO_INCREMENT,
        accountcode VARCHAR(30),
        src VARCHAR(64),
        dst VARCHAR(64),
        dcontext VARCHAR(32),
        clid VARCHAR(64),
        channel VARCHAR(32),
        dstchannel VARCHAR(32),
        lastapp VARCHAR(32),
        lastdata VARCHAR(256),
        calldate TIMESTAMP NOT NULL,
        answerdate TIMESTAMP NOT NULL,
        hangupdate TIMESTAMP NOT NULL,
        duration int(8) UNSIGNED DEFAULT NULL,
        billsec int(8) UNSIGNED DEFAULT NULL,
        disposition VARCHAR(32),
        amaflags VARCHAR(128),
        uniqueid VARCHAR(128),
        userfield VARCHAR(128),
        PRIMARY KEY(id),
        KEY `calldate` (`calldate`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

import_cdr.php

<?php
/*** process asterisk cdr file (Master.csv) insert usage
* values into a mysql database which is created for use
* with the Asterisk_addons cdr_addon_mysql.so
* The script will only insert NEW records so it is safe
* to run on the same log over-and-over.
*
* Author: John Lange (john@johnlange.ca)
* Date: Version 2 Released July 8, 2008
*
* Here is what the script does:
*
* Parse each row from the text log and insert it into the database after testing for a
* matching "calldate, src, duration" record in the database. Note that not all fields are
* tested.
*
* If you have a large existing database it is recommended that you add an index to the calldate
* field which will greatly speed up this import.
*
*/
/*
 * Modified by kiba, December 1, 2016 to migrate to mysqli
 * Modified by Leif Madsen, July 29, 2009 to add additional columns.
 * Original post and code by John Lange: http://www.johnlange.ca/tech-tips/asterisk/asterisk-cdr-csv-mysql-import-v20/
 */

$db_host = 'localhost';
$db_name = 'asterisk';
$db_login = 'asterisk';
$db_pass = 'change_me';

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
$mysqli = new mysqli($db_host, $db_login, $db_pass, $db_name); 
if ($mysqli->connect_errno) {
  die("Could not connect : " . $mysqli->connect_error());
}

//** 1) Find 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, $uniqueid, $userfield ) = $data;
	/** 2) Test to see if the entry is unique **/
	$sql = "SELECT calldate, src, duration".
	" FROM cdr".
	" WHERE calldate='$start'".
	" AND src='$src'".
	" AND duration='$duration'".
	" LIMIT 1";
	if(!($result = $mysqli->query($sql))) {
		print("Invalid query: " . mysqli_error()."\n");
		print("SQL: $sql\n");
		die();
	}
	if($result->num_rows === 0) { // we found a new record so add it to the DB
		// 3) insert each row in the database
        if ($answer === '') $answer = '0000-00-00 00:00:00';  // replace empty date with default value
        $sql = "INSERT INTO cdr (calldate,
                                 answerdate,
                                 hangupdate,
                                 clid,
                                 src,
                                 dst,
                                 dcontext,
                                 channel,
                                 dstchannel,
                                 lastapp,
                                 lastdata,
                                 duration,
                                 billsec,
                                 disposition,
                                 amaflags,
                                 accountcode,
                                 uniqueid,
                                 userfield) 
            VALUES('$start',
                '$answer',
                '$end',
                '".$mysqli->escape_string($clid)."',
                '$src',
                '$dst',
                '$dcontext',
                '$channel',
                '$dstchannel',
                '$lastapp',
                '$lastdata',
                '$duration',
                '$billsec',
                '$disposition',
                '$amaflags',
                '$accountcode',
                '$uniqueid', 
                '$userfield')";
		if(!($result2 = $mysqli->query($sql))) {
			print("Invalid query: " . $mysqli->error."\n");
			print("SQL: $sql\n");
			continue; // skip invalid record or you can die() here
		}
		print("Inserted: $end $src $duration\n");
		$rows++;
	} else {
		print("Not unique: $end $src $duration\n");
	}
}

$result->free();
$result2->free();
$mysqli->close();

fclose($handle);
print("$rows imported\n");
?>

import_master.csv.sql

USE asterisk
LOAD DATA LOCAL INFILE '/var/log/asterisk/cdr-csv/Master.csv'
INTO TABLE asterisk.cdr
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(
    accountcode, 
    src, 
    dst, 
    dcontext, 
    clid, 
    channel, 
    dstchannel, 
    lastapp, 
    lastdata, 
    calldate, 
    answerdate, 
    hangupdate, 
    duration, 
    billsec, 
    disposition, 
    amaflags, 
    uniqueid, 
    userfield
);

Cool, the sql sentence, load data in file, works perfect for me.