----- Original Message -----
From: "NIPP, SCOTT V (SBCSI)" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, October 02, 2002 12:04 AM
Subject: Database to Excel script...
> I have a MySQL database that I have a few Perl scripts working on.
> I am now looking into doing some reporting on these database tables. I
> would really like to find a script that queries the database and the uses
> the Spreadsheet::WriteExcel module to output the data directly to an Excel
> file. Does anyone out there have something like this that I could
> cannibalize? Thanks in advance.
Well, it has been already solved this problem,.
[yet another answer...]
use strict;
use DBI;
my $hDbM = DBI->connect('DBI:mysql:test', undef, undef,
{RaiseError=>1, AutoCommit=>1});
my $hDbE = DBI->connect
('DBI:Excel:file=dummy.xls', undef, undef,
{RaiseError=>1, AutoCommit=>1});
my $hStMt = $hDbM->prepare('SHOW TABLES');
$hStMt->execute();
my $raMt;
while($raMt=$hStMt->fetchrow_arrayref()) {
my $sTbl = $raMt->[0];
print "-- $sTbl\n";
my $hStMs = $hDbM->prepare("SELECT * FROM $sTbl");
$hStMs->execute();
my ($hStEi, $raMs);
$hDbE->do(
"CREATE TABLE $sTbl (".
join(",\n", map { $_ . ' text' } @{$hStMs->{NAME}}) .
')');
$hStEi = $hDbE->prepare(
"INSERT INTO $sTbl VALUES(" .
join(',', ('?') x $hStMs->{NUM_OF_FIELDS}) .
')');
while($raMs = $hStMs->fetchrow_arrayref()) {
$hStEi->execute(@$raMs);
}
$hStMs->finish();
}
$hDbM->disconnect;
$hDbE->func('dump.xls', 'save');
$hDbE->disconnect;
==============================================
Kawai, Takanori(Hippo2000)
Mail: [EMAIL PROTECTED] [EMAIL PROTECTED]
http://member.nifty.ne.jp/hippo2000/index_e.htm
http://www.hippo2000.info/cgi-bin/KbWikiE/KbWiki.pl
==============================================
