On Tuesday 05 March 2002 21:46, Brian Volk wrote:
> Hi All,
>
> I'm having trouble loading a datafile into MySQL database via perl. I
> have a couple of scripts working but I get stuck when I try to load a
> file.... Here is what I have so far... How far off am I?? :-)
>
> $dbh->do("load data local infile $dbh->quote(/home/misc/cell_sql.csv)
> into table use_perl");
Hi, Brian: I'm working with PostgreSQL7.0.3. I am stumbling around with the
intent to write a script to load a text file into the db. There's a book,
Programming the Perl DBI by Alligator Descartes and Tim Bunce. On page 59,
there's a program example which "processes CSV files containing sales data
from one country, it fetches currency exchange rate information from a web
site and adds that to the data, it then performs a series of inserts,
selects, updates and more inserts of the data to update the database." It
then, I think, makes you toast and coffee. <grin>
It's less than 50 lines, including comments and spacing! I've packed it in
below. See if this helps:
Tom
-----------------------------------
#! /usr/bin/perl -w
use strict;
use DBI;
### Connect to db with transactions and error handling enabled
my $dbh = DBI->connect( "dbi:Oracle:archaeo", "username", "password", {
AutoCommit => 0;
RaiseError => 1;
} ) ;
### Keep count of failures. Used for program exit status
my @failed;
foreach my $country_code ( qw(US CA GB IE FR) ) {
print "Processing $country_code\n";
### Do all work for one country inside eval
eval {
### Read, parse and sanity check the data file ( e.g., using DBD::CSV)
my $data = load_sales_data_file( "$country_file.csv" );
### Add data from the Web (e.g., using the LWP modules)
add_exchange_rates( $data, $country_code, "http://exchange-rate-service.com";
);
### Perform database loading steps (e.g., using DBD::Oracle)
insert_sales_data( $dbh, $data );
update_country_summary_data( $dbh, $data );
insert_processed_files( $dbh, $country_code );
### Everything done OK for file, so commit db changes
$dbh->commit();
};
### If something went wrong . . .
if ($@) {
### Tell the user that something went wrong, and what went wrong
warn "Unable to process $country_code: $@\n";
### Undo any db changes made before the error occurred
$dbh->rollback();
### Keep track of failures
push @failed, $country_code;
}
}
$dbh->disconnect();
### Exit with useful status value for caller
exit @failed ? 1 : 0;
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
