Last active
October 22, 2020 21:03
-
-
Save diatche/164f451834729af8a40e4c3a9d3fcbbd to your computer and use it in GitHub Desktop.
Converts Swedbank CSV format into a CSV table format which is easier for automated processing.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/bin/python3 | |
# Converts Swedbank CSV format into a CSV table | |
# format which is easier for automated processing. | |
# | |
# Usage: | |
# | |
# 1. Save file as swedbank-csv.py and open terminal in folder location. | |
# 2. In termial: python3 swedbank-csv.py -i <path to source CSV> | |
# 3. For more info, run: python3 swedbank-csv.py --help | |
# | |
# Example: | |
# | |
# Date,Beneficiary/Payer,Details,Amount,Currency,Transaction type,Balance | |
# 01.01.2019,,Opening balance,,EUR,AS,41.7 | |
# 06.01.2019,Janis Pipars,Parskaitijums,-10.0,EUR,IZP, | |
# 10.01.2019,,Kartes mēneša maksa 12.2019,-1.41,EUR,KOM, | |
# 31.01.2019,,Closing balance,,EUR,LS,20.0 | |
# | |
# Works with Swedbank Latvia CSV format as at 23 Oct 2020. | |
# | |
# Author: Pavel Diatchenko <diatche@gmail.com> | |
# Licence: MIT | |
import csv | |
import argparse | |
from pathlib import Path | |
parser = argparse.ArgumentParser( | |
description=( | |
'Converts Swedbank CSV format into a CSV table ' | |
'format which is easier for automated processing.\n\n' | |
'Works with Swedbank Latvia CSV format as at 23 Oct 2020.\n\n' | |
'Author: Pavel Diatchenko <diatche@gmail.com>\n' | |
'Licence: MIT' | |
) | |
) | |
parser.add_argument( | |
'-i', | |
'--source', | |
type=str, | |
required=True, | |
help='Source CSV file path.', | |
) | |
parser.add_argument( | |
'-o', | |
'--destination', | |
type=str, | |
help='Destination CSV file path.', | |
) | |
parser.add_argument( | |
'-s', | |
'--suffix', | |
type=str, | |
help='Destination CSV file suffix. If destination file is ommited, this suffix is added to the source file name', | |
) | |
parser.add_argument( | |
'-d', | |
'--delimiter', | |
type=str, | |
help='Source CSV column delimiter.', | |
) | |
parser.add_argument( | |
'--verbose', | |
dest='verbose', | |
action='store_true', | |
help='Verbose mode.', | |
) | |
parser.set_defaults( | |
suffix='-parsed', | |
delimiter=';', | |
verbose=False, | |
) | |
args = parser.parse_args() | |
verbose = bool(args.verbose) | |
source_path = Path(args.source).expanduser() | |
if verbose: print(f'source_path: "{source_path}"') | |
destination_path = '' | |
if not bool(args.destination): | |
# Polyfill with_stem (new in Python 3.9) | |
def with_stem(p, stem): | |
ext = p.suffix | |
return p.with_name(f'{stem}{ext}') | |
if verbose: print(f'suffix: "{args.suffix}"') | |
destination_path = with_stem(source_path, source_path.stem + args.suffix) | |
else: | |
destination_path = Path(args.destination).expanduser() | |
if verbose: print(f'destination_path: "{destination_path}"') | |
delimiter = args.delimiter | |
if verbose: print(f'delimiter: "{delimiter}"') | |
BALANCE_KEY = 'Balance' | |
AMOUNT_KEY = 'Amount' | |
DEBIT_CREDIT_KEY = 'Debit/Credit' | |
DEBIT = 'D' | |
CREDIT = 'K' | |
TYPE_KEY = 'Transaction type' | |
BALANCE_TYPES = {'AS', 'LS'} | |
REMOVE_TYPES = set(['K2']) | |
INPUT_HEADERS = ['Client account', 'Row type', 'Date', 'Beneficiary/Payer', 'Details', AMOUNT_KEY, 'Currency', DEBIT_CREDIT_KEY, 'Transfer reference', TYPE_KEY, 'Reference number', 'Document number', ''] | |
OUTPUT_HEADERS = ['Date', 'Beneficiary/Payer', 'Details', AMOUNT_KEY, 'Currency', TYPE_KEY, BALANCE_KEY] | |
with open(source_path, newline='') as source: | |
reader = csv.reader(source, delimiter=delimiter, quotechar='"') | |
destination_path.touch() | |
with open(destination_path, 'w', newline='') as destination: | |
writer = csv.writer(destination, delimiter=',', quotechar='"') | |
if verbose: print('begin') | |
for i, row in enumerate(reader): | |
if i == 0: | |
assert row == INPUT_HEADERS, 'Unexpected input headers' | |
writer.writerow(OUTPUT_HEADERS) | |
continue | |
row_dict = {} | |
for key, val in zip(INPUT_HEADERS, row): | |
row_dict[key] = val | |
# filter types | |
if row_dict[TYPE_KEY] in REMOVE_TYPES: | |
continue | |
# map amount sign | |
amount = float(str(row_dict[AMOUNT_KEY]).replace(',', '.')) | |
debit_credit = row_dict[DEBIT_CREDIT_KEY] | |
is_debit = debit_credit == DEBIT | |
is_credit = debit_credit == CREDIT | |
if not is_debit and not is_credit: | |
continue | |
if is_debit: | |
amount = -amount | |
# map balance | |
balance = '' | |
if row_dict[TYPE_KEY] in BALANCE_TYPES: | |
balance = amount | |
amount = '' | |
else: | |
balance = '' | |
out_dict = { | |
AMOUNT_KEY: amount, | |
BALANCE_KEY: balance | |
} | |
outrow = [] | |
outval = None | |
for key in OUTPUT_HEADERS: | |
if key in out_dict: | |
outval = out_dict[key] | |
elif key in row_dict: | |
outval = row_dict[key] | |
outrow.append(outval) | |
writer.writerow(outrow) | |
if verbose: print(outrow) | |
if verbose: print('end') |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment