MT940 to csv convertor - specifically written to quickly analyse a large mt940 file, but very easy to change / append to

mt940.pl

#!/usr/bin/perl

%fieldmapping = ( '20'=>'transaction_ref', '25'=>'account_ref', '28C' => 'statement_no', '86'=>'account_owner',
		'61'=>'amount', '60F' => 'opening_balance', '62F'=>'closing_balance' );

@fieldorder = ($fieldmapping{'20'}, $fieldmapping{'25'}, 'date', $fieldmapping{'28C'}, $fieldmapping{'86'},
			'currency', $fieldmapping{'61'}, $fieldmapping{'60F'}, $fieldmapping{'62F'});

$last = "";
$skipline = 0;

foreach $o (@fieldorder) {
 print $o.";";
}
print "\n";

while(<>) {
 chomp;
 $_ =~ s/\r//g; # DOS line terminator

 if (!/\:(.*?)\:(.*)$/isgm) { # 'parse' a MT940 string
  if (!$skipline) {
   $record->{$fieldmapping{$last}} .= $_;
  }
  next;
 }

 $id = $1;
 $val = $2;
 $skipline = 0;

 next if keys %$record ==0 and $id != "20"; # did we arrive at a record yet?

 if ($id == "20") { # transaction reference; begin of new record
  if (keys %$record > 0) {

   # skip if there is no transaction record;
   next if !$record->{$fieldmapping{'61'}};

   foreach $o (@fieldorder) {
    print $record->{$o}.";";
   }
   print "\n";
  }
  $record = {};
  $record->{$fieldmapping{$id}} = $val;
 }

 $record->{$fieldmapping{$id}} = $val if $id == "25"; # account reference
 $record->{$fieldmapping{$id}} = $val if $id == "28C"; # statement number

 if ($id == "86") { # account owner
  $record->{$fieldmapping{$id}} = $val;
 }
 if ($id == "60F") { # opening balance
  ($currency, $balance) = ($val =~ /C.*?(EUR|USD)(.*)$/isgm);
  $balance =~ s/\,/\./isgm;
  $record->{$fieldmapping{$id}} = sprintf("%.2f", $balance);
 }
 if ($id == "62F") { # closing balance
  ($date, $currency, $balance) = ($val =~ /C(.*?)(EUR|USD)(.*)$/isgm);
  $balance =~ s/\,/\./isgm;
  $record->{$fieldmapping{$id}} = sprintf("%.2f", $balance);
  $date =~ /(\d\d)(\d\d)(\d\d)/;
  $record->{'date'} = "$3/$2/$1";
  $record->{'currency'} = $currency;
 }
 if ($id == "61") { # statement line (includes amount)
  if ($record->{'amount'}) { # hack update for multiple transactions in one reference(... ugh)
   foreach $o (@fieldorder) {
    print $record->{$o}.";";
   }
   print "\n";
  }
  ($dir, $amount) = ($val =~ /\d+?([DC])(\d+?\,\d\d).*$/);
  $amount =~ s/\,/\./isgm;
  $amount = $amount * -1 if $dir = "D";
  $amount = sprintf("%.02f", $amount);
  $record->{'amount'} = $amount;
  $skipline = 1;
 }
 $last = $id;
}