How to insert data into a table using perl ?

[omakhileshchand]

Sir,

I have a text file that contain some fields,the fields are given below:-
"","7041","8320 ","hunt_incomin g","7041","S IP/ccm102-00000001","","R ead","REPFLOW,/usr/WINAST/PRAMERICA/ENGLISH/PREVIOUSMENU,1, ,1,5","2011-05-03 03:21:57","2011-05-03 03:21:59","2011-05-03 03:22:36",39,37 ,"ANSWERED","DO CUMENTATION","1 304392917.1",""

I want to insert that fields into a table using perl.I'm getting problem with particular fields "REPFLOW,/usr/WINAST/PRAMERICA/ENGLISH/PREVIOUSMENU,1, ,1,5" that fields break up into five part automatically,I want store that complete field into a specified column.

Code is given below:-

#!/usr/bin/perl

use DBI;

print "Connecting to database...\n\n";

my $DSN = q/dbi:ODBC:SQLSERVER/;
my $uid = q/migration/;
my $pwd = q/mig0106@s/;
my $dbh = DBI->connect($DSN,$uid,$pwd) or die "Coudn't Connect SQL";
my $sth = undef;

my $cdr_log_file = "/tmp/Perl/test";

&dbformat if ($cdr_log_file eq "database_format");
&usage if (!$pwd);
&getNextField($_);

if ($dbh)
                {
        print "Successfully connected to $DSN";
        open cdr_log, "<$cdr_log_file" or die "Cannot open cdr_log_file\n";
        while (<cdr_log>)
                        {
                 $_ =~ s/\n//iog;
		 $_ =~ s/\"//iog;
 
                my (@fields) = split(/\,/, $_);

                foreach (@fields) {
                                 ($fields[0]   , $_) ;
                                 ($fields[1]   , $_) ;
                                 ($fields[2]   , $_) ;
                                 ($fields[3]   , $_) ;
                                 ($fields[4]   , $_) ;
                                 ($fields[5]   , $_) ;
                                 ($fields[6]   , $_) ;
                                 ($fields[7]   , $_) ;
                                 ($fields[8]  , $_); #= getNextField($_);
                                 ($fields[9]   , $_) ;
                                 ($fields[10]  , $_) ;
                                 ($fields[11]  , $_) ;
                                 ($fields[12]  , $_) ;
                                 ($fields[13]  , $_) ;
                                 ($fields[14]  , $_) ;
                                 ($fields[15]  , $_) ;
                                 ($fields[16]  , $_) ;
                                 ($fields[17]  , $_) ;

                                 my $s = "insert into cdr (accountcode,src,dst,dcontext,clid, channel, dstchannel, lastapp, lastdata,start,answer,\[end\], duration, billsec, disposition, amaflags,userfield,uniqueid) values (\'".$fields[0]."\',\'".$fields[1]."\', \'".$fields[2]."\', \'".$fields[3]."\', \'".$fields[4]."\', \'".$fields[5]."\', \'".$fields[6]."\',\'".$fields[7]."\',\'".$fields[8]."\',\'".$fields[9]."\' ,\'".$fields[10]."\', \'".$fields[11]."\', \'".$fields[12]."\', \'".$fields[13]."\', \'".$fields[14]."\',\'".$fields[15]."\',\'".$fields[16]."\',\'".$fields[17]."\');\n";
        $sth = $dbh->prepare($s);
            $sth->execute();

                                 }

=cut
                        sub getNextField {
                                            my $s = shift;
                                            my $delimiter = shift;
                                            $delimiter or $delimiter = "\",\"";
                                            my $endPos = index $s, "$delimiter";
                                            $delimiter eq ","     and $endPos++;
                                            $delimiter eq "\n"    and $endPos++;
                                            $delimiter eq "\",\"" and $endPos+=2;
                                            my $field = substr $s, 0, $endPos, "";
                                            $field = substr $field, 0, -1;
                                            (substr $field, -1) eq "\""   and $field = substr $field, 0 - 1;
                                            (substr $field, 0, 1) eq "\"" and $field = substr $field, 1;
                                            $field = $dbh->quote($field);
                                            return $field, $s;
                                        }

=cut
                        }
                }

