in reply to perl mysql - INSERT INTO, 157 columns

my $sql = q!LOAD DATA INFILE 'c:/temp/data.csv' INTO TABLE test3
            FIELDS TERMINATED BY ','
            OPTIONALLY ENCLOSED BY '"'
            LINES TERMINATED BY '\r\n'!;
            
$dbh->do($sql);

[download]
poj

Replies are listed 'Best First'.
Re^2: perl mysql - INSERT INTO, 157 columns
by ravi45722 (Pilgrim) on Jul 31, 2015 at 06:44 UTC
    My file is presented in /root/Download/backup.csv 
    open (DATA,"<database.csv");
while(my $line = <DATA>)
{
    chomp $line;
    @array=split(',',$line);
        my $sth = $dbh->prepare("insert into copy values ('$array[0]',
+$array[1],'$array[2]','$array[3]','$array[4]',$array[5],'$array[6]')"
+);
    $sth->execute() or die $DBI::errstr;
    $sth->finish();
}

    [download]
    How can i edit as per you suggest. I am very beginner to this. Please edit this program as per your sysntax.
[reply]
[d/l]

      Please use SQL placeholders. It's the only sensible way to construct an SQL statement for arbitrary data and frees you from worrying about contained quotes, newlines and other potentially problematic data. Also consider using Text::CSV_XS for reading in your CSV data instead of manually splitting the data. This frees you up from worrying about quotes, newlines, commas and other problematic input in your data.

      open (DATA,"<database.csv");
while(my $line = <DATA>)
{
    chomp $line;
    @array=split(',',$line);
    my $sth = $dbh->prepare("insert into copy values (?,?,?,?,?,?,?)")
+;
    $sth->execute(@array) or die $DBI::errstr;
    $sth->finish();
}

      [download]

      To help us help you better, please post the exact error message you get, and also the relevant input data that causes this.

[reply]
[d/l]

In Section Seekers of Perl Wisdom