Here is a stripped down script to load a Microsoft Access database table using Win32::ODBC. This loaded about 100k rows per minute on my machine, which is WinXP, Access 2003, Active State Perl 5.8.8 and a Core 2 6600 processor at 2.4GHz.
use strict;
use warnings;
use Win32::ODBC;
$| = 1;
my $dsn = "LinkManagerTest";
my $db = new Win32::ODBC($dsn)
or die "Connect to database $dsn failed: " . Win32::ODBC::Error();
my $rows_added = 0;
my $error_code;
while (<>) {
chomp;
print STDERR "." unless $. % 100;
print STDERR " $.\n" unless $. % 5000;
my ($source, $source_link, $url, $site_name) = split /\t/;
my $insert = qq{
insert into Links (
URL,
SiteName,
Source,
SourceLink
)
values (
'$url',
'$site_name',
'$source',
'$source_link'
)
};
$error_code = $db->Sql($insert);
if ($error_code) {
print "\nSQL update failed on line $. with error code $error_code\n";
print "SQL statement:\n$insert\n\n";
print "Error:\n" . $db->Error() . "\n\n";
}
else {
$rows_added++;
}
$db->Transact('SQL_COMMIT') unless $. % 1000;
}
$db->Transact('SQL_COMMIT');
$db->Close();
print "\n";
print "Lines Read: $.\n";
print "Rows Added: $rows_added\n";
exit 0;
Leave a comment