Re: COPY command - CSV files

I wish modify COPY command for support of CSV files (dump/load files in
CSV format).
I think this option is very important from import data from spreedsheet
as OpenOffice/calc or M$/excel.
I have found this task in TODO list, also.

I've begin my work, modify COPY syntax in:

COPY tablename [ ( column [, ...] ) ]
FROM { 'filename' | STDIN }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ [ OPTIONALLY ] ENCLOSED [ BY ] 'delimiter1' [ AND
'delimiter2' ] ]
[ NULL [ AS ] 'null string' ] ]

COPY tablename [ ( column [, ...] ) ]
TO { 'filename' | STDOUT }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ ENCLOSED [ BY ] 'delimiter1' [ AND 'delimiter2' ] ]
[ NULL [ AS ] 'null string' ] ]

Syntax is like to control-file of Oracle's utility sql*load.
Enclosed define the first and second delimiters (if are different)
which surround each field.
The delimiters may be optionally if keyword exists.

At soon!
Umberto Zappi.

It's been done already.

see http://developer.postgresql.org/todo.php and
http://developer.postgresql.org/docs/postgres/sql-copy.html

cheers

andrew

Umberto Zappi wrote:

> I wish modify COPY command for support of CSV files (dump/load files in
> CSV format).
> I think this option is very important from import data from spreedsheet
> as OpenOffice/calc or M$/excel.
> I have found this task in TODO list, also.
>
> I've begin my work, modify COPY syntax in:
>
> COPY tablename [ ( column [, ...] ) ]
> FROM { 'filename' | STDIN }
> [ [ WITH ]
> [ BINARY ]
> [ OIDS ]
> [ DELIMITER [ AS ] 'delimiter' ]
> [ [ OPTIONALLY ] ENCLOSED [ BY ] 'delimiter1' [ AND
> 'delimiter2' ] ]
> [ NULL [ AS ] 'null string' ] ]
>
> COPY tablename [ ( column [, ...] ) ]
> TO { 'filename' | STDOUT }
> [ [ WITH ]
> [ BINARY ]
> [ OIDS ]
> [ DELIMITER [ AS ] 'delimiter' ]
> [ ENCLOSED [ BY ] 'delimiter1' [ AND 'delimiter2' ] ]
> [ NULL [ AS ] 'null string' ] ]
>
> Syntax is like to control-file of Oracle's utility sql*load.
> Enclosed define the first and second delimiters (if are different)
> which surround each field.
> The delimiters may be optionally if keyword exists.
>
> At soon!
> Umberto Zappi.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>

Umberto Zappi wrote:
> I wish modify COPY command for support of CSV files (dump/load files in
> CSV format).
> I think this option is very important from import data from spreedsheet
> as OpenOffice/calc or M$/excel.
> I have found this task in TODO list, also.
>
> I've begin my work, modify COPY syntax in:
>
> COPY tablename [ ( column [, ...] ) ]
> FROM { 'filename' | STDIN }
> [ [ WITH ]
> [ BINARY ]
> [ OIDS ]
> [ DELIMITER [ AS ] 'delimiter' ]
> [ [ OPTIONALLY ] ENCLOSED [ BY ] 'delimiter1' [ AND
> 'delimiter2' ] ]
> [ NULL [ AS ] 'null string' ] ]
>
> COPY tablename [ ( column [, ...] ) ]
> TO { 'filename' | STDOUT }
> [ [ WITH ]
> [ BINARY ]
> [ OIDS ]
> [ DELIMITER [ AS ] 'delimiter' ]
> [ ENCLOSED [ BY ] 'delimiter1' [ AND 'delimiter2' ] ]
> [ NULL [ AS ] 'null string' ] ]
>
> Syntax is like to control-file of Oracle's utility sql*load.
> Enclosed define the first and second delimiters (if are different)
> which surround each field.
> The delimiters may be optionally if keyword exists.

I guess you didn't notice that the TODO item has a dash next it, meaning
it is done and will be in 7.5. We didn't use Oracle's syntax, but we do
allow for the escape character in the quotes to be specified if different:

COPY tablename [ ( column [, ...] ) ] ]' ]
FROM { 'filename' | STDIN }elimiter' ] ...] ]
[ [ WITH ] S ] AS ] 'null string' ]' ]
[ BINARY ] [ AS ] 'delimiter' ] ...] ]
[ OIDS ] AS ] 'null string' ]' ]
[ DELIMITER [ AS ] 'delimiter' ] ...] ]
[ NULL [ AS ] 'null string' ]' ]
[ CSV [ QUOTE [ AS ] 'quote' ] , ...] ]
[ ESCAPE [ AS ] 'escape' ]
[ FORCE NOT NULL column [, ...] ]
TO { 'filename' | STDOUT }delimiter' ]

