Hello,
Does anyone have a routine to convert from a csv file to an sql database,
that works. I'm having a hard time with it.
here is what I have so far: (almost works)
#!/usr/bin/perl -w
# <csv2sql.pl>
use DBI;
require Text::CSV_XS;
my $csv = Text::CSV_XS->new;
# bunch of my$ stuff and dbi:InterBase:db connection stuff
# dialect=3
...
open(InFile, $infile) || die "Can not open text file: $!\n";
while ($line = <InFile>){
if ($csv->parse($line)) {
$newrec = '';
my @field = $csv->fields;
$SQL = qq[INSERT INTO rdx_data VALUES (
'$field[0]', '$field[1]', '$field[2]', '$field[3]',
'$field[4]', '$field[5]', '$field[6]', '$field[7]',
'$field[8]', '$field[9]', '$field[10]', '$field[11]',
'$field[12]', '$field[13]', '$field[14]', '$field[15]',
'$field[16]', '$field[17]', '$field[18]', '$field[19]',
'$field[20]')];
$cursor = $dbh->prepare($SQL) or die print $SQL, "\n";
$cursor->execute;
}
}
$cursor->finish;
$dbh->disconnect;
close(InFile);
#EOF
most of the data gets in the Interbase DB but some is missing, and I get a
bunch of these error messages:
DBD::InterBase::st execute failed: Arithmetic overflow or division by zero
has occurred.
-arithmetic exception, numeric overflow, or string truncation
Is my punctuation bad or what? I thought it would put text into text
fields. Why would it be doing any math or string manipulation at this point?
Flamage for being a lousy, newbie, Perl wannabe hacker, and posting in this
forum, is acceptable. But any help is really appreciated.
Oh, also having a hard time with the Text::CSV_XS module not quite
following all the rules, is there a better way, or another module to use
instead (it's most up-to-date from CPAN v0.23). What I can't get it to do
is recognize a multi-line field, ie:
field1_text, field2_text, "field3_line1^M
field3_line2^M
field3_line3",field4_text,...
I even tried stripping out the doze <CR> (^M). However, I'm not so
concerned about that part, just in case someone has some insight on that.
TIA
Scott
