CODEWITHSUNDEEP
sqlperlspreadsheet
user1708526
user1708526

Reputation: 19

SQL Database to Perl Spreadsheet

I have a 'transaction' table created in SQL 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

The question is: Query data from transaction table and calculate total amount debited by cheque, dd and by cash for each account and store the result in a spreadsheet. My script 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`,`DDNo` 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;

I am not getting the proper output. Where am I going wrong? Please help. Thanks in advance. I have not got the answer for this question i.e., mainly storing the result in a spreadsheet. Please do not close this before answering. This is a very kind request to all of you.

Upvotes: 0

Views: 2811

Answers (2)

Justin
Justin

Reputation: 9724

   SELECT
    t1.AccountNumber, 
    SUM(t1.Amount)-(SELECT SUM(t2.Amount) from transaction t2 
     where t2.Type = 'Credit' 
     AND t2.AccountNumber = t1.AccountNumber) AS Subtraction
    from transaction t1
    WHERE t1.Type = 'Debit'
    group by t1.AccountNumber

Result:

ACCOUNTNUMBER   SUBTRACTION
016901581432    2500
016901581433    6000
016901581434    14500

SQLFIDDLE example edited.

Upvotes: 2

dan1111
dan1111

Reputation: 6566

You appear to have a good handle on how to interface to the database and write an SQL query. Now you just need to get the results into a spreadsheet.

If you want an Excel spreadsheet, I suggest you look at the Spreadsheet::WriteExcel module. I have used this in the past with good results. Here is a brief example of how to use it:

use DBI;
use strict;
use warnings;
use Spreadsheet::WriteExcel;

#
#Query the database here...
#


my $workbook = Spreadsheet::WriteExcel->new('query_results.xls');
my $worksheet = $workbook->add_worksheet();

my $row = 0;
my $col = 0;

#Write the column labels.
$worksheet->write_row($row++, $col, 
    ['Account Number','Type','Total Debit Amount']
);

while( my @data = $tran_cur->fetchrow_array)
{
    #Write an array into a row in the spreadsheet.
    $worksheet->write_row($row++, $col, \@data);
}

If you don't want an Excel spreadsheet, try searching CPAN for a module in the format you want, or use something like Text::CSV to create a CSV file that can easily be imported into any spreadsheet.

Upvotes: 1

Related Questions