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:umper;
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 >
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:umper;
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 >