Re: Export CSV from psql - Mailing list pgsql-general

Hi Matt,

This will work fine as long as you are SURE that there are no comma's in
your strings.

cop=# \f ,
Field separator is ",".
cop=# \t
cop=# \a
cop=# select '1,','2';
1,,2

As you can see, this makes two fields look like three.

As it's very unlikely that there are tab's in your strings, tab makes a
much safer field separator.

Craig

On Mon, 24 Nov 2003, Matthew wrote:

> I have been playing with this lately, and I have found that if you use
>
> \f ,
> \o /tmp/output.csv
>
> it opens fine in excel,  it just bypasses the need to go
> next, next, finish when opening from a TAB separated .txt file
>
> Matt
>
> Craig O'Shannessy wrote:
> > Yeah, if you change the field separator to TAB, it will import cleanly
> > into excel.
> >
> > Try this
> >
> > --   \f [STRING]    show or set field separator for unaligned query output
> > -- Note, to put in a TAB in psql, you will need to quote it, and put it
> > -- in using Ctrl-V TAB (Ctrl-V tells the readline library to not interpret
> > -- the next character I think, so you can use it to insert newlines and
> > -- tabs etc)
> >
> > \f '      '
> > \a
> > \t
> > \o outputfile.txt
> > select .....
> > \o
> >
> > This file should cleanly import into excel, excel even defaults to TAB
> > when you open it, you just say open, then next,next,finish.
> >
> > Craig
> >
> > On Thu, 20 Nov 2003, Alex wrote:
> >
> >
> >>you are right with the commas as delimiters , just sometimes you want to
> >>export it directly as csv for import into excel.
> >>
> >>thanks , got it now with your hints.
> >>alex
> >>
> >>Craig O'Shannessy wrote:
> >>
> >>
> >>>I use the standard pipe signs as delimiters, comma's are a pretty silly
> >>>delimiter if you ask me.  Try putting this at the top of your psql query
> >>>
> >>>--   \a             toggle between unaligned and aligned output mode
> >>>--   \t             show only rows (currently off)
> >>>--   \o [FILE]      send all query results to file or |pipe
> >>>\a
> >>>\t
> >>>\o /tmp/outputfile.txt
> >>>select ......
> >>>\o
> >>>
> >>>Don't know if this helps you, or if you really need CSV.  Someone probably
> >>>has written a CSV export I 'spose.
> >>>
> >>>Best of luck.
> >>>
> >>>Craig
> >>>
> >>>
> >>>On Thu, 20 Nov 2003, Alex wrote:
> >>>
> >>>
> >>>
> >>>
> >>>>Hi,
> >>>>is there a way to display the table in CSV format or write a query in
> >>>>csv to a file ?
> >>>>
> >>>>Alex
> >>>>
> >>>>
> >>>>
> >>>>---------------------------(end of broadcast)---------------------------
> >>>>TIP 2: you can get off all lists at once with the unregister command
> >>>>   (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >>>>
> >>>>
> >>>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>
> >>
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> >                http://archives.postgresql.org
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>