Hi, >----- Original Message ----- >From: "Christopher Wall" <[EMAIL PROTECTED]> > I have been lookin for someplace to lean how to dump all of the call > transactions into a sql database. Can anyone provide me any assistance?
If you want to import Master.csv, take a look at my mail posted on 2-Jul on this list, at the end. Best regards, Dan ----- Original Message ----- From: "Dan" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, June 02, 2004 2:30 PM Subject: Script to import Master.csv in the MySQL database - a short HowTo > 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 > _______________________________________________ Asterisk-Users mailing list [EMAIL PROTECTED] http://lists.digium.com/mailman/listinfo/asterisk-users To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
