Re: Import dbf data - Mailing list pgsql-novice

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;