Skip to content

Instantly share code, notes, and snippets.

@samueltc
Created Mar 13, 2017
Embed
What would you like to do?
visa desjardins statement to xero csv statement
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])
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment