SQL Database to Perl Spreadsheet

Question

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.

Go over your previous questions and click the big tick next to the answers you used. — RobEarl
– RobEarl, Commented Oct 5, 2012 at 6:20
Here's how it is: You created a good overall post. There's example data, you've described the situation, you've showed code and that code even has strict and warnings. But there's no question in the post, and you want us to write complete code. That's not what we do here. We can help you if you run into problems, but SO is not a code writing service. Also, as @RobEarl said, you should accept answers in your previous questions, or no-one will help you. — simbabque
– simbabque, Commented Oct 5, 2012 at 7:40
Also, your output will be weird. It's not a spreadsheet, but rather: <td>Account</td><td>Number-Type-Total</td><td>Debit</td><td>Amount</td> 016901581432-Debit-29000 ... and so on. That's hardly a spreadsheet. — simbabque
– simbabque, Commented Oct 5, 2012 at 7:43

Justin · Accepted Answer · 2012-10-05 09:29:03Z

2

   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.

edited Oct 5, 2012 at 9:29

answered Oct 5, 2012 at 7:57

Justin

9,7336 gold badges38 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user1708526 Over a year ago

Mr.Justin-How can I change your above query if I want to get the output for Debit-Credit amount(Subtraction) for each account?

Justin Over a year ago

I change my answer, now with Subtraction

Justin Over a year ago

Mark as answer, if this what you need:)

user1708526 Over a year ago

Sir.In the above query, isnt it possible to calculate and display only the account number and Subtraction column output using SQL query?

user1708526 Over a year ago

I got the answer required. But its the matter of displaying exactly in case of subtraction.Now I want to display only Account Number and Subtraction column using query.How to do it?

dan1111 · Accepted Answer · 2012-10-05 08:12:09Z

1

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.

answered Oct 5, 2012 at 8:12

dan1111

6,5662 gold badges20 silver badges29 bronze badges

3 Comments

user1708526 Over a year ago

Oh.Thank you so much. I got the required output

dan1111 Over a year ago

I'm curious as to why this post was downvoted with no explanation.

Brad Gilbert Over a year ago

If you can get away with writing Excel 2007+ XLSX files, I would recommend Excel::Writer::XLSX. over Spreadsheet::WriteExcel.

Collectives™ on Stack Overflow

SQL Database to Perl Spreadsheet

2 Answers 2

5 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related