in reply to Re: perl mysql - INSERT INTO, 157 columns
in thread perl mysql - INSERT INTO, 157 columns

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.

Replies are listed 'Best First'.
Re^3: perl mysql - INSERT INTO, 157 columns
by Corion (Patriarch) on Jul 31, 2015 at 07:06 UTC

    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