visa desjardins statement to xero csv statement

parse.py

import sys
import re
from pprint import pprint
import csv
from unidecode import unidecode

TR = re.compile(r'(?P<transaction_day>\d{2})\s+(?P<transaction_month>\d{2})\s+(?P<inscription_day>\d{2})\s+(?P<inscription_month>\d{2})\s+(?P<sequence>\d{3})\s+(?P<reference>\d{23})?(?P<payee>.*?)\s*(?P<amount>[\s0-9,]+)(?P<transaction_type>CR)?$')
FIELDS = ['Date','Amount','Payee','Description','Reference']

def parse(year):
  output = csv.DictWriter(open('%s.csv' % year, 'w+'), fieldnames=FIELDS, quoting=csv.QUOTE_ALL)
  output.writeheader()
  rows = open('%s.txt' % year).readlines()
  rows = [row.strip() for row in rows]
  for index, row in enumerate(rows):
    row = row.strip()
    transaction = TR.match(row)
    if transaction:
      dough = transaction.groupdict()
      dough['amount'] = dough['amount'].strip().replace(' ', '').replace(',', '.')
      dough['payee'] = unidecode(re.sub('\s+',' ', dough['payee'].strip()))
      dough['inscription_year'] = year
      dough['transaction_year'] = year
      dough['inscription_date'] = '-'.join(map(dough.pop, ['inscription_year', 'inscription_month', 'inscription_day']))
      dough['transaction_date'] = '-'.join(map(dough.pop, ['transaction_year', 'transaction_month', 'transaction_day']))

      if not TR.match(rows[index+1]):
        note = re.sub('\s+',' ', rows[index+1])
        dough.update(note=note)

      if not dough['transaction_type']:
        dough['amount'] = '-%s' % dough['amount']
      xero = {
        'Date': dough['transaction_date'],
        'Amount': dough['amount'],
        'Payee': dough['payee'],
        'Description': dough.get('note', ''),
        'Reference': dough['reference']
      }
      output.writerow(xero)

if __name__=='__main__':
  parse(sys.argv[1])