Text::CSV and Mysql

A

azzi.george

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 >
 
U

usenet

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

azzi.george

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.

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
 

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

No members online now.

Forum statistics

Threads
473,743
Messages
2,569,478
Members
44,898
Latest member
BlairH7607

Latest Threads

Top