pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
SQL copy from csv with explicit field ordering
2+ messages / 2 participants
[nested] [flat]

* SQL copy from csv with explicit field ordering
@ 2001-03-01 20:46  Terry Fielder <terry@greatgulfhomes.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Terry Fielder @ 2001-03-01 20:46 UTC (permalink / raw)
  To: pgsql-sql

I guess this is really a SQL question:

I have a csv that I want to import, but the csv has different column
ordering.

I have tried putting the column names in the first row, but then the
copy command fails on field which is data type (eg it is seeing the
cells in first row as data, not header info).

ggtest=> copy ap_trans from '/var/dbase/ap_trans.csv' using delimiters
',';
ERROR:  pg_atoi: error in "id": can't parse "id"


The help indicates:
ggtest=> \h copy
Command: copy
Description: copy data to and from a table
Syntax:
        COPY [BINARY] class_name [WITH OIDS]
        TO|FROM filename|STDIN|STDOUT [USING DELIMITERS 'delim'];


I have tried WITH OIDS but with same results.

Is there somewhere that I can either enable the first line of CSV as
header names

OR

Can I explicitly define my import field ordering from within the select
statement?

Thanks

Terry Fielder
terry@greatgulfhomes.com




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: SQL copy from csv with explicit field ordering
@ 2001-03-02 16:10  Peter Eisentraut <peter_e@gmx.net>
  parent: Terry Fielder <terry@greatgulfhomes.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Peter Eisentraut @ 2001-03-02 16:10 UTC (permalink / raw)
  To: Terry Fielder <terry@greatgulfhomes.com>; +Cc: pgsql-sql

Terry Fielder writes:

> Is there somewhere that I can either enable the first line of CSV as
> header names
>
> OR
>
> Can I explicitly define my import field ordering from within the select
> statement?

No and no.  You will have to preprocess your file.  Something like this
will probably do:

#! /bin/sh
IFS=,
cat "inputfile" | \
while read COL1 COL2 COL3; do
    echo "$COL3,$COL2,$COL1" >> "outputfile"
done

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/




^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2001-03-02 16:10 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-01 20:46 SQL copy from csv with explicit field ordering Terry Fielder <terry@greatgulfhomes.com>
2001-03-02 16:10 ` Peter Eisentraut <peter_e@gmx.net>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox