Export CSV from psql
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:
Show quoted text
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)
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:
Show quoted text
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.
alexCraig 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 ......
\oDon'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)
Import Notes
Reply to msg id not found: 3FBC7320.1060000@meerkatsoft.com | Resolved by subject fallback
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:
Show quoted text
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 .....
\oThis 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.
alexCraig 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 ......
\oDon'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?
[sNip]
I have been playing with this lately, and I have found that if you use
\f ,
\o /tmp/output.csvit opens fine in excel, it just bypasses the need to go
next, next, finish when opening from a TAB separated .txt file
Have you tested other Spreadsheet applications? If not, please feel
free to post both .TAB and .TXT files in a reply here in this newsgroup and
I'll try to load them up in Quattro Pro and post the results (and maybe
others who use different spreadsheet applications could do the same if they
have a little extra time to spare).
--
Randolf Richardson - rr@8x.ca
Inter-Corporate Computer & Network Services, Inc.
Vancouver, British Columbia, Canada
http://www.8x.ca/
This message originated from within a secure, reliable,
high-performance network ... a Novell NetWare network.
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:
Show quoted text
I have been playing with this lately, and I have found that if you use
\f ,
\o /tmp/output.csvit opens fine in excel, it just bypasses the need to go
next, next, finish when opening from a TAB separated .txt fileMatt
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 .....
\oThis 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.
alexCraig 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 ......
\oDon'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?---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
--- Craig O'Shannessy <craig@ucw.com.au> wrote:As it's very unlikely that there are tab's in your
strings, tab makes a
much safer field separator.
Probably, but I wouldn't bet the farm on it. I have
found plenty of instances of unexpected tabs,
especially with data generated from character-based
apps, where users tend to forget that they can't tab
from one field to another... There is no substitute
for checking.
__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree