uweziegenhagen.de

Für Dante e.V. bestand die Notwendigkeit, aus MT940 Dateien moderne CAMT.053 zu erzeugen, dank Python wurde das eine lösbare Aufgabe.

Schritt 1

Die MT940 Datei parsen und die Transaktionen in einen pandas DataFrame überführen.

import mt940
import pprint
import pandas as pd

df = pd.DataFrame()

transactions = mt940.parse('Umsaetze_2310007_22.07.2024.mta')

print('Transactions:')
pprint.pprint(transactions.data)

for transaction in transactions:
    print('Transaction: ', transaction)
    pprint.pprint(transaction.data)
    t = transaction.data
    tt = pd.DataFrame(t, index=[0])

    df = pd.concat([df,tt],ignore_index=True)
    
df.to_excel('AllBookings.xlsx',index=False)

Schritt 2

Aus dem DataFrame das XML befüllen, die für den Kopf der XML-Datei notwendigen Kontostandsinformationen holen ich dazu aus der MT940 Datei.

import pandas as pd # data wrangling
import jinja2 # template engine
import os # for file-related stuff
import mt940
from datetime import datetime


today = datetime.today()
now = today.strftime("%Y-%m-%d")



transactions = mt940.parse('Umsaetze_2310007_22.07.2024.mta')

opening = transactions.data['final_opening_balance']
openingamount = str(opening.amount)[:-4]
openingdate = opening.date

closing = transactions.data['final_closing_balance']
closingamount = str(closing.amount)[:-4]
closingdate = closing.date

 
# create jinja env that can load template from filesystem
jinja_env = jinja2.Environment(loader = jinja2.FileSystemLoader(os.path.abspath('.')))
 
df = pd.read_excel('AllBookings.xlsx', dtype={'date': str,'amount':str})

df['CreditDebit'] = ''
df['CreditDebit'] = df['CreditDebit'].where(df['amount'].str.get(0).isin(['-']), 'CRDT')
df['CreditDebit'] = df['CreditDebit'].where(~df['amount'].str.get(0).isin(['-']), 'DBIT')

df['date'] = df['date'].str[:-9]


# Währung weg
df['amount'] = df['amount'].str[:-4]
# Vorzeichen weg
df['amount'] = df['amount'].str.replace('-','')

#df['amount'].replace('-','',inplace=True)
#df["amount"] = df["amount"].apply(lambda x: x.str.replace("-", ""))

template = jinja_env.get_template('Ntry.xml')
 
with open('FertigesXML.xml','w') as output:
    output.write(template.render(data=df,
                                 openingamount=openingamount,
                                 openingdate=openingdate,
                                 closingamount=closingamount,
                                 closingdate=closingdate
                                 ))

Jinja2 XML-Template

Das XML-Template für Jinja2 findet ihr hier:

Ntry_blog

Uwe

Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined. Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.

More Posts - Website