else    {
                die("Problem connecting to : $DSN\n");
        }

print "\n\nEnd.\n";
exit;


sub usage()     {
                print_header();
                print "\nUsage:  <cdr_log_file> <sql_hostname> <Database> <Table> <Username> <Password>";
                print "\n\nTo see the Expected database format Database_format\n\nEnd.\n";
                die;
                };

sub dbformat()
                {
                print_header();
                print "\n Expects a table containing the following fields:\n
		accountcode varchar(80) DEFAULT ''::character varying NOT NULL,
		src varchar(80) DEFAULT ''::character varying NOT NULL,
		dst varchar(80) DEFAULT ''::character varying NOT NULL,
                dcontext varchar(80) DEFAULT ''::character varying NOT NULL,
		clid varchar(80) DEFAULT ''::character varying NOT NULL,
                channel varchar(80) DEFAULT ''::character varying NOT NULL,
                dstchannel varchar(80) DEFAULT ''::character varying NOT NULL,
                lastapp varchar(80) DEFAULT ''::character varying NOT NULL,
                lastdata varchar(80) DEFAULT ''::character varying NOT NULL,
		start datetime with time zone DEFAULT now() NOT NULL, 
		answer datetime with time zone DEFAULT now() NOT NULL,
		[end] datetime with time zone DEFAULT now() NOT NULL,
                duration int DEFAULT (0)::bigint NOT NULL,
                billsec int DEFAULT (0)::bigint NOT NULL,
                disposition varchar(80) DEFAULT ''::character varying NOT NULL,
                amaflags varchar(80) DEFAULT ''::character varying NOT NULL,
                accountcode character varying(20) DEFAULT ''::character varying NOT NULL,
		unquie character varying(255) DEFAULT ''::character varying NOT NUL,
                userfield character varying(255) DEFAULT ''::character varying NOT NULL
                ";
                die;
                };

sub print_header()
                {
                print "\nimport.pl - Load Asterisk CDR datas to SQL Server database\n";
                print "\n==============Storing data into SQL Server================\n";
                };

Please help me....

[RonB]

Your script has a number of problems, but to fix the one you're asking about you need to use the Text::CSV or Text::CSV_XS module. The XS module is faster, so that's what I'd use.

#!/usr/bin/perl

use strict;
use warnings;
use Text::CSV_XS;
use Data::Dumper;

my $str = q("","7041","8320","hunt_incoming","7041","SIP/ccm102-00000001","","Read","REPFLOW,/usr/WINAST/PRAMERICA/ENGLISH/PREVIOUSMENU,1,,1,5","2011-05-03 03:21:57","2011-05-03 03:21:59","2011-05-03 03:22:36",39,37,"ANSWERED","DOCUMENTATION","130439 2917.1","");

my $csv = Text::CSV_XS->new ( { binary => 1 } )
        or die "Cannot use CSV: ".Text::CSV_XS->error_diag ();

$csv->parse($str);
my @fields = $csv->fields;

print Dumper \@fields;

Outputs:

$VAR1 = [
          '',
          '7041',
          '8320',
          'hunt_incoming',
          '7041',
          'SIP/ccm102-00000001',
          '',
          'Read',
          'REPFLOW,/usr/WINAST/PRAMERICA/ENGLISH/PREVIOUSMENU,1,,1,5',
          '2011-05-03 03:21:57',
          '2011-05-03 03:21:59',
          '2011-05-03 03:22:36',
          '39',
          '37',
          'ANSWERED',
          'DOCUMENTATION',
          '130439 2917.1',
          ''
        ];

[omakhileshchand]

thanks a lot RonB sir ...

[nileshkirve]

I have same issue while importing data into the database. The issue was resolved by using Test::CSV module. Thank you so much Omakilesh & RonB.