216 lines
5.6 KiB
Python
216 lines
5.6 KiB
Python
import fs
|
|
import csv
|
|
import sys
|
|
|
|
def parseRow(row, header):
|
|
d = {}
|
|
for i in range(0, len(row)):
|
|
d[header[i]] = row[i]
|
|
return d
|
|
|
|
def deparseRow(row):
|
|
header = list(row.keys())
|
|
data = []
|
|
for key in header:
|
|
if(type(row[key]) != "String"):
|
|
row[key] = str(row[key])
|
|
data.append(row[key])
|
|
return (header, data)
|
|
|
|
def guessFonds(row):
|
|
if("wasmunt" in row['memo'].lower()):
|
|
return 'WF'
|
|
if(row['amount'] == 5.0 or row['amount'] == 10.0):
|
|
return 'WF'
|
|
|
|
if("maandelijkse vergoeding" in row['memo'].lower()):
|
|
return 'BF'
|
|
if("maandelijks vergoeding" in row['memo'].lower()):
|
|
return 'BF'
|
|
if(row['payee'] == "Kosten Zakelijk Betalingsverkeer ()"):
|
|
return "BF"
|
|
|
|
return 'UNKOWN'
|
|
|
|
Keys = []
|
|
def convert(row):
|
|
global Keys
|
|
row['Datum'] = f'{row["Datum"][0:4]}-{row["Datum"][4:6]}-{row["Datum"][6:8]}'
|
|
row['date'] = row['Datum']
|
|
del row['Datum']
|
|
|
|
#none 0
|
|
#credidcard 1
|
|
#cheque 2
|
|
#cash 3
|
|
if(row['Mutatiesoort'] == 'Overschrijving'):
|
|
row['payment'] = 4
|
|
elif(row['Mutatiesoort'] == 'iDEAL'):
|
|
row['payment'] = 7 #standing order
|
|
elif(row['Mutatiesoort'] == 'Online bankieren'):
|
|
row['payment'] = 8 #electronic payment
|
|
elif(row['Mutatiesoort'] == 'Diversen'):
|
|
row['payment'] = 10 #FI fee
|
|
del row['Mutatiesoort']
|
|
|
|
row['Tag'] = row['Tag'].split(',')
|
|
row['info'] = ''
|
|
if(len(row['Tag']) > 1):
|
|
row['info'] = row['Tag'][1]
|
|
|
|
row['payee'] = f'{row["Naam / Omschrijving"]} ({row["Tegenrekening"]})'
|
|
|
|
if( len(row['Mededelingen']) > 6
|
|
and (row['Mededelingen'][:6] == 'Naam: '
|
|
or row['Mededelingen'][:6] == 'Name: ')):
|
|
# print(row['Mededelingen'])
|
|
memo = row['Mededelingen'].split(': ')
|
|
key = 'name'
|
|
values = {}
|
|
for i in range(1, len(memo)):
|
|
memo[i] = memo[i].split(' ')
|
|
nextKey = memo[i].pop()
|
|
values[key] = ' '.join(memo[i])
|
|
key = nextKey
|
|
values[list(values.keys())[-1]] = key
|
|
if(values['name'] == row['Naam / Omschrijving']):
|
|
del values['name']
|
|
if('IBAN' in values and values['IBAN'] == row['Tegenrekening']):
|
|
del values['IBAN']
|
|
if('Datum/Tijd' in values):
|
|
del values['Datum/Tijd']
|
|
if('Valutadatum' in values):
|
|
del values['Valutadatum']
|
|
row['Mededelingen'] = ''
|
|
for key in list(values.keys()):
|
|
row['Mededelingen'] += f'{key}: {values[key]} '
|
|
|
|
row['memo'] = row['Mededelingen']
|
|
del row['Mededelingen']
|
|
|
|
row['Bedrag (EUR)'] = float(row['Bedrag (EUR)'].replace(',', '.'))
|
|
if(row['Af Bij'] == 'Af'):
|
|
row['Bedrag (EUR)'] = -row['Bedrag (EUR)']
|
|
row['amount'] = row['Bedrag (EUR)']
|
|
del row['Bedrag (EUR)']
|
|
del row['Af Bij']
|
|
|
|
row['category'] = row['Tag'][0]
|
|
if(row['category'] == ''):
|
|
row['category'] = guessFonds(row)
|
|
|
|
row['tags'] = ''
|
|
|
|
del row['Tag']
|
|
del row['Code']
|
|
del row['Saldo na mutatie']
|
|
del row['Naam / Omschrijving']
|
|
del row['Rekening']
|
|
del row['Tegenrekening']
|
|
|
|
return row
|
|
|
|
def main(inputFile):
|
|
global Keys
|
|
rows = []
|
|
csv.register_dialect('thing', delimiter=';', quoting=csv.QUOTE_ALL)
|
|
with open(inputFile, mode ='r') as file:
|
|
imp = csv.reader(file, 'thing')
|
|
|
|
headers = False
|
|
|
|
for row in imp:
|
|
if(headers == False):
|
|
headers = row
|
|
else:
|
|
rows.append(parseRow(row, headers))
|
|
|
|
file.close()
|
|
|
|
for row in rows:
|
|
# print("import")
|
|
# print(row)
|
|
row = convert(row)
|
|
# print("export")
|
|
# print(row)
|
|
print(Keys)
|
|
|
|
with open('homebank.csv', mode='w') as file:
|
|
header, row = deparseRow(rows[0])
|
|
header = '";"'.join(header)
|
|
header = f'"{header}"\n'
|
|
|
|
file.write(header)
|
|
|
|
fondsen = ['WF', 'BF', 'KHF', 'LF', 'UNKOWN']
|
|
fondsFiles = {}
|
|
for fonds in fondsen:
|
|
fondsFiles[fonds] = open(f'homebank-{fonds}.csv', 'w')
|
|
fondsFiles[fonds].write(header)
|
|
|
|
for row in rows:
|
|
fonds = row['category']
|
|
header, row = deparseRow(row)
|
|
row = '";"'.join(row)
|
|
row = f'"{row}"\n'
|
|
file.write(row)
|
|
|
|
try:
|
|
fondsFiles[fonds].write(row)
|
|
except:
|
|
fondsFiles['UNKOWN'].write(row)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
# def splitColls(row):
|
|
# colls = ['']
|
|
# quote = ''
|
|
# coll = 0
|
|
# for i in range(0, length(row)):
|
|
# if row[i] == ';' && quote == '':
|
|
# colls[length(colls)] = ''
|
|
# coll += 1
|
|
# continue
|
|
# else if row[i] == '"':
|
|
# if quote == '"':
|
|
# quote = ''
|
|
# continue
|
|
# else if not quote == "'":
|
|
# quote == "'"
|
|
# continue
|
|
# row[coll] += row[i]
|
|
# return colls
|
|
#
|
|
# def parseRow(row, headers):
|
|
# colls = splitColls(row)
|
|
# data =
|
|
#
|
|
# def readExport(filename):
|
|
# with f as open(filename, 'r'):
|
|
# rows = f.readlines()
|
|
#
|
|
# headers = False
|
|
#
|
|
# for row in rows:
|
|
# if(headers == False):
|
|
# headers = splitColls(row)
|
|
# else:
|
|
# yield parseRow(row, headers)
|
|
|
|
if __name__ == '__main__':
|
|
|
|
print(sys.argv)
|
|
|
|
if (len(sys.argv) == 2):
|
|
main(sys.argv[1])
|
|
else:
|
|
print("please use one orgument for the path to the imputfile")
|
|
|
|
|
|
# "Datum";"Naam / Omschrijving";"Rekening";"Tegenrekening";"Code";"Af Bij";"Bedrag (EUR)";"Mutatiesoort";"Mededelingen";"Saldo na mutatie";"Tag"
|