how-to-calculate-balance-of-bank-accounts-in-csv-file-using-perl.tt

=title How to calculate the balance of bank accounts in a CSV file, using Perl?
=timestamp 2012-08-04T20:45:56
=indexes CSV, split
=status show
=author szabgab
=index 1
=archive 1
=feed 1
=comments 1
=social 1

=abstract start

One of the readers of the <a href="/how-to-read-a-csv-file-using-perl">How to read a CSV file using Perl?</a> article
has sent me a CSV file and a question:

<b>How to Calculate and display total balance in each account using hash in perl. Without using parse function?</b>

Let's see how can we handle such a request?

=abstract end

<code>
TranID,Date,AcNo,Type,Amount,ChequeNo,DDNo,Bank,Branch
13520,01-01-2011,5131342,Dr,5000,,,,
13524,01-01-2011,5131342,Dr,1000,,416123,SB,Ashoknagar
13538,08-01-2011,5131342,Cr,1620,19101,,,
13548,17-01-2011,5131342,Cr,3500,19102,,,
13519,01-01-2011,5522341,Dr,2000,14514,,SBM,Hampankatte
13523,01-01-2011,5522341,Cr,500,19121,,,
13529,02-01-2011,5522341,Dr,5000,13211,,SB,Ashoknagar
13539,09-01-2011,5522341,Cr,500,19122,,,
13541,10-01-2011,5522341,Cr,2000,19123,,,
</code>


At first it was not clear where is he stuck or if he made any progress at all.
In order to help him I had to try to figure out both what he wanted to reach and the where he is stuck.

I asked for the code he already wrote, and got this script:

<code lang="perl">
#!/usr/bin/perl

print "Content-type:text/html\n\n";

my $sum;
my $sum1;
my $sum2;

open(FILEHANDLE, "<banktran.csv") or die "Could not open 'banktran.csv' $!\n";

while (my $line = <FILEHANDLE>) {
  chomp $line;
  my @fields = split "," , $line;

  if ($fields[2] eq 5131342) {
    if ($fields[3] eq Dr) {
      $sum1 += $fields[4];
    } else {
      $sum2 += $fields[4];
    }
    $sum = $sum1-$sum2;
    print "Total Balance of Account Number is Rs.$sum\n";
  }
}

close(FILEHANDLE);
</code>

Besides some obvious beginner issues I started to understand, that probably he wants to create a separate report for
each account - the third column - <b>AcNo</b> is probably the account number.

The amount is in the 5th column under the title <b>Amount</b>.

As I can see the 4th column indicates if the type of the transaction. A little search indicates that Dr would be debit
and Cr would be credit, though in the code that seems to be the opposite.

The first sentence in the question seems to indicate that he already understand he needs to use hashes,
instead of the scalar <hl>$sum</hl> variables, but it is yet unclear to him how.

The second sentence, <b>Without using parse function?</b> seems to indicate to me that for some reason the reader
cannot use the Text::CSV module that has the parse method. That's unfortunate, as that is the right tool in the
general case of parsing and reading CSV files, but in many corporate settings installing a module from CPAN is
difficult. Especially to someone who is new to Perl.

Assuming the CSV file is simple - no quoted separators, no embedded newlines - we can handle it with a call
to the <hl>split</hl> function.


The code he sent me is reasonable for someone starting to use Perl, let's see how can we improve it
and how can we try to implement what he needed?


<h2>Running the code</h2>

Before trying to improve the code, let's see if it runs, and what does it do? The script is saved
as <b>banktran.pl</b> and the csv file as <b>banktran.csv</b>

<b>perl bantran.pl</b>

<code>
Content-type:text/html

Total Balance of Account Number is Rs.5000
Total Balance of Account Number is Rs.6000
Total Balance of Account Number is Rs.4380
Total Balance of Account Number is Rs.880
</code>

Now that we see it does something we can make some improvements:

<h2>use strict and use warnings</h2>

First of all, I very strongly recommend every Perl script to start with the two statements of the
safety-net. I know that I can waste a lot of valuable time searching for bugs that these
two would catch, so I don't want to be without them.

<code lang="perl">
use strict;
use warnings;
</code>

It should come right after the sh-bang. If we add this to the above code and try to run it again we get
the following:

<code>
Bareword "Dr" not allowed while "strict subs" in use at banktran.pl line 18.
Execution of banktran.pl aborted due to compilation errors.
</code>

<a href="/barewords-in-perl">Bareword not allowed while "strict subs" in use</a>
is one of the common warnings described in <a href="/perl-tutorial">Perl Maven tutorial</a>.
We need to put single-quotes <hl>'</hl> around the string <b>Dr</b>

