Re: Import DB from DOS- dbase4

At 06:50 PM 12/15/01 -0300, Fernando M. Maresca wrote:
>i working around an application with Postgres as DB server, but the
>data for now comes from a DOS app. and the file format of the
>tables is dbase 4; so, can i import these tables directly into
>postgres tables automatically? Are there some kind of utility?

Conversion of data from xBase to PostgreSQL is (almost) trivial.

Just install DBI, DBD:Pg and DBD:xBase, and write a little read/write routine.
http://search.cpan.org/search?module=DBI
http://search.cpan.org/search?module=DBD::XBase
*DBD::XBase supports many dBase variants.
*I can't seem to find DBD:Pg at CPAN today??
The "dbfdump" utility (to get DBF header info) is included in DBD:XBase.
Just ftp all your dbase files into a single directory ($dir below).

#!/usr/bin/perl -w

use strict;
use File::Basename; # for basename() function
use DBI; use DBD::XBase; use DBD::Pg;
my $dir = '/home/fbax/DBFCONV';
my $dbf = DBI->connect("dbi:XBase:$dir", {RaiseError => 1} );
my $dbp = DBI->connect("dbi:Pg:dbname=fbax", "fbax", "", {RaiseError => 1} );
while (my $fname = <$dir/*.DBF>) {
&DBF2PG ($dbf, $dbp, $fname, basename(substr($fname, 0, length($fname)-4)));
}
$dbf->disconnect;
$dbp->disconnect;

sub DBF2PG {
(my $dbf, my $dbp, my $fname, my $table) = @_;
$table = lc("\"$table\"");
print "$fname - $table\n";
open (PIPE, "dbfdump --info $fname |") or die "Can't open $fname: $!";
my $sql = "CREATE TABLE $table ";
my $sep = "(";
while( <PIPE> ) {
chomp;
if (/^[0-9]+\./) { # line starts with number.
# print "$_\n";
my @stru = split; # stru contains field,type,len,dec
$sql .= $sep.' "'.lc($stru[1]).'"';
if ($stru[2] eq 'D') {
$sql .= " date";
} elsif ($stru[2] eq 'L') {
$sql .= " boolean";
} elsif ($stru[2] eq 'M') {
$sql .= " text";
} elsif ($stru[2] eq 'G') {
$sql .= " text";
} elsif ($stru[2] eq 'C' && $stru[3] eq 1) {
$sql .= " char";
} elsif ($stru[2] eq 'C') {
$sql .= " varchar($stru[3])";
} elsif ($stru[2] eq 'N' && $stru[4] eq 0 && $stru[3] < 5) {
$sql .= " int2";
} elsif ($stru[2] eq 'N' && $stru[4] eq 0 && $stru[3] < 10) {
$sql .= " int4";
} elsif ($stru[2] eq 'N' && $stru[4] eq 0) {
$sql .= " int8";
} elsif ($stru[2] eq 'N') {
$sql .= " numeric($stru[3],$stru[4])";
} else {
$sql .= " $stru[2].$stru[3].$stru[4]";
}
$sep = ',';
}
}
close (PIPE);
$sql .= ' );';
$dbp->{RaiseError} = 0;
$dbp->do( "DROP TABLE $table" );
$dbp->{RaiseError} = 1;
$dbp->do( $sql );

my $sth = $dbf->prepare(" SELECT * FROM ".basename($fname) );
$sth->execute;
while (my @row = $sth->fetchrow_array()) {
$sql = "INSERT INTO $table VALUES ";
$sep = "(";
foreach my $fld (@row) {
$sql .= "$sep ".$dbp->quote($fld);
$sep = ",";
}
$sql .= ' );';
$dbp->do( $sql );
}
$sth->finish;
}