pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedexport to excel format
7+ messages / 5 participants
[nested] [flat]
* export to excel format
@ 2003-04-11 06:35 Faizol Halim <faizol_h@yahoo.com>
0 siblings, 2 replies; 7+ messages in thread
From: Faizol Halim @ 2003-04-11 06:35 UTC (permalink / raw)
To: pgsql-interfaces
hi all,
first of all, I'm sorry if this is not the right
place to ask (if so, where should I direct the
question to?)
my question:
is there anyway that I could export some of my tables
to an excel format..? any module or pointers on how
this could be done..?
TIA
Faizol
__________________________________________________
Do you Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more
http://tax.yahoo.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: export to excel format
@ 2003-04-11 12:58 Jan Wieck <JanWieck@Yahoo.com>
parent: Faizol Halim <faizol_h@yahoo.com>
1 sibling, 1 reply; 7+ messages in thread
From: Jan Wieck @ 2003-04-11 12:58 UTC (permalink / raw)
To: Faizol Halim <faizol_h@Yahoo.com>; +Cc: pgsql-interfaces
Faizol Halim wrote:
>
> hi all,
> first of all, I'm sorry if this is not the right
> place to ask (if so, where should I direct the
> question to?)
> my question:
> is there anyway that I could export some of my tables
> to an excel format..? any module or pointers on how
> this could be done..?
Maybe, but what about "importing" the data directly?
Get the PostgreSQL ODBC driver
http://gborg.postgresql.org/project/psqlodbc/projdisplay.php
and install it on your Windows box. Add the ODBC support functions on
the database side. Make sure the Windows system has access to the
database (pg_hba.conf, username/password). Create a DSN and access the
database directly from Excel.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: export to excel format
@ 2003-04-11 13:06 Peter Mount <peter@retep.org.uk>
parent: Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 0 replies; 7+ messages in thread
From: Peter Mount @ 2003-04-11 13:06 UTC (permalink / raw)
To: Faizol Halim <faizol_h@Yahoo.com>; +Cc: pgsql-interfaces
Missed the original post, but Jakarta's POI can read/write excel from
java. Works pretty well, I'm using it on a project here.
Peter
--
Peter Mount
peter@retep.org.uk
http://www.retep.org.uk/
Tel/Fax: +44 (0) 1622 749439
Mobile: +44 (0) 7903 155887
US Fax: 1 435 304 5165
US Voice: 1 435 304 5165
IM-MSN: retep207@hotmail.com
IM-AOL/ICQ: retepworld
On Fri, 11 Apr 2003, Jan Wieck wrote:
> Faizol Halim wrote:
> >
> > hi all,
> > first of all, I'm sorry if this is not the right
> > place to ask (if so, where should I direct the
> > question to?)
> > my question:
> > is there anyway that I could export some of my tables
> > to an excel format..? any module or pointers on how
> > this could be done..?
>
> Maybe, but what about "importing" the data directly?
>
> Get the PostgreSQL ODBC driver
>
> http://gborg.postgresql.org/project/psqlodbc/projdisplay.php
>
> and install it on your Windows box. Add the ODBC support functions on
> the database side. Make sure the Windows system has access to the
> database (pg_hba.conf, username/password). Create a DSN and access the
> database directly from Excel.
>
>
> Jan
>
> --
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me. #
> #================================================== JanWieck@Yahoo.com #
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: export to excel format
@ 2003-04-11 14:29 Bruno Wolff III <bruno@wolff.to>
parent: Faizol Halim <faizol_h@yahoo.com>
1 sibling, 1 reply; 7+ messages in thread
From: Bruno Wolff III @ 2003-04-11 14:29 UTC (permalink / raw)
To: Faizol Halim <faizol_h@yahoo.com>; +Cc: pgsql-interfaces
On Thu, Apr 10, 2003 at 23:35:03 -0700,
Faizol Halim <faizol_h@yahoo.com> wrote:
> hi all,
> first of all, I'm sorry if this is not the right
> place to ask (if so, where should I direct the
> question to?)
> my question:
> is there anyway that I could export some of my tables
> to an excel format..? any module or pointers on how
> this could be done..?
If you output data as tab separated text files it will be very easy to
import the data into excel.
For a project I have where people that communicate with me use Windows
and excel is useful for combining different sets of data (so that
scripts can handle hundreds of similar data sets). To provide a way for
them to get data back to me I wrote a perl program that gets data from
the database and writes excel spreadsheets. It uses Spreadsheet::WriteExcel
which you can get from CPAN. It seems to be pretty slow. It takes about
3 minutes to produce a 4MB spreadsheet.
If you want to see the example to decide if it is something that might be
useful to you, you can look at:
http://wolff.to/area/excel.pl
A good chunk of the program is for handling the web part of things as the
sheets get generated on the fly (snail might be a better word) to satisfy
web requests.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: export to excel format
@ 2003-04-11 22:05 John McNamara <jmcnamara@cpan.org>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 7+ messages in thread
From: jmcnamara@cpan.org @ 2003-04-11 22:05 UTC (permalink / raw)
To: pgsql-interfaces
bruno@wolff.to (Bruno Wolff III) wrote:
> I wrote a perl program that gets data from
> the database and writes excel spreadsheets. It uses Spreadsheet::WriteExcel
> which you can get from CPAN. It seems to be pretty slow. It takes about
> 3 minutes to produce a 4MB spreadsheet.
Are you sure that the delay is caused by Spreadsheet::WriteExcel. The
following program produces a 4MB file in 3 seconds on a P700:
#!/usr/bin/perl -w
use strict;
use Spreadsheet::WriteExcel;
my $workbook = Spreadsheet::WriteExcel->new("4mb.xls");
my $worksheet = $workbook->addworksheet();
for my $row (0 .. 8_100) {
$worksheet->write($row, 0, "Test string " x 20);
$worksheet->write($row, 1, "Test string " x 20);
}
__END__
John.
--
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: export to excel format
@ 2003-04-14 01:05 Bruno Wolff III <bruno@wolff.to>
parent: John McNamara <jmcnamara@cpan.org>
0 siblings, 1 reply; 7+ messages in thread
From: Bruno Wolff III @ 2003-04-14 01:05 UTC (permalink / raw)
To: John McNamara <jmcnamara@cpan.org>; +Cc: pgsql-interfaces
On Fri, Apr 11, 2003 at 15:05:34 -0700,
John McNamara <jmcnamara@cpan.org> wrote:
> bruno@wolff.to (Bruno Wolff III) wrote:
>
> > I wrote a perl program that gets data from
> > the database and writes excel spreadsheets. It uses Spreadsheet::WriteExcel
> > which you can get from CPAN. It seems to be pretty slow. It takes about
> > 3 minutes to produce a 4MB spreadsheet.
>
>
> Are you sure that the delay is caused by Spreadsheet::WriteExcel. The
> following program produces a 4MB file in 3 seconds on a P700:
That runs in a couple of seconds here. There must be something else making
it a lot slower. Splitting the sheets out with ParseExcel takes about a
minute, so just having about four hundred worksheets may account for
a good chunk of the time if there is substantial per sheet overhead.
There also is a query per sheet that takes about a quarter of a second per
sheet. This would account for about a minute and a half right there.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: export to excel format
@ 2003-04-14 01:24 Bruno Wolff III <bruno@wolff.to>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 7+ messages in thread
From: Bruno Wolff III @ 2003-04-14 01:24 UTC (permalink / raw)
To: John McNamara <jmcnamara@cpan.org>; pgsql-interfaces
On Sun, Apr 13, 2003 at 20:05:28 -0500,
Bruno Wolff III <bruno@wolff.to> wrote:
> On Fri, Apr 11, 2003 at 15:05:34 -0700,
> John McNamara <jmcnamara@cpan.org> wrote:
> > bruno@wolff.to (Bruno Wolff III) wrote:
> >
> > > I wrote a perl program that gets data from
> > > the database and writes excel spreadsheets. It uses Spreadsheet::WriteExcel
> > > which you can get from CPAN. It seems to be pretty slow. It takes about
> > > 3 minutes to produce a 4MB spreadsheet.
> >
> >
> > Are you sure that the delay is caused by Spreadsheet::WriteExcel. The
> > following program produces a 4MB file in 3 seconds on a P700:
>
> That runs in a couple of seconds here. There must be something else making
> it a lot slower. Splitting the sheets out with ParseExcel takes about a
> minute, so just having about four hundred worksheets may account for
> a good chunk of the time if there is substantial per sheet overhead.
> There also is a query per sheet that takes about a quarter of a second per
> sheet. This would account for about a minute and a half right there.
I double checked and the query seems to now be taking about .6 seconds
for sheets on the bigger end. That would account for virtually all of
the time.
I had problems with a similar version of this query that slowed down a
lot in 7.3.1 or 7.3.2 when an incorrect optimization bug was fixed.
Moving to 7.4 development cut the time for at least the original version
in half. Looking at the explain results makes it seem there is some
hope for improvement as the plan doesn't look like it is taking full
advantage of available indexes. But I need to look at it some more to
see why that might be.
Thanks for pointing out that WriteExcel was not really the problem. I couldn't
have done much about that, but I think I have a chance to make the query run
significantly faster.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2003-04-14 01:24 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-11 06:35 export to excel format Faizol Halim <faizol_h@yahoo.com>
2003-04-11 12:58 ` Jan Wieck <JanWieck@Yahoo.com>
2003-04-11 13:06 ` Peter Mount <peter@retep.org.uk>
2003-04-11 14:29 ` Bruno Wolff III <bruno@wolff.to>
2003-04-11 22:05 ` John McNamara <jmcnamara@cpan.org>
2003-04-14 01:05 ` Bruno Wolff III <bruno@wolff.to>
2003-04-14 01:24 ` Bruno Wolff III <bruno@wolff.to>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox