Sending SQL Queries output to different Excel sheets

Shell Programming and Scripting
1

Hi,

I need your help in sedning sql queries output to different excel sheets.

My requirement is like this:

Query1: Select name from table1 where status = 'Complete'
Query2: Select name from table1 where status = 'Failed'
Query3: Select name from table1 where status = 'Ignored'

When i execute these queries, i want output of Query1 to be on sheet1 and output of query2 to be on sheet2 and output of query3 on sheet3 of an excel file.

Not sure whether it can be achieved using sqlplus.

Could some one please help me in this.

Thanks .
Parvathi

2

You can do that in perl using Simple::Excel

3

Hi dinjo_jo,

Thanks for your inputs.
Could you please tell me the procedure to do that?
sorry for asking. I know perl but not to this extent.

Once again thanks.

Parvathi.

4

I have being doing it in sybase using perl.

use Spreadsheet::WriteExcel;
use Spreadsheet::WriteExcel::Worksheet;
use Sybase::DBlib;

my @results;

my $sql = "select *"
my $dbh = Sybase::DBlib->new($user , $passwd , $server);
if (defined $dbh)
{
@results = $dbh->nsql($sql , "HASH");
}
else
{
print "No DB Connection found for $server";
}

my $ebook = Spreadsheet::WriteExcel->new(file);
my $esheet = $ebook->addworksheet($title);
my $format = $ebook->addformat();
my $formatheader = $ebook->addformat();
my $formatcolumn = $ebook->addformat();

$formatheader->set_size(20);
$formatheader->set_font('Verdana');
$formatheader->set_color('brown');
$formatheader->set_align('center');
$formatheader->set_bold();
$formatheader->set_merge();

$formatcolumn->set_size(10);
$formatcolumn->set_font('Verdana');
$formatcolumn->set_color('navy');
$formatcolumn->set_align('center');
$formatcolumn->set_bold();
$formatcolumn->set_italic();

$format->set_color('black');
$format->set_font('Verdana');
$format->set_align('center');

$esheet->write_blank(0,0,$formatheader);
$esheet->write_blank(0,1,$formatheader);
$esheet->write_blank(0,2,$formatheader);
$esheet->write_blank(0,3,$formatheader);
$esheet->set_column(0, 2, 25,$formatheader);

$esheet->set_column(2, 0, 25,$formatcolumn);
$esheet->set_column(2, 1, 25,$formatcolumn);
$esheet->set_column(2, 2, 25,$formatcolumn);
$esheet->set_column(2, 3, 25,$formatcolumn);
$esheet->set_column(2, 4, 25,$formatcolumn);

my $o = 0;
foreach my $values (@results)
{
$esheet->set_column(3,$o, 25,$format);
$esheet->write($i,0,"$values->{'Branches_ShortName'}",$format);
$esheet->write($i,1,"$values->{'Portfolios_ShortName'}",$format);
$esheet->write($i,2,"$values->{'Portfolios_Name'}",$format);
$esheet->write($i,3,"$values->{'Folders_ShortName'}",$format);
$esheet->write($i,4,"$values->{'Folders_Name'}",$format);
$i++;
$o++;
}

$ebook->close();

5

Thanks dinjo_jo.

Will use this to get the results i am looking for.

Thanks alot...You have been very helpful.

Parvathi.