Re: [newbie] DbaseIV database to use in Mandrake 9.1

[Frank Bax]

At 02:33 PM 7/16/03, rikona wrote:
>I have some old dbase files I may convert too. If you get
>this to work, please post the result here. Thanks.
I have a perl script (from my *bsd system, but should work on Linux) that 
converts all dBase files in a directory to pgsql tables, you should be able 
to convert to mysql with different libraries.  You'll need to install:
DBI - abstraction layer for database access from perl
DBD::XBase - access to DBF files via DBI - include a program to dump structure
DBD::Pg or DBI:MySQL - access to database from DBI

For Mysql, you may need to change DBF2PG subroutine for translating 
datatypes; otherwise, just rewrite the mainline for your own purposes.

The script is written to take a parameter - if this is a filename (without 
extension), then it will process just that file, otherwise, every file 
starting with those characters.

Frank

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

my $dir = '/home/fbax/DBFfiles/' . $base;
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;
$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;
}

Want to buy your Pack or Services from MandrakeSoft? 
Go to http://www.mandrakestore.com