First ahve postgres "copy" the file using tabs as the delimiter then this is
what I used to convert a "copy into" file to foxbase importable format...
s/([^\t]*?)\t([^\t]*?)\t([+-]*\d*?)\t([+-]*\d*?)\t([^\t]*?)\t([^\t]*?)\t([^\
t]*?)\t([^\t]*?)\t([^\t]*?)\t([^\t]*?)\t([^\t]*?)\t([^\t]*?)\t([^\t]*?)\t([^
\t]*?)\t([^\t]*?)\t([^\t]*?)\t([^\t]*?)\t([^\t]*?)\t([^\t]*?)\t([^\t]*?)\t([
^\t]*?)\t([^\t]*?)\t([^\t]*?)\t(.*)/"$1"\t"$2"\t$3\t$4\t"$5"\t"$6"\t"$7"\t"$
8"\t"$9"\t"$10"\t"$11"\t"$12"\t"$13"\t"$14"\t"$15"\t"$16"\t"$17"\t"$18"\t"$1
9"\t"$20"\t"$21"\t"$22"/;
As you can see the text file has 22 fields in it with some of them being
numeric and others being text (the quoted ones).
If you don't understand some of the regex expressions let me know....
-----Original Message-----
From: Thomas Good <tomg@q8.nrnet.org>
To: pgsql-novice@postgreSQL.org <pgsql-novice@postgreSQL.org>
Cc: pgsql-general@postgreSQL.org <pgsql-general@postgreSQL.org>
Date: Tuesday, April 27, 1999 8:31 AM
Subject: [GENERAL] Desperately Seeking Regular Expression
>Hi all -
>
>I am porting a PROGRESS database to PostgreSQL.
>
>I've had success previously doing a port - but from FoxPro which
>allows one to dump data delimited by tabs. Unfortunately, PROGRESS
>dumps fields delimited by whitespace rather than tabs and I can find no
>documentation on how to alter this behaviour.
>
>I read the recent post wherein someone used awk to change whitespace
>to tabs:
>
>cat $input | awk '{ print $1"\t"$2"\t"$3"\t"$4"\t"$5"\t" \
>$6"\t"$7"\t" }' > $input.out
>
>I am using this with good effect. However, I run into trouble as
>inside my dump file(s) there are doublequoted character strings.
>awk is changing the whitespace delimited words inside the char strs
>into tab delimited words inside strings. Ouch.
>
>What follows is my inept effort to get sed on my side as I try to sort
>this out:
>sed -e 's/" *"/ /g' $input.out > $input.sql
>
>This is a miserable failure as it simply converts all the tabs back
>to whitespace. I've tried escaping the double quotes in the regex
>but then sed changes nothing.
>
>Can someone put me out of my misery? Anyone have suggestions on a
>regular expression that will:
>
>Convert tabs to whitespace *inside* of double quoted strings *only* ???
>I don't care if the regex is for sed/awk/perl, whatever, I need to get
>the job done!
>
>TIA!
>
>Stuck in Staten Island,
>Tom
>----
> North Richmond Community Mental Health Center
> ---
> Thomas Good tomg@ { admin | q8 } .nrnet.org
> Phone: 718-354-5528
> Fax: 718-354-5056
> Powered By: Slackware 3.6 PostgreSQL 6.3.2
> ---
> /* Die Wahrheit Ist Irgendwo Da Draussen... */
>
>
