Thread: Export CSV from psql
Hi, is there a way to display the table in CSV format or write a query in csv to a file ? Alex
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) >
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) > >> > >> > >> > > > > > > > > > > > >
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 >
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 >
--- 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
[sNip]
> 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
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.