From: | Thomas Good <tomg(at)nrnet(dot)org> |
---|---|
To: | "S(dot)Ramaswamy" <srswamy(at)giasdl01(dot)vsnl(dot)net(dot)in> |
Cc: | pgsql-admin(at)postgreSQL(dot)org |
Subject: | Re: [ADMIN] How to transfer data from FoxPro data to Postgresql? |
Date: | 1998-09-01 18:42:42 |
Message-ID: | Pine.LNX.3.96.980901142018.11042A-100000@mailhost.nrnet.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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(at)q8(dot)nrnet(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | eigenstr | 1998-09-01 18:54:32 | Re: [ADMIN] How to transfer data from FoxPro data to Postgresql? |
Previous Message | S.Ramaswamy | 1998-09-01 16:03:41 | How to transfer data from FoxPro data to Postgresql? |