Text::CSV and Mysql - invalid number of columns

Discussion in 'Perl Misc' started by azzi.george@gmail.com, Feb 23, 2007.

  1. Guest

    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.
    , Feb 23, 2007
    #1
    1. Advertising

  2. wrote in
    news::

    > 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
    A. Sinan Unur, Feb 23, 2007
    #2
    1. Advertising

  3. Mumia W. Guest

    On 02/22/2007 07:59 PM, 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/10/29/microsoft_vista_eula_analysis/
    Mumia W., Feb 23, 2007
    #3
  4. Guest

    On Feb 23, 12:46 am, "Mumia W." <paduille.4060.mumia.w
    > wrote:
    > On 02/22/2007 07:59 PM, 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/10/29/microsoft_vista_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.
    , Feb 23, 2007
    #4
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?YmVub2l0?=

    Read CSV - string Columns - Int columns

    =?Utf-8?B?YmVub2l0?=, May 8, 2006, in forum: ASP .Net
    Replies:
    0
    Views:
    421
    =?Utf-8?B?YmVub2l0?=
    May 8, 2006
  2. Java Guy
    Replies:
    1
    Views:
    661
    Manish Pandit
    Oct 15, 2006
  3. Drew Olson
    Replies:
    2
    Views:
    314
    William James
    Dec 19, 2006
  4. Text::CSV and Mysql

    , Feb 23, 2007, in forum: Perl Misc
    Replies:
    2
    Views:
    103
  5. Java Guy
    Replies:
    1
    Views:
    284
Loading...

Share This Page