[Asterisk-Users] Script to import Master.csv in the MySQL database - a short HowTo

[Dan]

Hi,

I hope this can help others, so this is it.
Use it at your own risk. I have test it on 3 separate systems without any
problem.
Take care to edit the following files taking into consideration your own
settings.
If you have all the CDR info in the Master.csv too, then delete all the data
from  the 'cdr' table in MySQL before running the script bellow in oder to
prevent dupplicate records.
In my example, I have the following config:
CDR database:        asteriskcdrdb
CDR table:                cdr
CVS file:              /var/log/asterisk/cdr-csv/Master.csv


1. Create a file named 'impcdr2sql' with the following content:

#!/bin/bash
# make a copy of the original Master.csv file to Master.csv.mod
cp -vf /var/log/asterisk/cdr-csv/Master.csv
/var/log/asterisk/cdr-csv/Master.csv.mod
#  format the file to comply with the MySQL data (delete '"' chars when need
it)
#  use a VIM script (nofielddelims.vim) for this purpose
ex /var/log/asterisk/cdr-csv/Master.csv.mod -c ":source
nofielddelims.vim" -c ":exit"
# run the MySQL commands from the cmd.sql file
mysql < cmd.sql

2. Enter the command to make the script executable:

chmod 755 impcdr2sql

3. Create a file named 'nofielddelims.vim' with the following content:

"
" Delete '"' chars at the beginning of the line
"
:%s/^"//
"
" Delete '"' chars at the end of the line
"
:%s/"$//
"
" Delete '"' chars near the ',' char
"
:%s/",/,/g
:%s/,"/,/g
"
" Replace '""' by '"'
"
:%s/""/"/g


4.  Create a file named 'cmd.sql' with the following content:

use asteriskcdrdb;
ALTER TABLE `cdr` ADD `tmp1` VARCHAR(30)  DEFAULT "x" NOT NULL;
ALTER TABLE `cdr` ADD `tmp2` VARCHAR(30)  DEFAULT "y" NOT NULL;
LOAD DATA INFILE '/var/log/asterisk/cdr-csv/Master.csv.mod'
replace INTO TABLE cdr
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(accountcode,src,dst,dcontext,clid,channel,dstchannel,lastapp,lastdata,calld
ate,tmp1,tmp2,duration,billsec,disposition,amaflags,uniq
ueid,userfield);
ALTER TABLE `cdr` DROP `tmp1`;
ALTER TABLE `cdr` DROP `tmp2`;


5. Keep all the files in the same directory.
All you need to do is to run the script:

./impcdr2sql

as root or as an user with full rights on the asteriskcdrdb database and cdr
table
E... voila!
All your old data from Master.csv is now in the MySQL database in the
correct format (I hope).


Please feel free to make any improovments you want.
I'm not a Linux expert.

Best regards to you all,
Dan