Postgres Pro
Downloads
Home   >   mailing lists

Thread: Import dbf data

Import dbf data

From
Rafael Barbosa
Date:
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...

Re: Import dbf data

From
Andrej Ricnik-Bay
Date:
On 9/23/05, Rafael Barbosa <rrbarbosa@gmail.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

Re: Import dbf data

From
Frank Bax
Date:
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;