From: Phillip Smith <phillip.smith@weatherbeeta.com.au>
To: pgsql-sql@postgresql.org
Subject: Re: Issue with copying data from a text file.
Date: Tue, 20 Mar 2007 14:05:24 +1100
Message-ID: <1174359924.10466.32.camel@it-laptop> (raw)
In-Reply-To: <45FF4DFA.1020202@autoledgers.com.au>
References: <45FF4622.2010404@autoledgers.com.au>
<1174358236.10466.14.camel@it-laptop>
<45FF4AA3.4060808@autoledgers.com.au>
<b35603930703191953g73e0e28bl3012e7b6836ba780@mail.gmail.com>
<45FF4DFA.1020202@autoledgers.com.au>
Maybe use char 254 or 253 or something similar... Anything that isn't
going to be found in the file.
Have you tried using a string as a delimiter? QUOTE
'THIS.STRING.ISNT.IN.THE.TEXT.FILE'
~p
On Tue, 2007-03-20 at 11:59 +0900, Paul Lambert wrote:
> Andrej Ricnik-Bay wrote:
> > On 3/20/07, Paul Lambert <paul.lambert@autoledgers.com.au> wrote:
> >
> >> The source file comes from extracts on our main application which sits
> >> inside an in-house pretending-to-be-a-dbms file system. The content of
> >> these extracts would be difficult to change - the extract program would
> >> need to parse the data looking for quotes and preceed them with the
> >> necessary escape character.
> >>
> >> Not being a proper database dump it's not a simple matter of flicking a
> >> switch to get it to include the escape character. The way the extracts
> >> are written would require a few dozen lines of code to each extract, and
> >> theres about 40ish extracts.
> >>
> >> Plus I don't maintain that side of our code, and those that do can be a
> >> bit lazy and I'd likely be waiting months to get it done - if they even
> >> decide to do it.
> > Pipe it through sed and replace the Carets with TABS?
> > sed 's/^/\t/g' c:/temp/autodrs_deal_lines.txt >
> > c:/temp/autodrs_deal_lines.tab
> >
> > Then use copy like so:
> > \copy table from 'c:/temp/autodrs_deal_lines.tab' delimiter E'\t' null ''
> >
> >
> > Cheers,
> > Andrej
> >
> >
>
> The data contains tabs... don't ask why... I don't have a clue :P
>
> I'll do something along the lines of sed... but not with sed, I'll use
> the command line interpreter on the OpenVMS systems where the extracts
> run. I just thought there might have been a quicker way to switch it off
> in the copy command, i.e. specifying "quote none" as one of the
> parameters to the command. I guess not...
>
> Thanks for the pointers.
>
> P.
>
Phillip Smith
IT Coordinator
Weatherbeeta P/L
8 Moncrief Rd
Nunawading, Vic, 3131
AUSTRALIA
P. +613 9845 0600
F. +613 9845 0655
E. phillip.smith@weatherbeeta.com.au
Attachments:
smime.p7s (application/x-pkcs7-signature, 2.1K) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: phillip.smith@weatherbeeta.com.au
Subject: Re: Issue with copying data from a text file.
In-Reply-To: <1174359924.10466.32.camel@it-laptop>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox