Text::CSV and Mysql - invalid number of columns

A

azzi.george

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
 
A

A. Sinan Unur

(e-mail address removed) wrote in
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
 
M

Mumia W.

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.
[...]
 
A

azzi.george

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.
[...]


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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,733
Messages
2,569,440
Members
44,832
Latest member
GlennSmall

Latest Threads

Top