Thread: Exporting data from PostgreSQL

Exporting data from PostgreSQL

From
Michael Glaesemann
Date:
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


Re: Exporting data from PostgreSQL

From
"paul butler"
Date:
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)



Re: Exporting data from PostgreSQL

From
Michael Glaesemann
Date:
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


Re: Exporting data from PostgreSQL

From
Jeff Eckermann
Date:
--- 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

Re: Exporting data from PostgreSQL

From
Josh Berkus
Date:
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

Re: Exporting data from PostgreSQL

From
Jeff Eckermann
Date:
--- 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

Re: Exporting data from PostgreSQL

From
brew@theMode.com
Date:
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
 ==========================================================================



Re: Exporting data from PostgreSQL

From
Michael Glaesemann
Date:
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