COPY tablename [ ( column [, ...] ) ] ]' ]
TO { 'filename' | STDOUT }delimiter' ]
[ [ WITH ] S ] AS ] 'null string' ]' ]
[ BINARY ]R [ AS ] 'delimiter' ]
[ OIDS ] AS ] 'null string' ]' ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]' ]
[ CSV [ QUOTE [ AS ] 'quote' ]
[ ESCAPE [ AS ] 'escape' ]

One interesting idea we had was for ,, to be null, and ,"", to be a
zero-length string. You can control that with FORCE NOT NULL,
meaning ,, is a zero-length string too.

To get the full details, see the current docs on the developers web
page.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Sun, May 02, 2004 at 17:03:35 +0200,
Umberto Zappi <uzappi(at)inwind(dot)it> wrote:
> I wish modify COPY command for support of CSV files (dump/load files in
> CSV format).
> I think this option is very important from import data from spreedsheet
> as OpenOffice/calc or M$/excel.
> I have found this task in TODO list, also.

A lot of work has already been done on this. You should read through
the archives. I think most of the discussion was roughly a month ago.

I believe this has already been implemented in CVS...

Chris

Umberto Zappi wrote:

> I wish modify COPY command for support of CSV files (dump/load files in
> CSV format).
> I think this option is very important from import data from spreedsheet
> as OpenOffice/calc or M$/excel.
> I have found this task in TODO list, also.

Thanks to everybody has reply to my email.
Stop immediatly my work in progress.

Some days ago I've downloaded version 7.4.3 of postgresql and I've begin
to work over without know other jobs of other developers :-o

Bye
Umberto

Umberto Zappi wrote:

> I wish modify COPY command for support of CSV files (dump/load files in
> CSV format).
> I think this option is very important from import data from spreedsheet
> as OpenOffice/calc or M$/excel.
> I have found this task in TODO list, also.
>
> [...]
>
>

On Fri, May 07, 2004 at 12:31:51AM +0200, Umberto Zappi wrote:
> Thanks to everybody has reply to my email.
> Stop immediatly my work in progress.
>
> Some days ago I've downloaded version 7.4.3 of postgresql and I've begin
> to work over without know other jobs of other developers :-o

You should really get the CVS code if you want to hack on Postgres ...
there are a lot of changes since 7.4.3.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Jason Tesser: You might not have understood me or I am not understanding you.
Paul Thomas: It feels like we're 2 people divided by a common language...

Hi Umberto,

If you are interested in doing any development work on PostgreSQL, you
_really_ need to work from the CVS version :)

Chris

Umberto Zappi wrote:

> Thanks to everybody has reply to my email.
> Stop immediatly my work in progress.
>
> Some days ago I've downloaded version 7.4.3 of postgresql and I've begin
> to work over without know other jobs of other developers :-o
>
> Bye
> Umberto
>
>
> Umberto Zappi wrote:
>
>> I wish modify COPY command for support of CSV files (dump/load files in
>> CSV format).
>> I think this option is very important from import data from spreedsheet
>> as OpenOffice/calc or M$/excel.
>> I have found this task in TODO list, also.
>>
>> [...]
>>
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Alvaro Herrera wrote:

> On Fri, May 07, 2004 at 12:31:51AM +0200, Umberto Zappi wrote:
>
>>Thanks to everybody has reply to my email.
>>Stop immediatly my work in progress.
>>
>>Some days ago I've downloaded version 7.4.3 of postgresql and I've begin
>>to work over without know other jobs of other developers :-o
>
>
> You should really get the CVS code if you want to hack on Postgres ...
> there are a lot of changes since 7.4.3.

7.4.3 is not out yet, even I don't find the TAG on CVS.

Regards
Gaetano Mendola

On Fri, May 07, 2004 at 03:09:58PM +0200, Gaetano Mendola wrote:
> Alvaro Herrera wrote:
>
> >On Fri, May 07, 2004 at 12:31:51AM +0200, Umberto Zappi wrote:
> >
> >>Thanks to everybody has reply to my email.
> >>Stop immediatly my work in progress.
> >>
> >>Some days ago I've downloaded version 7.4.3 of postgresql and I've begin
> >>to work over without know other jobs of other developers :-o
> >
> >You should really get the CVS code if you want to hack on Postgres ...
> >there are a lot of changes since 7.4.3.
>
> 7.4.3 is not out yet, even I don't find the TAG on CVS.

Duh, isn't it? I haven't been paying attention to releases :-) But I
meant what's the current CVS tip for the 7.4 branch. Anyway there's a
lot of changes from there to the current CVS HEAD tip.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Aprender sin pensar es inútil; pensar sin aprender, peligroso" (Confucio)