Re: Oracle to Excel

From: <Jared.Still_at_radisys.com>
Date: Wed, 06 Nov 2002 11:46:34 -0800
Message-ID: <F001.004FD0CB.20021106114634@fatcity.com>

If you can live with dumping to a comma delimited file, just do that and import to Excel.

If you want to write directly to an Excel format, you could do that with Perl. Have any Perl programmers around?

Here's an example of one. Not really too much code.

Jared

---

#!/home/oracle/perl/bin/perl

# template for DBI programs

use warnings;
use FileHandle;
use DBI;
use strict;
use Spreadsheet::WriteExcel;
use Spreadsheet::WriteExcel::Big;
use Getopt::Long;
use constant LINES_PER_BOOK => 60001;

# flush buffers
$|++;

my %optctl = ();

Getopt::Long::GetOptions(

        \%optctl,
        "database=s",
        "username=s",
        "password=s",
        "sysdba!",
        "sysoper!",
        "z","h","help");

my($db, $username, $password, $connectionMode);

$connectionMode = 0;

if ( $optctl{sysoper} ) { $connectionMode = 4 } if ( $optctl{sysdba} ) { $connectionMode = 2 }

if ( ! defined($optctl{database}) ) {

        Usage();
        die "database required\n";

}
$db=$optctl{database};

if ( ! defined($optctl{username}) ) {

        Usage();
        die "username required\n";

}

$username=$optctl{username};
$password = $optctl{password};

my $dbh = DBI->connect(

        'dbi:Oracle:' . $db,
        $username, $password,
        {
                RaiseError => 1,
                AutoCommit => 0,
                ora_session_mode => $connectionMode
        }
        );

die "Connect to $db failed \n" unless $dbh;

$dbh->{RowCacheSize} = 100;

my $sql=q{

        select *
        from myschema.mytable
        --where rownum < 10001
        where year = '2000' -- year
        and month = '01' -- month
        order by year, month

};

print "Preparing SQL\n";

my $sth = $dbh->prepare($sql);

print "Executing SQL\n";

$sth->execute;

print "Creating Workbook\n";

my $workbook = Spreadsheet::WriteExcel::Big->new(newWorkBookName()); die "unable to create workbook - $!\n" unless $workbook;
$workbook->set_tempdir('/u03/tmp');

my $worksheet = $workbook->addworksheet();

my $colNames = $sth->{NAME_uc};

print "Fetching data\n";

my $rowCount=0;
my $lineCount=0;
$worksheet->write_row($lineCount,0,$colNames);
print "\n";

while( my $ary = $sth->fetchrow_arrayref ) {

        print "." unless $rowCount++%1000;

        if ( ++$lineCount >= LINES_PER_BOOK ) {
                $workbook->close;
                my $workBookName = newWorkBookName();
                $workbook  = 
Spreadsheet::WriteExcel::Big->new($workBookName);
                die "unable to create workbook - $!\n" unless $workbook;
                $worksheet = $workbook->addworksheet();
                $lineCount=0;
                $worksheet->write_row($lineCount,0,$colNames);
                $lineCount=1;
                print "\nNew Workbook: $workBookName\n";
        }
        $worksheet->write_row($lineCount,0,$ary);

}

print "\n";

$workbook->close;
$sth->finish;
$dbh->disconnect;

sub Usage {

        print "\n";
        print "usage:  we.pl\n";
        print "    we.pl -database dv07 -username scott -password tiger 
[-sysdba || -sysoper]\n";
        print "\n";

}

{

my $workBookNumber = 0;
sub newWorkBookName {

        return "/u01/tmp/excel_dump_" . ++$workBookNumber . ".xls"; }

}

"Burton, Laura L." <BurtonL_at_prismplus.com> Sent by: root_at_fatcity.com
 11/06/2002 10:48 AM
 Please respond to ORACLE-L  

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        Oracle to Excel

I think I have seen traffic concerning the extracting of data from Oracle into an Excel spreadsheet. We now have a need for this. Could anyone enlighten me?
Thank you in advance.
Laura

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Jared.Still_at_radisys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Nov 06 2002 - 13:46:34 CST