Thread: Exporting data from PostgreSQL
Hello! I've been enjoying getting to know PostgreSQL and am happy that my queries seem to be working well. I've also been getting along in PHP and have been able to impress my boss (always a good thing!) with an easy way for her to manage some of her data using just a browser. Browsers are great, but sometimes you need hard copy, and often something other than printing the browser window. So I'd like to be able to get query results out of PostgreSQL somehow, hopefully in a tab-delimited file. I'm sure this is something pretty easy and I just haven't hit upon it. I've googled and come across EMS' PostgreSQL Export. Sounds like a great product, but I'm on a Mac, am not sure what's involved with using the Linux version on OS X and am not too keen on trying, and I don't think I need something so fancy. This is probably a pretty common task, so there must be a way to do it, I'd think Things I've thought of: COPY: Only works with tables, not views (from what I've read). Seems like a bit of a hassle to create a short-lived table to select into just to get some data out, but maybe that's what I should do. PHP (or other language): Make a script that'll do this for me. Can be used with query results. I'm probably missing something. I'd love to hear any ideas, including finding out if I'm just too lazy to use the two I've already thought of! :) Thanks any advice! Michael Glaesemann grzm myrealbox com
Date sent: Fri, 13 Jun 2003 15:20:59 +0900 Subject: [NOVICE] Exporting data from PostgreSQL From: Michael Glaesemann <grzm@myrealbox.com> To: pgsql-novice@postgresql.org Try This, Go into psql mfx=# \pset fieldsep '\t' // sets field delimiter to tab Field separator is ' '. mfx=# select * from actor; // make your query mfx=# \o d:\A\tester.txt // set file to output results mfx=# select * from actor; // execute query open up resulting test file in excel, and then do with it what you will. Line breaks in long test fields are broken up over rows, but neatly. There is also a php class (with varying licenses) to create excel sheets http://www.web-aware.com/ Remember a few problems with text fields, but it did the job well enough Hope this helps Paul Butler > Hello! > > I've been enjoying getting to know PostgreSQL and am happy that my > queries seem to be working well. I've also been getting along in PHP > and have been able to impress my boss (always a good thing!) with an > easy way for her to manage some of her data using just a browser. > > Browsers are great, but sometimes you need hard copy, and often > something other than printing the browser window. So I'd like to be > able to get query results out of PostgreSQL somehow, hopefully in a > tab-delimited file. > > I'm sure this is something pretty easy and I just haven't hit upon it. > I've googled and come across EMS' PostgreSQL Export. Sounds like a > great product, but I'm on a Mac, am not sure what's involved with using > the Linux version on OS X and am not too keen on trying, and I don't > think I need something so fancy. This is probably a pretty common task, > so there must be a way to do it, I'd think > > Things I've thought of: > COPY: Only works with tables, not views (from what I've read). Seems > like a bit of a hassle to create a short-lived table to select into > just to get some data out, but maybe that's what I should do. > PHP (or other language): Make a script that'll do this for me. Can be > used with query results. > > I'm probably missing something. I'd love to hear any ideas, including > finding out if I'm just too lazy to use the two I've already thought > of! :) > > Thanks any advice! > > Michael Glaesemann > grzm myrealbox com > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
On Friday, Jun 13, 2003, at 16:38 Asia/Tokyo, paul butler wrote: > Go into psql > > mfx=# \pset fieldsep '\t' // sets field delimiter to tab > Field separator is ' '. > mfx=# select * from actor; // make your query > mfx=# \o d:\A\tester.txt // set file to output results > mfx=# select * from actor; // execute query Thanks, Paul! That is exactly what I was looking for. I was able to make a file! :) Though, for some reason still unknown to me, it wasn't tab-delimited, even though I entered the commands above and I got back the "Field separator is ' '." line. It came out the standard pipes and dashes, though that's easily cleaned up in BBEdit. I'll do a bit more reading about psql and see if I can't work it out. One question. Is there a reason why the query is included twice, once before and once after the \o line? I tried it omitting the first query and the results seemed just fine. Thanks again for your help, Paul! Michael Glaesemann grzm myrealbox com
--- Michael Glaesemann <grzm@myrealbox.com> wrote: > > On Friday, Jun 13, 2003, at 16:38 Asia/Tokyo, paul > butler wrote: > > Go into psql > > > > mfx=# \pset fieldsep '\t' // sets field > delimiter to tab > > Field separator is ' '. > > mfx=# select * from actor; // make your query > > mfx=# \o d:\A\tester.txt // set file to output > results > > mfx=# select * from actor; // execute query > > Thanks, Paul! That is exactly what I was looking > for. I was able to > make a file! :) > Though, for some reason still unknown to me, it > wasn't tab-delimited, > even though I entered the commands above and I got > back the "Field > separator is ' '." line. It came out the standard > pipes and dashes, I have done this kind of exporting a lot (really functional if you are using Samba, although AFAIK that only works with Windows?). I have never been able to make the "\pset fieldsep '\t'" work correctly either. I usually work around the problem by specifying a literal tab, using the Ctrl-V/Ctrl-I key combination. Another thing you may want to do: also issue the "\a" psql command, which eliminates the space padding normally used on output to align the result rows for display. The existence of superfluous spaces in your output may or may not matter for whatever you are doing (evidently not, so far), but I find it safer to just eliminate the possibility of error. You can also use "\t" to eliminate the headings etc. if you want only the data. > though that's easily cleaned up in BBEdit. I'll do a > bit more reading > about psql and see if I can't work it out. > > One question. Is there a reason why the query is > included twice, once > before and once after the \o line? I tried it > omitting the first query > and the results seemed just fine. The only reason that I can see is that you have the opportunity to test the output before sending it to a file. __________________________________ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com
Jeff, > I have done this kind of exporting a lot (really > functional if you are using Samba, although AFAIK that > only works with Windows?). Er, no ... Samba was written on Linux. What did you mean to say? There's also some 3rd-party tools out there: The current beta of PGAccess (www.pgaccess.org) includes export of query results, which works on some platforms. EMS Hitech (www.ems-hitech.com) has a GUI tool called "PgExport" for windows and Linux which will dump any table (support for queries and views is forthcoming) to a variety of formats: XLS, Text, PDF, XML, etc., with a high degree of customizability. -- Josh Berkus Aglio Database Solutions San Francisco
--- Josh Berkus <josh@agliodbs.com> wrote: > Jeff, > > > I have done this kind of exporting a lot (really > > functional if you are using Samba, although AFAIK > that > > only works with Windows?). > > Er, no ... Samba was written on Linux. What did you > mean to say? I meant, from the workstation side ... It's pretty cool to be able to save a file from a Windows workstation direct to a *nix directory, import it (perhaps with minimal editing) to PostgreSQL, crunch it with SQL (perhaps cross referencing to already-stored data), dump it straight out again, open it up directly in Excel and polish away... I think you get the picture. A very powerful model for user driven data analysis, and how I used to make my living not long ago. > > There's also some 3rd-party tools out there: > > The current beta of PGAccess (www.pgaccess.org) > includes export of query > results, which works on some platforms. > > EMS Hitech (www.ems-hitech.com) has a GUI tool > called "PgExport" for windows > and Linux which will dump any table (support for > queries and views is > forthcoming) to a variety of formats: XLS, Text, > PDF, XML, etc., with a high > degree of customizability. Not included in my oblique reference, but useful tools, I agree. __________________________________ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com
Michael....
Just going through some old email, so maybe you're way beyond needing a
response now, but:
> just to get some data out, but maybe that's what I should do.
> PHP (or other language): Make a script that'll do this for me. Can be
> used with query results.
>
> I'm probably missing something. I'd love to hear any ideas, including
> finding out if I'm just too lazy to use the two I've already thought
> of! :)
You could query and build up a string variable in php (using heredoc to
format the results as you want) then use the php mail() function to send
it out via email.
Not the most elegant, but it's one way.
brew
==========================================================================
Strange Brew (brew@theMode.com)
Check out my Musician's Online Database Exchange (The MODE Pages)
http://www.TheMode.com
==========================================================================
On Sunday, Jun 15, 2003, at 23:03 Asia/Tokyo, brew@theMode.com wrote: > Just going through some old email, so maybe you're way beyond needing a > response now, but: > >> just to get some data out, but maybe that's what I should do. >> PHP (or other language): Make a script that'll do this for me. Can be >> used with query results. >> >> I'm probably missing something. I'd love to hear any ideas, including >> finding out if I'm just too lazy to use the two I've already thought >> of! :) > > You could query and build up a string variable in php (using heredoc to > format the results as you want) then use the php mail() function to > send > it out via email. > > Not the most elegant, but it's one way. Likewise catching up and cleaning out the mailboxes. I've since implemented a similar solution using PHP to build up a string and pass it to a user via a webpage. Works well for handing data off to people who don't need or have access to the "database proper". Thanks for the response! Michael