Running the script again we get the following:

<code>
Content-type:text/html

Use of uninitialized value $sum2 in subtraction (-) at banktran.pl line 23, <FILEHANDLE> line 2.
Total Balance of Account Number is Rs.5000
Use of uninitialized value $sum2 in subtraction (-) at banktran.pl line 23, <FILEHANDLE> line 3.
Total Balance of Account Number is Rs.6000
Total Balance of Account Number is Rs.4380
Total Balance of Account Number is Rs.880
</code>


The <a href="/use-of-uninitialized-value">Use of uninitialized value</a> warning
is another common warning in Perl. It means the <hl>$sum2</hl> was undef in line 23.

<code lang="perl">
$sum = $sum1-$sum2;
</code>

We should probably initialize the variables to 0. It is not always necessary, but it can lead
to cleaner code. The resulting code so far looks like this:

<code lang="perl">
#!/usr/bin/perl
use strict;
use warnings;

print "Content-type:text/html\n\n";

my $sum  = 0;
my $sum1 = 0;
my $sum2 = 0;

open(FILEHANDLE, "<banktran.csv") or die "Could not open 'banktran.csv' $!\n";

while (my $line = <FILEHANDLE>) {
  chomp $line;
  my @fields = split "," , $line;

  if ($fields[2] eq 5131342) {
    if ($fields[3] eq 'Dr') {
      $sum1 += $fields[4];
    } else {
      $sum2 += $fields[4];
    }
    $sum = $sum1-$sum2;
    print "Total Balance of Account Number is Rs.$sum\n";
  }
}

close(FILEHANDLE);
</code>

<h2>Using open the "modern" way</h2>

I put the word "modern" in quotes because this is available since 2000 so it is not really
new, but still many people learn the old style first.

There is an article why one
<a href="/open-files-in-the-old-way">should not open files in the old way in Perl</a>,
I won't repeat it here, I'll just fix the code:

<code lang="perl">
#!/usr/bin/perl
use strict;
use warnings;

print "Content-type:text/html\n\n";

my $sum  = 0;
my $sum1 = 0;
my $sum2 = 0;

my $filename = 'banktran.csv';
open(my $FILEHANDLE, '<', $filename) or die "Could not open '$filename' $!\n";

while (my $line = <$FILEHANDLE>) {
  chomp $line;
  my @fields = split "," , $line;

  if ($fields[2] eq 5131342) {
    if ($fields[3] eq 'Dr') {
      $sum1 += $fields[4];
    } else {
      $sum2 += $fields[4];
    }
    $sum = $sum1-$sum2;
    print "Total Balance of Account Number is Rs.$sum\n";
  }
}

close($FILEHANDLE);
</code>

As you can see the I changed <hl>FILEHANDLE</hl> to be the lexical scalar <hl>$FILEHANDLE</hl>,
using 3 parameters in the <hl>open</hl> function and also put the name of the file in a variable.

This last step is important for two reasons:
<ol>
  <li>It will make it easier to pass the name of the file as a parameter, if we need it.</hl>
  <li>We won't fall in the trap of changing the name in the <hl>open()</hl> call, and leaving the old name in the
<hl>die()</hl> call and getting confused by the error message.</li>
</ol>

<h2>Better variable names</h2>

The fact that we are using and array called <hl>@fields</hl> and indexes in that array
makes it unclear what kind of values are in those field.

Quickly, can you remember what is in $fields[2]? I cannot. So instead of using the @fields array
we could use variables with better names and write:

<code lang="perl">
my ($id, $date, $account, $type, $amount, $cheque, $dd, $bank, $branch)
    = split "," , $line;
</code>

This turned the above line a bit longer but will make the rest of the code more readable.
This also makes us creates some unnecessary variables.

<a href="/advanced-perl-maven-e-book">Advanced Perl developers</a> could make it nicer by using an array slice:

<code lang="perl">
my ($account, $type, $amount) = (split "," , $line)[2, 3, 4];
</code>

The loop will look like this:

<code lang="perl">
while (my $line = <$FILEHANDLE>) {
  chomp $line;
  my ($account, $type, $amount) = (split "," , $line)[2, 3, 4];

  if ($account eq 5131342) {
    if ($type eq 'Dr') {
      $sum1 += $amount;
    } else {
      $sum2 += $amount;
    }
    $sum = $sum1-$sum2;
    print "Total Balance of Account Number is Rs.$sum\n";
  }
}
</code>

<h2>Eliminate temporary variables</h2>

As I can see the <hl>$sum1</hl> and <hl>$sum2</hl> variables are used only to hold
the values that either need to be added to the $sum or deducted from it. We don't really need them.
We could add to <hl>$sum</hl>, or deduct from it inside the condition:

<code lang="perl">
  if ($account eq 5131342) {
    if ($type eq 'Dr') {
      $sum += $amount;
    } else {
      $sum -= $amount;
    }
</code>

Let's see and try the full code again, before the big operation.

<code lang="perl">
#!/usr/bin/perl
use strict;
use warnings;

print "Content-type:text/html\n\n";

my $sum  = 0;

my $filename = 'banktran.csv';
open(my $FILEHANDLE, '<', $filename) or die "Could not open '$filename' $!\n";

while (my $line = <$FILEHANDLE>) {
  chomp $line;
  my ($account, $type, $amount) = (split "," , $line)[2, 3, 4];

  if ($account eq 5131342) {
    if ($type eq 'Dr') {
      $sum += $amount;
    } else {
      $sum -= $amount;
    }
    print "Total Balance of Account Number is Rs.$sum\n";
  }
}

close($FILEHANDLE);
</code>

<h2>Show the total for all the accounts</h2>

Right now, only one specific account (id = 5131342) is summarized and it is done in a scalar variable.
Instead of this we would like to summarize all the accounts. The easiest way is to use a hash.
The account ids will be the keys and the sum will be the value.

<code lang="perl">
#!/usr/bin/perl
use strict;
use warnings;

print "Content-type:text/html\n\n";

my %sum;

my $filename = 'banktran.csv';
open(my $FILEHANDLE, '<', $filename) or die "Could not open '$filename' $!\n";

while (my $line = <$FILEHANDLE>) {
  chomp $line;
  my ($account, $type, $amount) = (split "," , $line)[2, 3, 4];

  if ($type eq 'Dr') {
    $sum{$account} += $amount;
  } else {
    $sum{$account} -= $amount;
  }
  print "Total Balance of Account Number $account is Rs.$sum{$account}\n";
}

close($FILEHANDLE);
</code>

In this code we don't need the <hl>if ($account eq 5131342)</hl> condition any more.
We can access the hash key directly, using the <hl>$account</hl> number as the key.

After running the script the result looks like this:

<code>
Content-type:text/html

Argument "Amount" isn't numeric in subtraction (-) at banktran.pl line 19, <$FILEHANDLE> line 1.
Total Balance of Account Number AcNo is Rs.0
Total Balance of Account Number 5131342 is Rs.5000
Total Balance of Account Number 5131342 is Rs.6000
Total Balance of Account Number 5131342 is Rs.4380
Total Balance of Account Number 5131342 is Rs.880
Total Balance of Account Number 5522341 is Rs.2000
Total Balance of Account Number 5522341 is Rs.1500
Total Balance of Account Number 5522341 is Rs.6500
Total Balance of Account Number 5522341 is Rs.6000
Total Balance of Account Number 5522341 is Rs.4000
</code>

The warning we get is due to the first line in the CSV file. Earlier we did not have
to care about it as we only dealt with rows where the account id was matching the selected
number, but now we have to skip that line. It's easy, just read the first row before the
<hl>while</hl> loop, and throw it away.


<code lang="perl">
<$FILEHANDLE>;
while (my $line = <$FILEHANDLE>) {
</code>

<h2>Total only at the end?</h2>

This could be the final version, but it is unclear to me if we really need to display the Balance after every row,
or only at the end. So let's make another change that will display the results only at the end.

We remove the <hl>print</hl> call from the <hl>while</hl> loop and add another loop at the end, going over all the
accounts and displaying the account status:


<code lang="perl">
foreach my $account (sort keys %sum) {
  print "Total Balance of Account Number $account is Rs.$sum{$account}\n";
}
</code>

The full code
<code lang="perl">
#!/usr/bin/perl
use strict;
use warnings;

print "Content-type:text/html\n\n";

my %sum;

my $filename = 'banktran.csv';
open(my $FILEHANDLE, '<', $filename) or die "Could not open '$filename' $!\n";

<$FILEHANDLE>;
while (my $line = <$FILEHANDLE>) {
  chomp $line;
  my ($account, $type, $amount) = (split "," , $line)[2, 3, 4];

  if ($type eq 'Dr') {
    $sum{$account} += $amount;
  } else {
    $sum{$account} -= $amount;
  }
}

close($FILEHANDLE);

foreach my $account (sort keys %sum) {
  print "Total Balance of Account Number $account is Rs.$sum{$account}\n";
}
</code>

There is only one little thing that still bothers me. Why do we print Content-type
at the beginning of the code? Is this supposed to run as a CGI script?

If no, then we could remove that line.

If this is a CGI script then we should probably print real HTML out. At least we should
print pre tags around the report.