X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 308DAD1B52A for ; Fri, 9 Jan 2004 04:33:33 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 36095-07 for ; Fri, 9 Jan 2004 00:32:44 -0400 (AST) Received: from domino.nc.com.my (domino.nc.com.my [202.185.214.131]) by svr1.postgresql.org (Postfix) with ESMTP id BF0B6D1B4C7 for ; Fri, 9 Jan 2004 00:32:42 -0400 (AST) Subject: Re: COPY command To: pgsql-sql@postgresql.org X-Mailer: Lotus Notes Release 5.0 (Intl) 30 March 1999 Message-ID: From: azwa@nc.com.my Date: Fri, 9 Jan 2004 12:11:29 +0800 X-MIMETrack: Serialize by Router on domino/ANSI(Release 5.0.5 |September 22, 2000) at 01/09/2004 12:11:31 PM, Serialize complete at 01/09/2004 12:11:31 PM MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="=_alternative 0019255A48256E16_=" X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=1.7 tagged_above=0.0 required=5.0 tests=BAYES_30, HTML_20_30, NO_REAL_NAME X-Spam-Level: * X-Archive-Number: 200401/62 X-Sequence-Number: 16455 This is a multipart message in MIME format. --=_alternative 0019255A48256E16_= Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi ,=20 yes now i can do the copy after replace comma delimiter with tab delimiter= =20 which is default copy with delimiter . btw =A0i've another=20 question : how do we run the COPY command from file which has several null= =20 columns in each field. =A0=20 thanks.=20 example :=20 =A0 =A0dwnc=3D# copy biosadm.custinv_temp=20 dwnc-# from '/home/bios/customer_data/CustomerInvoice_5Dec03_tab.txt' ;=20 ERROR: =A0copy: line 1, Missing data for column "subsidiary" =A0=20 p/s: my subsidiary column has several null data.=20 "Gregory S. Williamson" 07:06 PM PST Yesterday To: , cc:=20 Subject: RE: [SQL] COPY command This sounds as if the last character in each line might be a delimiter (a= =20 ",") which is standard for data unloaded from some sources; if this is the= =20 case try removing it and your data should load. Or you may have a comma in= =20 a character field and that is throwing off the count for the line in=20 question ? HTH, Greg Williamson DBA GlobeXplorer LLC -----Original Message----- From: azwa@nc.com.my [mailto:azwa@nc.com.my] Sent: Tue 1/6/2004 10:19 PM To: pgsql-sql@postgresql.org Cc:=20 Subject: [SQL] COPY command Hi, i try to load a file into temporary table but it gives me an error . do= =20 u have any idea/solutions ?? the command that i've running as below : dwnc=3D# copy biosadm.custdo_temp dwnc-# from '/home/bios/customer_data/CustomerDO_new.CSV' dwnc-# WITH DELIMITER ',' ; ERROR: copy: line 141, Extra data after last expected column FYI, my file was in comma delimiter type (csv) . My table structure as=20 following : dwnc-# \dbiosadm.custdo_temp Table "biosadm.custdo_temp" Column | Type | Modifiers ----------+-----------------------+----------- dono | character varying(13) | dodate | date | custname | character varying(70) | custlo | character varying(40) | attnto | character varying(80) | Please guide me . thanks --=_alternative 0019255A48256E16_= Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi ,

yes now i can do the copy after repl= ace comma delimiter with tab delimiter which is default copy with delimiter= . btw =A0i've another
question : how do we run the COPY co= mmand from file which has several null columns in each field. =A0
thanks.

example :

=A0 =A0dwnc=3D# copy biosadm.custinv= _temp
dwnc-# from '/home/bios/customer_dat= a/CustomerInvoice_5Dec03_tab.txt' ;
ERROR: =A0copy: line 1, Missing data= for column "subsidiary" =A0

p/s: my subsidiary column has severa= l null data.

"Gregory S. = Williamson" <gsw@globexplorer.com>

07:06 PM PST Yesterday

To: &nbs= p; <azwa@nc.com.my>, <pgsql-sql@postgresql.org= >
cc: &nbs= p;
Subject:= RE: [SQL] COPY command

This sounds as if the last character in each line might be a delimiter (a &= quot;,") which is standard for data unloaded from some sources; if thi= s is the case try removing it and your data should load. Or you may have a = comma in a character field and that is throwing off the count for the line = in question ?

HTH,

Greg Williamson
DBA
GlobeXplorer LLC

-----Original Message-----
From: azwa@nc.com.m= y [mailto:azwa@nc.com.my]
Sent: Tue 1/6/2004 = 10:19 PM
To: pgsql-sql@postg= resql.org
Cc:
Subject: [SQL] COPY= command

Hi,

i try to load a file into temporary table but it gives me an error .= do
u have any idea/solutions ?? the command that i've running as below :=

dwnc=3D# copy biosadm.custdo_temp
dwnc-# from '/home/bios/customer_data/CustomerDO_new.CSV'
dwnc-# WITH DELIMITER ',' ;

ERROR: copy: line 141, Extra data after last expected column

FYI, my file was in comma delimiter type (csv) . My table structure as
following :

dwnc-# \dbiosadm.custdo_temp
Table "biosadm.custdo_temp"
Column | Type = | Modifiers
----------+-----------------------+-----------
dono | character varying(13) |
dodate | date &nbs= p; |
custname | character varying(70) |
custlo | character varying(40) |
attnto | character varying(80) |

Please guide me . thanks

--=_alternative 0019255A48256E16_=--