Re: [ADMIN] How to transfer data from FoxPro data to Postgresql?

Thomas Good writes:
>On Tue, 1 Sep 1998, S.Ramaswamy wrote:
>
>> We have our data now in FoxPro dbf format under DOS. We want to transfer
>> this to
>> Postgresql under Linux. What is the best way to do so?

Have you checked out the Xbase Perl module from CPAN?  Xbase reads
generic DBF-format files.  From there it would be easy to use the Pg
Perl module to connect to Postgres.  (It doesn't understand FoxPro
compact indices, at least not the last time I looked, but that may not
matter for you.)

Here's a simple program I wrote for one of our clients to yank data
out of Adman (a FoxPro-based billing system) for use on their
intranet.  In this case, it's taking the DBFs and converting them to
tab-delimited flat files.  (Incidentally, the data is grabbed from a
Netware partition that's been mounted by the Linux intranet server.
...and then exported to the rest of their WAN using Samba.  But I
digress...)

It's not pretty, but it's real-world. :-)


Todd

----------------------------------------------------------------------

#!/usr/bin/perl

chdir "/tmp";

use Xbase;

my $DIR    = "/mnt/netware/vol1/Adman_WM/DATA01";

$JOB_DB    = "$DIR/J/JOB.DBF";
$CLIENT_DB = "$DIR/C/CLIENT.DBF";
$VENDOR_DB = "$DIR/A/APVENDOR.DBF";

-f $JOB_DB    or die "Cannot open $JOB_DB: $!";
-f $CLIENT_DB or die "Cannot open $CLIENT_DB: $!";
-f $VENDOR_DB or die "Cannot open $VENDOR_DB: $!";


my (%job_title, %client_name);

$db = new Xbase;

######################################################################

$db->open_dbf( $JOB_DB );
$db->go_top;

while ( ! $db->eof )
{
    my @fields = $db->get_record;
    my $client_code = $fields[ 0 ];
    my $job_number = $fields[ 1 ];
    my $job_title_1 = $fields[ 4 ];
    my $job_title_2 = $fields[ 5 ];

    $job_title{ $client_code, $job_number } = $job_title_1;

#    printf STDERR "%4s: %4d: $job_title_1\n", $client_code, $job_number;

    $db->go_next;
}

$db->close_dbf;


######################################################################

$db->open_dbf( $CLIENT_DB );
$db->go_top;

while ( ! $db->eof )
{
    my @fields = $db->get_record;
    my $client_code = $fields[ 0 ];
    my $client_name = $fields[ 1 ];
    my $client_addr1 = $fields[ 2 ];
    my $client_addr2 = $fields[ 3 ];
    my $client_addr3 = $fields[ 4 ];
    my $client_att = $fields[ 5 ];
    my $client_phone = $fields[ 48 ];

    $client_name{ $client_code } = $client_name;
    $client_addr1{ $client_code } = $client_addr1;
    $client_addr2{ $client_code } = $client_addr2;
    $client_addr3{ $client_code } = $client_addr3;
    $client_att{ $client_code } = $client_att;
    $client_phone{ $client_code } = $client_phone;

#    printf STDERR "%4s: $client_name\n", $client_code;
    $db->go_next;
}

$db->close_dbf;


my @jobs = keys %job_title;

foreach $client ( keys %client_name )
{
    my @j = grep /^$client/, @jobs;

    if ( @j == 1 )
    {
    my ($code, $job) = split /$;/, $j[0];
    if ( $job == 9999 )
    {
        $skip1 ++;
#        print "$skip1: Skipping client $code: $client_name{ $code }\n";
        delete $client_name{ $code };
        delete $job_title{ $j[ 0 ] };
    }
    else
    {
#        print "Single job $job: $code: $client_name{ $code }\n";
    }
    }
    elsif ( @j == 0 )
    {
    $skip2 ++;
#    print "$skip2: Client with no jobs: $client: ",
#        "$client_name{ $client }\n";
    delete $client_name{ $client };
    }
}

######################################################################

open MAP, "> jobs.map";

print MAP "JobNumber\tJobName\n";

foreach $key ( keys %job_title )
{
    my $val = $job_title{ $key };

    my ($client_code, $job_number) = split /$;/, $key, 2;
    next if ! $client_name{ $client_code } or $job_number == 9999;

    print MAP "$client_code$job_number\t$val\n";
}

close MAP;

######################################################################

open MAP, "> client.map";

print MAP "ClientName\tClientCode\tClientContact\tClientAddress1",
    "\tClientAddress2\tClientAddress3\tClientPhone\n";

foreach $key ( sort { $client_name{$a} cmp $client_name{$b} }
                keys %client_name )
{
    my $val = $client_name{ $key };
    $key =~ s/^\s*(.*?)\s*$/$1/;
    $val =~ s/^\s*(.*?)\s*$/$1/;

    print MAP "$val\t$key\t$client_att{ $key }\t$client_addr1{ $key }",
        "\t$client_addr2{ $key }\t$client_addr3{ $key }",
        "\t$client_phone{ $key }\n";
}

close MAP;

######################################################################

$db->open_dbf( $VENDOR_DB );
$db->go_top;

open VENDOR, "> vendor.map";

print VENDOR "VendorCode\tVendorName\tAddress1\tAddress2\tAddress3\tAttention\tContact\tPhone\tFax\n";

while ( ! $db->eof )
{
    my @fields = $db->get_record;
    my $vendor_code = $fields[ 0 ];
    my $vendor_name = $fields[ 1 ];
    my $vendor_address_1 = $fields[ 2 ];
    my $vendor_address_2 = $fields[ 3 ];
    my $vendor_address_3 = $fields[ 4 ];
    my $vendor_attention = $fields[ 5 ];
    my $vendor_contact = $fields[ 6 ];
    my $vendor_phone = $fields[ 7 ];
    my $vendor_fax = $fields[ 27 ];

    print VENDOR <<EOF;

$vendor_code\t$vendor_name\t$vendor_address_1\t$vendor_address_2\t$vendor_address_3\t$vendor_attention\t$vendor_contact\t$vendor_phone\t$vendor_fax
EOF

    $db->go_next;
}

$db->close_dbf;

close VENDOR;

######################################################################

my $dir = "/web/data/client/forms/maps";

system "/bin/ln -f $dir/client.map $dir/client.map-";
system "/bin/cp client.map $dir/client.map";

system "/bin/ln -f $dir/jobs.map $dir/jobs.map-";
system "/bin/cp jobs.map $dir/jobs.map";

system "/bin/ln -f $dir/vendor.map $dir/vendor.map-";
system "/bin/cp vendor.map $dir/vendor.map";