Text::CSV and Mysql

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

  1. Guest

    Dear Perl Programmers,

    Good morning. 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 as unsure as to how to
    remove the beginning [ and ] brackets. Thanks, and await for your
    tested resolution.

    Pierre

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

    use strict;
    use warnings;

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

    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 $file = "file_csv.txt";
    my $fh = new IO::File "<$file" || die "Could not open $file: $! \n";

    while (<$fh>) {

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

    if ($line =~ /^START/) {
    my $sql_start = "INSERT INTO Start VALUES (@data);";
    my $sth_start = $dbh->prepare($sql_start);
    $sth_start->execute();
    }
    else ($line =~ /^STOP/) {
    my $sql_stop = "INSERT INTO Stop VALUES (@data);";
    my $sth_stop = $dbh->prepare($sql_stop);
    $sth_stop->execute();
    }

    } #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: You have an error in your SQL syntax;
    check the manual that corresponds to your MySQL server version for the
    right syntax to use near '[
    'START',
    'intent',
    'jobs.',
    'testing,,' at line 1 at ./dbi_test.pl line 35, <GEN0> line
    1.
    DBD::mysql::st execute failed: You have an error in your SQL syntax;
    check the manual that corresponds to your MySQL server version for the
    right syntax to use near '[
    'START',
    'tent',
    'job',
    'can..this,be,' at line 1 at ./dbi_test.pl line 35, <GEN0>
    line 2.
    DBD::mysql::st execute failed: You have an error in your SQL syntax;
    check the manual that corresponds to your MySQL server version for the
    right syntax to use near '[
    'STOP',
    '',
    'tent',
    '',
    '',
    ' at line 1 at ./dbi_test.pl line 40, <GEN0> line 3.
    callroute:/var/home/collect/scripts >
    , Feb 23, 2007
    #1
    1. Advertising

  2. Guest

    On Feb 23, 10:14 am, wrote:
    > Thanks, and await for your tested resolution.


    Oh, not only are we supposed to fix the problems, but we're supposed
    to test it as well. Would you like a mocha latte while we're working
    on it?

    Tell ya what - how 'bout I just point out a couple of obvious problems
    and let you have another swing at it.


    > my @data = Dumper([$csv->fields()]);


    You want to know where those [] are coming from? Well, that's
    where...

    > my $sql_start = "INSERT INTO Start VALUES (@data);";


    No trailing semicolon on DBI statements. This ain't a SQL shell.

    > else ($line =~ /^STOP/) {


    ???? Did you mean elsif (...

    > my $sql_stop = "INSERT INTO Stop VALUES (@data);";


    Why don't you print that variable out for debugging purposes? You
    will immediately see the problem caused by trying to load Dumper()
    output straight into the database. This is your biggest problem.


    --
    The best way to get a good answer is to ask a good question.
    David Filmer (http://DavidFilmer.com)
    , Feb 23, 2007
    #2
    1. Advertising

  3. Guest

    On Feb 23, 1:58 pm, wrote:
    > On Feb 23, 10:14 am, wrote:
    >
    > > Thanks, and await for your tested resolution.

    >
    > Oh, not only are we supposed to fix the problems, but we're supposed
    > to test it as well. Would you like a mocha latte while we're working
    > on it?
    >
    > Tell ya what - how 'bout I just point out a couple of obvious problems
    > and let you have another swing at it.
    >
    > > my @data = Dumper([$csv->fields()]);

    >
    > You want to know where those [] are coming from? Well, that's
    > where...
    >
    > > my $sql_start = "INSERT INTO Start VALUES (@data);";

    >
    > No trailing semicolon on DBI statements. This ain't a SQL shell.
    >
    > > else ($line =~ /^STOP/) {

    >
    > ???? Did you mean elsif (...
    >
    > > my $sql_stop = "INSERT INTO Stop VALUES (@data);";

    >
    > Why don't you print that variable out for debugging purposes? You
    > will immediately see the problem caused by trying to load Dumper()
    > output straight into the database. This is your biggest problem.
    >
    > --
    > The best way to get a good answer is to ask a good question.
    > David Filmer (http://DavidFilmer.com)


    Wanted to give as much information as possible. And greatly appreciate
    you clearing up that my issue was that the [ ] were put into the > >
    my @data = Dumper([$csv->fields()]); statement! This helped a
    tremendous amount as had used someone else's code for that aspect of
    it.

    Pierre
    , Feb 23, 2007
    #3
    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. Michal Mikolajczyk
    Replies:
    0
    Views:
    648
    Michal Mikolajczyk
    Feb 13, 2004
  2. Tintin92
    Replies:
    1
    Views:
    1,703
    Andrew Thompson
    Feb 14, 2007
  3. jliu66
    Replies:
    0
    Views:
    506
    jliu66
    Oct 19, 2007
  4. Replies:
    3
    Views:
    153
  5. Sacha Rook

    csv read clean up and write out to csv

    Sacha Rook, Nov 2, 2012, in forum: Python
    Replies:
    2
    Views:
    221
    Hans Mulder
    Nov 2, 2012
Loading...

Share This Page