merkurToXeroCSVConverter

merkurToXero.py

#!/usr/bin/env python
# -*- coding: utf-8 -*-

# xero seems to only read ÆØÅ correctly if we encode the file with ISO8859-1 

#import csv
import unicodecsv as csv
import sys
import codecs
import string

def strip_control_characters(input):

    if input:

        import re
        # unicode invalid characters
        RE_XML_ILLEGAL = u'([\u0000-\u0008\u000b-\u000c\u000e-\u001f\ufffe-\uffff])' + \
                         u'|' + \
                         u'([%s-%s][^%s-%s])|([^%s-%s][%s-%s])|([%s-%s]$)|(^[%s-%s])' % \
                          (unichr(0xd800),unichr(0xdbff),unichr(0xdc00),unichr(0xdfff),
                           unichr(0xd800),unichr(0xdbff),unichr(0xdc00),unichr(0xdfff),
                           unichr(0xd800),unichr(0xdbff),unichr(0xdc00),unichr(0xdfff),
                           )
        input = re.sub(RE_XML_ILLEGAL, " ", input)

        # ascii control characters
        input = re.sub(r"[\x01-\x1F\x7F]", " ", input)

    return input

sourceFileName = sys.argv[1]
outFileName = "export_"+sourceFileName

# output
# xero template: *Date,*Amount,Payee,Description,Reference,Check Number

with open(sourceFileName, "rb") as f:
    with open(outFileName, "w+") as o:

        writer = csv.writer(o, delimiter=',', encoding="ISO8859-1")

        lines = []
        for line in f:
            s = strip_control_characters(line)
            s = s.decode("ISO8859-1").encode("utf-8")

            lines.append(s)

        reader = csv.reader(lines, delimiter=';', quotechar="\"", quoting=csv.QUOTE_ALL)

        outdata = []

        rownum = 0
        headers = []
        for row in reader:

            date, amount, payee, description, ref = "", "", "", "", ""
            if rownum == 0:
                headers = row

            for i, col in enumerate(row):

                col = col.encode("ISO8859-1") #iso-8859-1

                if headers[i] == u"Dato":
                    date = col

                elif headers[i] == u"Modtagernavn" or headers[i] == u"Indbetaler":
                    if payee is not "":
                        payee += " "

                    payee += col

                elif headers[i] == u"Beløb":
                    amount = col

                elif headers[i] == u"Supp. tekst til modtager" or headers[i] == u"Tekst":
                    if description is not "":
                        description += " "
                    description += col

                elif headers[i] == u"Tekst til modtager":
                    ref = col

            if rownum is 0:
                outdata.append([u"Date", u"Amount",u"Payee", u"Description", u"Reference"])
            else:
                outdata.append([date, amount, payee, description, ref])

            rownum += 1

        writer.writerows(outdata)
        o.close()
        f.close()

requirements.txt

unicodecsv==0.14.1