Bundesbank bank data to sqlite

Andreas

2020-01-26 22:00

The German Bundesbank releases every quarter of a year a list of all banks in Germany. We build a Django based api website for this data a while ago: https://github.com/opendata-stuttgart/banking-api. The site went offline after a while.

After experimenting a bit with Datasette I built a converter of the Bundesbank Excel file to sqlite.

The code is using sqlite-utils for the sqlite part and openpxl for the Excel part.

Main converting loop:

def convert(db, xlsx_path):
    wb = load_workbook(filename=xlsx_path, read_only=True)

    # use the first worksheet
    ws = wb[wb.sheetnames[0]]

    translate_fieldnames = {
        'Bezeichnung': 'name',
        'Bank-leitzahl': 'blz',
        'BIC': 'bic',
        'PLZ': 'zipcode',
        'Ort': 'city',
        'Kurzbezeichnung': 'short_description',
        'PAN': 'pan',
        'Prüfziffer-berechnungs-methode': 'check_calculation_method',
        'Datensatz-nummer': 'dataset_number',
        'Merkmal': 'merkmal',
        'Änderungs-kennzeichen': 'change_type',
        'Bankleitzahl-löschung': 'is_deletion',
        'Nachfolge-Bankleitzahl': 'following_blz',
    }

    # translate tablenames to English names
    field_names = []
    for item in list(ws.iter_rows(min_row=1, max_row=1, values_only=True))[0]:
        field_names.append(translate_fieldnames[item])

    for row in ws.iter_rows(min_row=2, values_only=True):
        db["bundesbank_blz"].insert(
            record=dict(zip(field_names, row)), pk="blz", replace=True)

Especially the db["tablename"].insert() from sqlite-utils makes adding data to a sqlite databases really easy.

The full code is on Github: https://github.com/mfa/bundesbank-to-sqlite.