From richard@ibisaustralia.com Mon Sep  15 08:55:25 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 732BCD1B507
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 15 Sep 2003 08:55:25 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 24116-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 15 Sep 2003 05:54:55 -0300 (ADT)
Received: from server146.hostingrevolution.com
	(server146.hostingrevolution.com [64.21.143.245])
	by svr1.postgresql.org (Postfix) with ESMTP id 24EE2D1B50F
	for <pgsql-sql@postgresql.org>; Mon, 15 Sep 2003 05:54:53 -0300 (ADT)
Received: from 221.b.002.pth.iprimus.net.au ([210.50.109.221] helo=athlon2000)
	by server146.hostingrevolution.com with smtp (Exim 4.22)
	id 19yp8H-0000Gk-Bl
	for pgsql-sql@postgresql.org; Mon, 15 Sep 2003 03:54:32 -0500
Message-ID: <001601c37b67$608235e0$dd6d32d2@athlon2000>
From: "Richard Sydney-Smith" <richard@ibisaustralia.com>
To: <pgsql-sql@postgresql.org>
Subject: Copy Command Error
Date: Mon, 15 Sep 2003 16:57:07 +0800
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_000B_01C37BAA.657B6BF0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
X-AntiAbuse: This header was added to track abuse,
	please include it with any abuse report
X-AntiAbuse: Primary Hostname - server146.hostingrevolution.com
X-AntiAbuse: Original Domain - postgresql.org
X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [47 12]
X-AntiAbuse: Sender Address Domain - ibisaustralia.com
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=HTML_50_60
X-Spam-Level: 
X-Archive-Number: 200309/132
X-Sequence-Number: 15110

This is a multi-part message in MIME format.

------=_NextPart_000_000B_01C37BAA.657B6BF0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

I am outputting the following rows from a SYBASE database on W2000

3 '2' 'IBISFinancials' 'A.C.N. 072 378 815'
8 'RK' 'Personal Accounts'=20

Using the command:=20

select cast(seq as integer) , id , cmpy, tr_n from fcont; output to d:\fcon=
t.dat format 'ascii' delimited by '\x09'

This outputs the fields tab delimited and with strings encased in inverted =
commas

I then use the followin POSTRESQL command:

copy fcont (seq,id,cmpy,tr_n) from 'd:\\fcont.dat'

The problem is that each string is imported with the inverted commas.

eg field ID =3D ''RK'' instead of 'RK'

Can someone please help?

thanks

Richard



------=_NextPart_000_000B_01C37BAA.657B6BF0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2600.0" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>I am outputting the following rows from a =
SYBASE=20
database on W2000</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>3&nbsp;'2'&nbsp;'IBISFinancials'&nbsp;'A.C=
.N. 072=20
378 815'<BR>8&nbsp;'RK'&nbsp;'Personal Accounts'&nbsp;</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Using the command: </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>select cast(seq as integer) , id , cmpy, t=
r_n from=20
fcont; output to d:\fcont.dat format 'ascii' delimited by '\x09'</FONT></DI=
V>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>This outputs the fields tab delimited and =
with=20
strings encased in inverted commas</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I then use the followin POSTRESQL=20
command:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>copy fcont (seq,id,cmpy,tr_n) from=20
'd:\\fcont.dat'</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>The problem is that each string is importe=
d with=20
the inverted commas.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>eg field ID =3D ''RK'' instead of 'RK'</FO=
NT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Can someone please help?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>thanks</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Richard</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV></BODY></HTML>

------=_NextPart_000_000B_01C37BAA.657B6BF0--




From dev@archonet.com Mon Sep  15 10:02:03 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 69ED8D1B4F2
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 15 Sep 2003 10:02:03 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 34319-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 15 Sep 2003 07:01:33 -0300 (ADT)
Received: from anchor-post-30.mail.demon.net (anchor-post-30.mail.demon.net
	[194.217.242.88])
	by svr1.postgresql.org (Postfix) with ESMTP id D4C1AD1B4F1
	for <pgsql-sql@postgresql.org>; Mon, 15 Sep 2003 07:01:25 -0300 (ADT)
Received: from mwynhau.demon.co.uk ([193.237.186.96]
 helo=mainbox.archonet.com)
	by anchor-post-30.mail.demon.net with esmtp (Exim 3.35 #1)
	id 19yqAh-0008PN-0U; Mon, 15 Sep 2003 11:01:27 +0100
Received: from localhost (localhost.localdomain [127.0.0.1])
	by mainbox.archonet.com (Postfix) with ESMTP
	id 6B5E5162AB; Mon, 15 Sep 2003 11:01:26 +0100 (BST)
Received: from client17.archonet.com (client17.archonet.com [192.168.1.17])
	by mainbox.archonet.com (Postfix) with ESMTP
	id CA20E1597A; Mon, 15 Sep 2003 11:01:25 +0100 (BST)
From: Richard Huxton <dev@archonet.com>
To: "Richard Sydney-Smith" <richard@ibisaustralia.com>,
	<pgsql-sql@postgresql.org>
Subject: Re: Copy Command Error
Date: Mon, 15 Sep 2003 11:01:24 +0100
User-Agent: KMail/1.5
References: <001601c37b67$608235e0$dd6d32d2@athlon2000>
In-Reply-To: <001601c37b67$608235e0$dd6d32d2@athlon2000>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200309151101.24985.dev@archonet.com>
X-Virus-Scanned: by AMaViS snapshot-20020531
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200309/133
X-Sequence-Number: 15111

On Monday 15 September 2003 09:57, Richard Sydney-Smith wrote:
> I am outputting the following rows from a SYBASE database on W2000

> The problem is that each string is imported with the inverted commas.
>
> eg field ID = ''RK'' instead of 'RK'
>
> Can someone please help?

A short bit of perl. Something like (untested)

perl -pi -e 's/\t"/\t/g' fcont.dat
perl -pi -e 's/"\t/\t/g' fcont.dat

That should pre-process the file. I'm sure someone else on the list will show 
how to do it in sed.

Hmm - just noticed you are on Windows. Not sure what cygwin ships with, or 
whether you have perl installed.
-- 
  Richard Huxton
  Archonet Ltd