Re: Export data to MS Excel - Mailing list pgsql-general

From Bill Bartlett
Subject Re: Export data to MS Excel
Date
Msg-id 0e9701c7ec9b$2aaf8360$1e9617ac@bartletthome.com
Whole thread Raw
In response to Re: Export data to MS Excel  ("Phoenix Kiula" <phoenix.kiula@gmail.com>)
List pgsql-general
For quick/simple table format reports, you can just use psql to create
the output in HTML format, then import that directly into Excel.  For
example, I use the following psql line to generate an HTML-format report
of server IP information; this file can then be directly opened in
Excel.  (Excel 2002 and later will open and parse most HTML format files
without even needing to import them -- just open the file.)

   psql -H -P tableattr='cellspacing=0 cellpadding=6' -P title='Server
IP Information' -f get_server_ip_info.sql -o get_server_ip_info.html

Alternatively, you can bury the formatting commands in the SQL file
itself -- this is handy if the formatting is longer or doesn't change,
or you want different formats or titles for different reports.  For
example:

 list_of_unsigned_images_report.sql:
     \pset format html
     \pset title 'List of Unsigned Images'
     \pset tableattr 'cellspacing=0 cellpadding=6'
     SELECT p.last_name, p.first_name, [blah blah blah]...

- Bill

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Phoenix Kiula
> Sent: Saturday, September 01, 2007 5:24 AM
> To: Ashish Karalkar
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Export data to MS Excel
>
>
> On 01/09/07, Ashish Karalkar
> <ashish.karalkar@info-spectrum.com> wrote:
> >
> >
> > Hello All,
> > I want to export data from PostgreSQL tables to MS Excel.
> > Is there any way?
>
>
>
> Sure, write SQL in a program (php, perl, jsp, asp) to dump
> the tables in HTML <table><tr><td> rows format. Then import
> that HTML page program into Excel from Tools --> Data
> Sources. Once you save the xls file, you can always just
> refresh the data because it already points to your program.
>
> I guess there must be other ways, but this one works fine for
> me. Excel has a limit of 65,700 rows (or thereabouts) so it's
> not a huge deal for a db like pgsql.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>



pgsql-general by date:

Previous
From: "chris smith"
Date:
Subject: Re: Bigtime scaling of Postgresql (cluster and stuff I suppose)
Next
From: Bill Moran
Date:
Subject: Re: Bigtime scaling of Postgresql (cluster and stuff I suppose)