Import dbf data

Hello everyone,

I've decide to use PostgreSql as the SGBD for the application i'm
developing. I've already have some info based on dbf files. Someone
has any idea on how can i use the info on this dbf files?
If only i could extract de info of the the dbf, i could make the
database schemas and the genarate "insert into" script using the
information extracted of the dbf.

Thanks...

On 9/23/05, Rafael Barbosa <rrbarbosa(at)gmail(dot)com> wrote:
> Hello everyone,
>
> I've decide to use PostgreSql as the SGBD for the application i'm
> developing. I've already have some info based on dbf files. Someone
> has any idea on how can i use the info on this dbf files?
> If only i could extract de info of the the dbf, i could make the
> database schemas and the genarate "insert into" script using the
> information extracted of the dbf.
Two things I know off:
1.) Open dbf in scalc, save as DELIMITED TEXT.
2.) http://www.anubisnet.de/products/dbf

Cheers,
Andrej

At 03:22 PM 9/22/05, Rafael Barbosa wrote:
>I've decide to use PostgreSql as the SGBD for the application i'm
>developing. I've already have some info based on dbf files. Someone
>has any idea on how can i use the info on this dbf files?
>If only i could extract de info of the the dbf, i could make the
>database schemas and the genarate "insert into" script using the
>information extracted of the dbf.

From http://www.cpan.org/ you can install:
DBI - 1.48
DBD::Pg - 1.41
DBD::XBase - 0.241
Then write a perl script to access dbf file with sql statements. This
might help get you started:

#!/usr/bin/perl -w
use strict;
use DBI; use DBD::XBase; use DBD::Pg;
use File::Basename; # for basename() function

my $base = shift;

my $dir = '/home/FamTree/' . $base . '/data';
my $dbf = DBI->connect("dbi:XBase:$dir", {RaiseError => 1} );
my $dbp = DBI->connect("dbi:Pg:dbname=famtree", "famtree", "", {RaiseError
=> 1} );

while (my $fname = <$dir/$base*.DBF>) {
&DBF2PG ($dbf, $dbp, $fname, basename(substr($fname, 0, length($fname)-4)));
}
$dbf->disconnect();

$ cat scripts/DBF2PG.pl

sub DBF2PG {
(my $dbf, my $dbh, 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])";
} elsif ($stru[2] eq 'I' && $stru[4] eq 0 && $stru[3] eq 4) {
$sql .= " int4";
} else {
$sql .= " $stru[2].$stru[3].$stru[4]";
}
$sep = ',';
}
}
close (PIPE);
$sql .= ' );';
$dbh->{RaiseError} = 0; $dbh->{PrintError} = 0;
$dbh->do( "DROP TABLE $table" );
$dbh->{RaiseError} = 1; $dbh->{PrintError} = 1;
$dbh->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 ".$dbh->quote($fld);
$sep = ",";
}
$sql .= ' );';
$dbh->do( $sql );
}
$sth->finish;
}

1;