Script to convert ECSL CSV files exported from MYOB on Mac to HMRC compatible CSV

December 4, 2012 under Main

Every 3 months we have to send Her Majesty’s Revenue and Customs (HMRC) in the UK a list of customers in the EU to which we have sold our services, this list is called the ECSL or European Community Sales List. Unfortunately due to choices made long ago which are non-trivial to change, we use a program called MYOB on a Mac for our book keeping. Unlike some of its competitors, MYOB can’t upload the ECSL data directly to HMRC, so we have to enter the data into forms manually.

This is a very tedious job, and since I don’t like tedious things, I set out to find a better solution. HMRC offer a facility whereby you can automate the data entry by importing a CSV file formatted a specific way. MYOB can export the ECSL to CSV, but the format is not compatible with the HMRC system.

The solution I came up with was to write a small Perl script to convert from the MYOB format to the HMRC format. Posted here, in case anyone else finds it useful!

#!/usr/bin/perl -w

# Input file (MYOB export) should be passed as an argument to this script
my $infile = $ARGV[0];
chomp($infile);

my $outfile = $infile . '-hmrc.csv';

# Ask for year and month for this submission
print "Year: ";
my $year = <STDIN>;
chomp($year);
print "Month: ";
my $month = <STDIN>;
chomp($month);

# open output file
open(HMRCFILE, '>', $outfile) or die "Could not open output file: $!";

# Write header
my $vatregno = "your_number_here";
my $subsidiary = "000";
my $name = "Christopher Wik";
print HMRCFILE "HMRC_VAT_ESL_BULK_SUBMISSION_FILE\n";
print HMRCFILE "$vatregno,$subsidiary,$year,$month,GBP,$name,0\n";

# Set record separator to \r (Mac) - MYOB saves in this format
$/ = "\r";

# Convert data from MYOB to HMRC format
open(MYOBFILE, $infile) or die "Could not open input file: $!";
while(<STDIN>) {
  next if !/,\w\w\d+/;
  my ($cust,$vat,$amount) = split(/,/,$_,3);
  my $country = substr($vat, 0, 2);
  my $vatno = substr($vat, 2);
  $amount =~ /(\d+)/;
  $amount_num = $1;
  $amount =~ /(-)/;
  if( $1 eq '-' ) { $amount_num *= -1; }
  print HMRCFILE "$country,$vatno,$amount_num,3\n";
}
close(MYOBFILE);

# close HMRC output file
close(HMRCFILE);
comments: Comments Off on Script to convert ECSL CSV files exported from MYOB on Mac to HMRC compatible CSV