PERL Miscellaneous - Text::CSV and Mysql - invalid number of columns

Author

Text::CSV and Mysql - invalid number of columns

azzi.george@gmail.com

2007-02-22, 10:03 pm

Dear Perl and MySQL/Perl Gurus,

Good evening. Am having a problem loading data from a CSV file into a
test database. Below should be all of the information to duplicate my
error. Please let me know what I am doing wrong! Thanks, and await for
your resolution to my overly simple problem.

Pierre

PERL SCRIPT
---------------------------------------------------------------------------
#!/usr/local/bin/perl

use strict;
use warnings;

use Text::CSV;
use IO::File;
use Data::Dumper;
use DBI;

#========
# Globals
#========
my $csv = Text::CSV->new;

my $db_host = "x.x.x.x";
my $db_name = "test_db";
my $db_user = "user";
my $db_pass = "";
my $dbh = DBI->connect("dbi:mysql:$db_name:$db_host","$db_user");
; #or die "Cannot connect to the Mysql
database $db_name: $DBI:errstr\n";

my $sql_start = "INSERT INTO Start VALUES (?);";
my $sql_stop = "INSERT INTO Stop VALUES (?);";
my $sql_attempt = "INSERT INTO Attempt VALUES (?);";

my $sth_attempt = $dbh->prepare($sql_attempt);
my $sth_stop = $dbh->prepare($sql_stop);
my $sth_start = $dbh->prepare($sql_start);

my $fh = new IO::File "<file_csv.txt";

#===================
# Loads file into DB
#===================
while (<$fh> ) {

my $line = $_;
my $stts = $csv->parse($_);
my @data = Dumper([$csv->fields()]);

if ($line =~ /^START/) {
#$sth_start->execute(Dumper([$csv->fields()]));
$sth_start->execute(@data);
}

elsif ($line =~ /^STOP/) {
#$sth_stop->execute(Dumper([$csv->fields()]));
#$sth_stop->execute(Dumper(@data));
}
elsif ($line =~ /^ATTEMPT/) {
#$sth_attempt->execute(Dumper([$csv->fields()]));
#$sth_attempt->execute(Dumper(@data));
}

} #end of while loop

FILE CONTENTS BEING IMPORTED
----------------------------------------------------------
START,intent,jobs.,"testing,,can..this,be,a,fieldbyitself,",testing,end,
0,
START,tent,job,"can..this,be,a,field-by-itself,",testing,end,0,1
STOP,,tent,,,job,"can..this,be,a,field-by-itself,",,0,1

MySQL Database Structure
-------------------------------------------------------------------------------

mysql> desc Start;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| field1 | varchar(50) | YES | | NULL | |
| field2 | varchar(50) | YES | | NULL | |
| field3 | varchar(50) | YES | | NULL | |
| field4 | varchar(50) | YES | | NULL | |
| field5 | varchar(50) | YES | | NULL | |
| field6 | varchar(50) | YES | | NULL | |
| field7 | varchar(50) | YES | | NULL | |
| field8 | varchar(50) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+

mysql> desc Stop;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| field1 | varchar(50) | YES | | NULL | |
| field2 | varchar(50) | YES | | NULL | |
| field3 | varchar(50) | YES | | NULL | |
| field4 | varchar(50) | YES | | NULL | |
| field5 | varchar(50) | YES | | NULL | |
| field6 | varchar(50) | YES | | NULL | |
| field7 | varchar(50) | YES | | NULL | |
| field8 | varchar(50) | YES | | NULL | |
| field9 | varchar(50) | YES | | NULL | |
| field10 | varchar(50) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+

PERL ERROR
----------------------------------------------------------------------------
DBD::mysql::st execute failed: Column count doesn't match value count
at row 1 at ./dbi_file.pl line 45, <GEN0> line 1.
DBD::mysql::st execute failed: Column count doesn't match value count
at row 1 at ./dbi_file.pl line 45, <GEN0> line 2.

A. Sinan Unur

2007-02-23, 4:06 am

azzi.george@gmail.com wrote in
news:1172195948.601530.116650@p10g2000cwp.googlegroups.com:

> Dear Perl and MySQL/Perl Gurus,

This is a Perl group.

....

> my $sql_start = "INSERT INTO Start VALUES (?);";
> my $sql_stop = "INSERT INTO Stop VALUES (?);";
> my $sql_attempt = "INSERT INTO Attempt VALUES (?);";

All these statements have exactly one placeholder. That is, you are
promising to insert one value for each row.

> my $sth_attempt = $dbh->prepare($sql_attempt);
> my $sth_stop = $dbh->prepare($sql_stop);
> my $sth_start = $dbh->prepare($sql_start);

You don't like error checking?

> my $fh = new IO::File "<file_csv.txt";

open my $fh, '<', 'file_csv.txt'
or die "Cannot open 'file_csv.txt': $!";

> if ($line =~ /^START/) {
> #$sth_start->execute(Dumper([$csv->fields()]));
> $sth_start->execute(@data);
> }

Don't include commented out sections in posted code.

You are trying to insert more than one value when you promised to insert
only one value.

> PERL ERROR
> ----------------------------------------------------------------------
-
> ----- DBD::mysql::st execute failed: Column count doesn't match value
> count at row 1 at ./dbi_file.pl line 45, <GEN0> line 1.
> DBD::mysql::st execute failed: Column count doesn't match value count
> at row 1 at ./dbi_file.pl line 45, <GEN0> line 2.
>

And you are being told exactly what you did.

Sinan

Mumia W.

2007-02-23, 4:06 am

On 02/22/2007 07:59 PM, azzi.george@gmail.com wrote:
> Dear Perl and MySQL/Perl Gurus,
>
> Good evening. Am having a problem loading data from a CSV file into a
> test database. [...]
>
> my $sql_start = "INSERT INTO Start VALUES (?);";

You need as many question marks as you have values to insert. If you
have three values to insert, you need three question marks:

.... VALUES (?, ?, ?);

HTH

> [...]
> DBD::mysql::st execute failed: Column count doesn't match value count
> at row 1 at ./dbi_file.pl line 45, <GEN0> line 1.
> [...]

--
Windows Vista and your freedom in conflict:
http://www.regdeveloper.co.uk/2006/..._eula_analysis/

azzi.george@gmail.com

2007-02-23, 7:07 pm

On Feb 23, 12:46 am, "Mumia W." <paduille.4060.mumia.w
+nos...@earthlink.net> wrote:
> On 02/22/2007 07:59 PM, azzi.geo...@gmail.com wrote:
>
>
>
>
> You need as many question marks as you have values to insert. If you
> have three values to insert, you need three question marks:
>
> ... VALUES (?, ?, ?);
>
> HTH
>
>
> --
> Windows Vista and your freedom in conflict:http://www.regdeveloper.co.uk/2006/..._eula_analysis/

Sorry I put in the commented out test lines, and didn't want to
include all of the checks to make the script as small as possible.
Either way, appreciate the help. Thanks.

Sponsored Links