SQL Database table to Perl Spreadsheet script

dinkar has asked for the wisdom of the Perl Monks concerning the following question:

I have a transaction table like this:

TranID  Date        AccNum   Type    Amount ChequeNo DDNo  
657520  02-07-1999  0181432  Debit   16000  465774     
657524  02-07-1999  0181432  Debit   13000           569086
657538  09-07-1999  0181432  Credit  11000  
657548  18-07-1999  0181432  Credit  15500  
657519  02-07-1999  0181432  Debit   12000  
657523  02-07-1999  0181432  Credit  11000  
657529  03-07-1999  0181433  Debit   15000  466777
657539  10-07-1999  0181433  Credit  10000  
657541  11-07-1999  0181433  Debit   12000  
657525  03-07-1999  0181433  Debit   15000           569999
657533  05-07-1999  0181433  Credit  12500
[download]

My question is: Query the data from transaction table and calculate the total amount debited by cheque, dd and cash for each account and store the result in a spreadsheet? My code is like this:

#!/usr/bin/perl
use strict;
use warnings;
use DBI; 
use Spreadsheet::WriteExcel;
my $dbh = DBI->connect('dbi:mysql:database:3306','prithvi','prithvi') 
+or die $dbh->errstr;
my $sth = $dbh->prepare("SELECT `AccNum`,`Type`,`Amount`,`ChequeNo`,`D
+DNo` FROM `transaction`");
$sth->execute or die $sth->errstr;
my $workbook = Spreadsheet::WriteExcel->new('query_result.xls');
my $worksheet = $workbook->add_worksheet();
my $row = 0;
my $col = 0;
my %h;
$worksheet->write_row($row++,$col,['Account Number','Cheque Debit','DD
+ Debit','Cash Debit']);
while(my @data = $sth->fetchrow_array)
{
 next unless($data[1] eq 'Debit');
 my $result = $data[3] ? "ChequeNo" : $data[4] ? "DDNo" : "Cash";
 $h{$data[0]}{$result} += $data[2];
 $worksheet->write_row($row++,$col,\@data);
}
$sth->finish;
$dbh->disconnect;
[download]

I am not getting the proper output. Where am I going wrong? Please help. Thanks in advance

Comment on SQL Database table to Perl Spreadsheet script Select or Download Code

Replies are listed 'Best First'.

Re: SQL Database table to Perl Spreadsheet script
by erix (Prior) on Oct 13, 2012 at 22:27 UTC

This question was answered already: 997371

[reply]

A reply falls below the community's threshold of quality. You may see it by logging in.

Re: SQL Database table to Perl Spreadsheet script
by marto (Cardinal) on Oct 14, 2012 at 09:11 UTC

"I am not getting the proper output."

It'd be helpful if you told us what output you do get. How do I post a question effectively?. Also you posted this question in the wrong section of the forum, again. Where should I post X?.

"Where am I going wrong? Please help."

You don't seem to be making an effort at debugging your code, some simple print statements would help you track down at which point you are going wrong. Basic debugging checklist, Debugging and Optimization, tutorials.

Update: are you also prithviraj who posted SQL Database Table to Perl Script?

[reply]

Re: SQL Database table to Perl Spreadsheet script
by Generoso (Prior) on Oct 14, 2012 at 08:02 UTC

Maybe this is what you are looking for.

my @sql =();
$sql[0] = qq[SELECT AccNum as Account, max(date) as Date, Type, count(
+*) as Total_No, sum(Amount) as Amount
FROM transaction GROUP BY AccNum, Type
union
SELECT AccNum, max(date),'Balance', count(*), sum(if(type='Debit',-1*A
+mount,Amount))
FROM transaction GROUP BY AccNum;];
$sql[1] = qq[SELECT AccNum as Account, max(date) as Date, Type, 'CHEQU
+ENO' as Subtype, count(*) as Total_No, sum(Amount) as Amount
FROM transaction where chequeno is not null GROUP BY AccNum, Type
union
SELECT AccNum as Account, max(date) as Date, Type, 'DDNO' as Subtype, 
+count(*) as Total_No, sum(Amount) as Amount
FROM transaction where DDNO is not null GROUP BY AccNum, Type;];
$sql[2] = qq[desc transaction;];
open (FH, ">".$ENV{"TMP"}."\\mytmp.html") || die "Cannot open temporar
+y file: $!\n";
print FH '<HTML><Body>';
foreach (@sql) {
    my $l = $_;
    $l =~ s/\n/<br>/g;
    print FH '<p>'.$l.'</p>';
    my $tran_cur = $dbh->prepare($_)or die "Cannot prepare: " . $DBI::
+errstr;
    $tran_cur->execute or die "Cannot execute: " . $DBI::errstr;
    print FH '<table border=1 cellspacing=1 cellpadding=2><tr>';
    map {print FH "<td>$_</td>"}@{$tran_cur->{NAME}};
    print FH "</tr><br/>";
    while( my @data = $tran_cur->fetchrow_array)
    {
        print FH "<tr>";
        map {print FH "<td>";
            my $r = defined ($_) ? $_ : '&nbsp;'; 
            print FH length ($r)>0 ? $r: '&nbsp;';
            print FH "</td>";}@data;
        print FH "</tr>";
    }
    print FH "</table>";
    }
print FH "</Body></HTML>\n";
my $url = "file://".$ENV{"TMP"}."\\mytmp.html";
my $commandline = qq{start "$url" "$url"};
system($commandline) == 0
    or die qq{Couldn't launch '$commandline': $!/$?};
close FH;    
$dbh->disconnect;
sleep(5);
unlink $ENV{"TMP"}."\\mytmp.html" or warn "Could not unlink ".$ENV{"TM
+P"}."\\mytmp.html: $!";
[download]

Result:

SELECT AccNum as Account, max(date) as Date, Type, count(*) as Total_No, sum(Amount) as Amount
FROM transaction GROUP BY AccNum, Type
union
SELECT AccNum, max(date),'Balance', count(*), sum(if(type='Debit',-1*Amount,Amount))
FROM transaction GROUP BY AccNum;

Account	Date	Type	Total_No	Amount
181432	1999-07-18	Credit	5	60000.00
181432	1999-07-11	Debit	6	83000.00
181432	1999-07-18	Balance	11	-23000.00

SELECT AccNum as Account, max(date) as Date, Type, 'CHEQUENO' as Subtype, count(*) as Total_No, sum(Amount) as Amount
FROM transaction where chequeno is not null GROUP BY AccNum, Type
union
SELECT AccNum as Account, max(date) as Date, Type, 'DDNO' as Subtype, count(*) as Total_No, sum(Amount) as Amount
FROM transaction where DDNO is not null GROUP BY AccNum, Type;

Account	Date	Type	Subtype	Total_No	Amount
181432	1999-07-03	Debit	CHEQUENO	2	31000.00
181432	1999-07-03	Debit	DDNO	2	28000.00

desc transaction;

Field	Type	Null	Key	Default	Extra
TranID	int(10) unsigned	NO	PRI
Date	date	NO
AccNum	int(10) unsigned	NO
Type	enum('Credit','Debit')	NO
Amount	decimal(10,2)	NO
ChequeNo	int(10) unsigned	YES
DDNo	int(10) unsigned	YES

[reply]
[d/l]

A reply falls below the community's threshold of quality. You may see it by logging in.

Re: SQL Database table to Perl Spreadsheet script
by Generoso (Prior) on Oct 14, 2012 at 09:45 UTC

All you need is to change the HTML code for OLE excel code.

#!/usr/bin/perl

use DBI;
use strict;
use warnings;
use OLE;
use Win32::OLE::Const "Microsoft Excel";
my $dbh = DBI->connect('dbi:mysql:database:3306','prithvi','prithvi') 
+or die "Cannot connect: $DBI::errstr\n";
my @sql =();
$sql[0] = qq[SELECT AccNum as Account, max(date) as Date, Type, count(
+*) as Total_No, sum(Amount) as Amount
FROM transaction GROUP BY AccNum, Type
union
SELECT AccNum, max(date),'Balance', count(*), sum(if(type='Debit',-1*A
+mount,Amount))
FROM transaction GROUP BY AccNum;];
$sql[1] = qq[SELECT AccNum,Type,Amount,ChequeNo,DDNo FROM transaction;
+];
$sql[2] = qq[desc transaction;];
my $excel = CreateObject OLE "Excel.Application";
$excel -> {Visible} = 1;
my $workbook = $excel    -> Workbooks -> Add;
my $i =0;
foreach (@sql) { $i++;
    my $sheet    = $workbook -> Worksheets("Sheet$i");
    $sheet                -> Activate;
    my $row = 1;
    my $col = 1;
    my $l = $_;
    $l =~ s/\n/<br>/g;
    print ' '.$l.'</p>';
    my $tran_cur = $dbh->prepare($_)or die "Cannot prepare: " . $DBI::
+errstr;
    $tran_cur->execute or die "Cannot execute: " . $DBI::errstr;
    foreach (@{$tran_cur->{NAME}})
        {$sheet->Cells($row,$col++)->{'Value'} = $_;}
    $col = 1;
    while( my @data = $tran_cur->fetchrow_array)
        {$row++;
        foreach (@data)
            { $sheet->Cells($row,$col++)->{'Value'} = $_;}
        $col = 1;
    }
}
$dbh->disconnect;
#$workbook -> SaveAs ("C:\\file_name.xls");
#$excel    -> Quit;
[download]

[reply]
[d/l]


We don't bite newbies here... much
	PerlMonks