Re: Export data to MS Excel

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(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Phoenix Kiula
> Sent: Saturday, September 01, 2007 5:24 AM
> To: Ashish Karalkar
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] Export data to MS Excel
>
>
> On 01/09/07, Ashish Karalkar
> <ashish(dot)karalkar(at)info-spectrum(dot)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
>