Обсуждение: How to transfer data from FoxPro data to Postgresql?

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?


Regards

S.Ramaswam

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?

Hi,

I don't know the best way ;-)

Here is what I do (this is the `clumsy way'):

Dump the query output as a dumpfile delimited by tabs.
Reformat the output for pgsql...

NOTES: FoxPro DOS gave me an EOF (^Z) that had to be stripped.
As DOS does a ^J^M dance (CRLF) these embedded chars must also be
stripped. The /         / bit in the script below was to rm any bad date
values - making them proper NULLs.  The empty date placeholders must
be nulled BEFORE you strip away the double quotes otherwise char strs
end up with embedded nulls.  ;-)

Next the double quote FoxPro nulls ( "" ) are converted to proper
NULLs.  Lastly, the surviving quotes are stripped.

IMPORTANT: the ^Z and ^M are _literals_.  I edit them into the script
with i, ^V, ^M  (in vi...)

#!/bin/sh
tput clear
echo -n "Enter file to be converted: "
read file
sed -e '/^Z/d' $file > $file.step0
sed -e 's/^M//g' $file.step0 > $file.step1
sed -e 's/        /\\N/g' $file.step1 > $file.step2
sed -e 's/""/\\N/g' $file.step2 > $file.step3
sed -e 's/"//g' $file.step3 > $file.pg
rm $file.step0
rm $file.step1
rm $file.step2
rm $file.step3

After this, add
COPY table_name FROM stdin;
to the top of your dumpfile.

And
\.
to the end.

Then run:
psql -e db_name < dumpfile_name

BTW, if you have questions, fire away.  All of us FoxPro victims must
stick together!  (I recommend a pint of bitter be kept handy during the
process of hacking the foxpro psuedo-sql into postgres ;-)

Cheers,
Tom

    ----------- Sisters of Charity Medical Center ----------
                    Department of Psychiatry
                              ----
 Thomas Good, System Administrator            <tomg@q8.nrnet.org>
 North Richmond CMHC/Residential Services     Phone: 718-354-5528
 75 Vanderbilt Ave, Quarters 8                Fax:   718-354-5056
 Staten Island, NY   10304                    www.panix.com/~ugd
                              ----
 Powered by PostgreSQL 6.3.2 / Perl 5.004 / DBI-0.91::DBD-PG-0.69

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";

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?
> Regards
> S.Ramaswam

Off the top of my head ... in a hurry ... so check syntax ...
in vfp
use table
copy to filename delimited with '|' ..... or some such
check the help for copy.  What you want to end up with is a text file with
all fields pipe delimited '|' and double quotes around any char/text
fields.
Why a pipe?  It PROBABLY does not exist in your data.
...Ya, would be nice to NOT have the quotes, but M$ INSISTS on
putting some thing extra there, so may as well be quotes, which is the
default.

Now, on your Linux box, use sed or awk to strip out the carraige returns
and also to strip out the double quotes that are next to a pipe.
If you don't know sed or awk, get the O'Reilly book "Sed & Awk".
Oh what fun =)

Now, in psql do:
copy tablename from 'filename' delimiter '|' ... or some such
do '\h copy' for exact syntax.

                            *** OR ***

Email the vfp files to me and for $25/hour I will send you back a postgres
dump file :)

Hope that helps
Terry Mackintosh <terry@terrym.com>          http://www.terrym.com
sysadmin/owner  Please! No MIME encoded or HTML mail, unless needed.

Proudly powered by R H Linux 4.2, Apache 1.3, PHP 3, PostgreSQL 6.3
-------------------------------------------------------------------
Success Is A Choice ... book by Rick Patino, get it, read it!