MT940 to csv convertor - specifically written to quickly analyse a large mt940 file, but very easy to change / append to
#!/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; | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment