Thread: 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.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 09/01/07 02:16, Ashish Karalkar wrote:
> Hello All,
> I want to export data from PostgreSQL tables to MS Excel.
> Is there any way?

Extract the data to a CSV (comma or tab) file.

http://www.postgresql.org/docs/
http://www.postgresql.org/docs/8.2/interactive/index.html
http://www.postgresql.org/docs/8.2/interactive/sql-copy.html

- --
Ron Johnson, Jr.
Jefferson LA  USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFG2TopS9HxQb37XmcRAi2oAJ0VGYEPQm0N/y1kYVW6a+eg8hfyyACg6AUz
B0u5gnYgPz8GyMIMH2hSe3g=
=l1VW
-----END PGP SIGNATURE-----

Ashish Karalkar wrote:

> Hello All,
> I want to export data from PostgreSQL tables to MS Excel.
> Is there any way?

ODBC is one way to do it.
Use the data import, that runs msquery

--
/Björn
b.f.lundin@gmail.com

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
>

--- 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?

Excel has the ability to directly pull data from any system ODBC DSN configured on the windows
box.   Was is nice with this method is the ability for the users to "Refresh" the exported data
directly from excel using the build in functionality of excel.

http://pgfoundry.org/projects/psqlodbc/
http://www.sls.psi.ch/controls/help/tutorials/Excel_ODBC/index.html

Regards,
Richard Broersma Jr.

On Saturday 01 September 2007 12:16 am, Ashish Karalkar wrote:
> Hello All,
> I want to export data from PostgreSQL tables to MS Excel.
> Is there any way?
>
> Thanks in advance...
>
> With Regrads
> Ashish...
One relatively easy way to do it is to use the Base component of OpenOffice.
You can dump data directly from a table to a spreadsheet. Just save as *.xls.
--
Adrian Klaver
aklaver@comcast.net