Received: from localhost (pluto.hub.org [200.46.204.4]) by postgresql.org (Postfix) with ESMTP id 19D3A9FA186 for ; Tue, 21 Nov 2006 21:20:24 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.4]) (amavisd-new, port 10024) with ESMTP id 80159-01 for ; Tue, 21 Nov 2006 21:20:15 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.191]) by postgresql.org (Postfix) with ESMTP id 0C9119FA161 for ; Tue, 21 Nov 2006 21:20:19 -0400 (AST) Received: by nf-out-0910.google.com with SMTP id x4so369908nfb for ; Tue, 21 Nov 2006 17:20:17 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=YKSJt20HXxt2DqgCfSpGtUIVKWjIXbqqcn4n1eMpMTZjhKt5BR2+vSBnJMmTSWuqS+mPd1i8KuupUUwn96rFapwqHYOKM+Nm56amwzahxJsx7IdOsc9IoocvARF6LcVbIvMRSTuBc8yM9C7JcdLTAjN5g3VBZxNzrgjtlrSxSZI= Received: by 10.78.201.15 with SMTP id y15mr7118809huf.1164152535565; Tue, 21 Nov 2006 15:42:15 -0800 (PST) Received: by 10.78.136.6 with HTTP; Tue, 21 Nov 2006 15:42:15 -0800 (PST) Message-ID: Date: Tue, 21 Nov 2006 17:42:15 -0600 From: "Aaron Bono" To: "Steve Midgley" Subject: Re: "Copy from" omit source columns? Cc: pgsql-sql@postgresql.org In-Reply-To: <20061121221911.C10D09FB26E@postgresql.org> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_51016_30386002.1164152535399" References: <20061121221911.C10D09FB26E@postgresql.org> X-Google-Sender-Auth: a6d1aa8e7e211839 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/226 X-Sequence-Number: 26878 ------=_Part_51016_30386002.1164152535399 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 11/21/06, Steve Midgley wrote: > > Hello, > > (I already fear the answer to my question is "no..") :) > > I am using the 'COPY FROM' SQL command (not psql \copy) to move data > into a table from a CSV file. The source data are suitable for import > EXCEPT there are extraneous columns of data in the source file. In > essence, my table schema is: > > id, col1, col2, col4 > > My source file schema is: > > col1,col2,col3,col4 > > I'm curious if there is any way to tell "COPY" to ignore the third > column of data? Basically I'd like to write something along the lines > of: > > copy import (col1,col2,\n,col4) > from 'c:/dev/import/source/test.csv' > with CSV HEADER > > Note "\n" would indicate there there is a column in the source that > should simply be ignored during the import. Of course, I tried this > syntax and it didn't work. Perhaps there is another way of indicating > that there is a "null column" in the source file that should be skipped > during import? > > Any assistance in solving this problem (without having to change the > source file) would be greatly appreciated. If I have to change the > source file, I will, but it would greatly simplify things if I didn't > have to.. > > Best regards and thanks for any ideas, > If you are using Linux/Unix or a Mac, you could run the file through sed first and remove the unwanted column: cat original.csv | sed 's/^\(.*\),\(.*\),\(.*\),\(.*\)$/\1,\2,\4/' > new.csv Of course if you have commas in the data you will have to play with the sed command a little but this will get you started. -- ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ================================================================== ------=_Part_51016_30386002.1164152535399 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 11/21/06, Steve Midgley <public@misuse.org> wrote:
Hello,

(I already fear the answer to my question is "no..") :)

I am using the 'COPY FROM' SQL command (not psql \copy) to move data
into a table from a CSV file. The source data are suitable for import
EXCEPT there are extraneous columns of data in the source file. In
essence, my table schema is:

id, col1, col2, col4

My source file schema is:

col1,col2,col3,col4

I'm curious if there is any way to tell "COPY" to ignore the third
column of data? Basically I'd like to write something along the lines
of:

copy import (col1,col2,\n,col4)
from 'c:/dev/import/source/test.csv'
with CSV HEADER

Note "\n" would indicate there there is a column in the source that
should simply be ignored during the import. Of course, I tried this
syntax and it didn't work. Perhaps there is another way of indicating
that there is a "null column" in the source file that should be skipped
during import?

Any assistance in solving this problem (without having to change the
source file) would be greatly appreciated. If I have to change the
source file, I will, but it would greatly simplify things if I didn't
have to..

Best regards and thanks for any ideas,

If you are using Linux/Unix or a Mac, you could run the file through sed first and remove the unwanted column:

cat original.csv | sed 's/^\(.*\),\(.*\),\(.*\),\(.*\)$/\1,\2,\4/' > new.csv

Of course if you have commas in the data you will have to play with the sed command a little but this will get you started.

--
==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
================================================================== ------=_Part_51016_30386002.1164152535399--