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