Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 1CE949FB3A2 for ; Mon, 19 Mar 2007 23:59:42 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 32033-08 for ; Mon, 19 Mar 2007 23:59:38 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from rrmailscan3.safenetbox.biz (unknown [203.25.40.75]) by postgresql.org (Postfix) with ESMTP id A95C49FB30B for ; Mon, 19 Mar 2007 23:59:38 -0300 (ADT) Received: from rrmailscan3.safenetbox.biz (rrmailscan3.safenetbox.biz [127.0.0.1]) by rrmailscan3.safenetbox.biz (Postfix) with ESMTP id CD8821D48479; Tue, 20 Mar 2007 13:59:36 +1100 (EST) Received: from [127.0.0.1] (online4-new.lnk.telstra.net [139.130.169.227]) (Authenticated sender: remote) by rrmailscan3.safenetbox.biz (Postfix) with ESMTP id 3E1251D4847A; Tue, 20 Mar 2007 13:59:35 +1100 (EST) Message-ID: <45FF4DFA.1020202@autoledgers.com.au> Date: Tue, 20 Mar 2007 11:59:06 +0900 From: Paul Lambert User-Agent: Thunderbird 1.5.0.10 (Windows/20070221) MIME-Version: 1.0 To: Andrej Ricnik-Bay CC: Phillip Smith , pgsql-sql@postgresql.org Subject: Re: Issue with copying data from a text file. References: <45FF4622.2010404@autoledgers.com.au> <1174358236.10466.14.camel@it-laptop> <45FF4AA3.4060808@autoledgers.com.au> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/260 X-Sequence-Number: 27898 Andrej Ricnik-Bay wrote: > On 3/20/07, Paul Lambert 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. -- Paul Lambert Database Administrator AutoLedgers