Re: dos Dbase -> pg table - Mailing list pgsql-novice

install DBI, DBD:pg, DBD:XBase, then run this script (after customising the
$dir, $dbf and $dbp lines):

#!/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;
}


At 08:44 AM 1/3/02 -0300, Fernando M. Maresca wrote:
>Hello everybody,
>
>There is out there a proggy called dbf2pg (like dbf2mysql) that makes a
good job, but it can not deal with MEMO
>fields. AHHGGG!!!
>So, somebody knows a program that can deal with MEMO fields in (DOS) .dbf
files, or, at least, a program that can
>cut off memo fields of a .dbf file from command line in *nix?
>The idea is to build a script (well, I wrote it and run ok if there are no
MEMOs in source .dbf) that cron.ically
>updates a table from a dbase file.
>Tnx, regards
>
>
>--
>Fernando M. Maresca
>
>Monitoring Station S.A.
>48 nº 812 La Plata (1900) BA - ARG
>Tel/Fax: (54) 221 425-3355
>ICQ: 101304086
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html
>