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_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",""

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.