Postgres Pro
Downloads
Home   >   mailing lists

Thread: COPY FROM function

COPY FROM function

From
"Patrick Hatcher"
Date:
I have 2 questions concerning COPY FROM:

1 - I've successfully imported a comma-delimited file that contain no
quotes (" ").  I now have to import a FileMaker export where all fields
contain quotes.  How do I setup COPY to accept this

2 - Is there a way to import to only certain fields using the COPY FROM
function?  Or would I need to import to a temp table and do an insert to
the main table.

Ex:  CREATE TABLE tb1 (
  F1   int4,
  F2 varchar(255),
  F3 varchar(10),
  F4 varchar(55)
);

I may only want to import data into F1, F3 and F4.

Thanks in advance

Patrick Hatcher
Macys.Com
Legacy Integration Developer
415-932-0610 office

Re: COPY FROM function

From
"Joshua b. Jore"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You'll want to convert CSV into something slightly more sane. Check out
http://www.greentechnologist.org/perl/mac2local.pl and
http://www.greentechnologist.org/perl/csv2tab.pl to un-Macify the data and
then turn the CSV into tab delimited data.

Once converted you could read just specific columns by running a simple
perl script.like

#!/usr/local/bin/perl
while (<>) {
    @ary = split /\t/;
    splice @ary, 1, 1;
    print join "\t", @ary;
}


Joshua b. Jore
http://www.greentechnologist.org

On Wed, 6 Feb 2002, Patrick Hatcher wrote:

> I have 2 questions concerning COPY FROM:
>
> 1 - I've successfully imported a comma-delimited file that contain no
> quotes (" ").  I now have to import a FileMaker export where all fields
> contain quotes.  How do I setup COPY to accept this
>
> 2 - Is there a way to import to only certain fields using the COPY FROM
> function?  Or would I need to import to a temp table and do an insert to
> the main table.
>
> Ex:  CREATE TABLE tb1 (
>   F1   int4,
>   F2 varchar(255),
>   F3 varchar(10),
>   F4 varchar(55)
> );
>
> I may only want to import data into F1, F3 and F4.
>
> Thanks in advance
>
> Patrick Hatcher
> Macys.Com
> Legacy Integration Developer
> 415-932-0610 office
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (OpenBSD)
Comment: For info see http://www.gnupg.org

iD8DBQE8YbMCfexLsowstzcRAk+zAKCwm68dfp1P8Z8Ms6KXMA2qWfQw3wCg8FxI
aZMh5X26wXypo07qiXSwmbA=
=HN4g
-----END PGP SIGNATURE-----

Re: COPY FROM function

From
"Patrick Hatcher"
Date:
Thank you

Patrick Hatcher
Macys.Com
Legacy Integration Developer
415-932-0610 office





                    "Joshua b. Jore"
                    <josh@greentechnol       To:     Patrick Hatcher <PHatcher@macys.com>
                    ogist.org>               cc:     pgsql-novice@postgresql.org
                                             Subject:     Re: [NOVICE] COPY FROM function
                    02/06/2002 02:49
                    PM






-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You'll want to convert CSV into something slightly more sane. Check out
http://www.greentechnologist.org/perl/mac2local.pl and
http://www.greentechnologist.org/perl/csv2tab.pl to un-Macify the data and
then turn the CSV into tab delimited data.

Once converted you could read just specific columns by running a simple
perl script.like

#!/usr/local/bin/perl
while (<>) {
           @ary = split /\t/;
           splice @ary, 1, 1;
           print join "\t", @ary;
}


Joshua b. Jore
http://www.greentechnologist.org

On Wed, 6 Feb 2002, Patrick Hatcher wrote:

> I have 2 questions concerning COPY FROM:
>
> 1 - I've successfully imported a comma-delimited file that contain no
> quotes (" ").  I now have to import a FileMaker export where all fields
> contain quotes.  How do I setup COPY to accept this
>
> 2 - Is there a way to import to only certain fields using the COPY FROM
> function?  Or would I need to import to a temp table and do an insert to
> the main table.
>
> Ex:  CREATE TABLE tb1 (
>   F1   int4,
>   F2 varchar(255),
>   F3 varchar(10),
>   F4 varchar(55)
> );
>
> I may only want to import data into F1, F3 and F4.
>
> Thanks in advance
>
> Patrick Hatcher
> Macys.Com
> Legacy Integration Developer
> 415-932-0610 office
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (OpenBSD)
Comment: For info see http://www.gnupg.org

iD8DBQE8YbMCfexLsowstzcRAk+zAKCwm68dfp1P8Z8Ms6KXMA2qWfQw3wCg8FxI
aZMh5X26wXypo07qiXSwmbA=
=HN4g
-----END PGP SIGNATURE-----

Re: COPY FROM function

From
Frank Bax
Date:
At 04:49 PM 2/6/02 -0600, Joshua b. Jore wrote:
>Once converted you could read just specific columns by running a simple
>perl script.like
>
>#!/usr/local/bin/perl
>while (<>) {
>    @ary = split /\t/;
>    splice @ary, 1, 1;
>    print join "\t", @ary;
>}

see also:
    man cut