Team project · Stop 9 of 10

ROBIN

A tool that transfers Excel participant lists into a PostgreSQL database. A team project of two.

Goal

Get participant data from Excel into the database reliably and without manual work.

Implementation

My part was the backend: reading Excel files, preparing the data and writing it to the database.
Connection to PostgreSQL through an SSH tunnel, with configuration, error handling and a log.
The interface (import, preview, export, animations) was built by my team partner.

Code

Finding the header row of the Excel file

data_processor.py

def _find_header_and_indices(self, raw_rows: List[List[Any]]) -> Tuple[int, Dict[str, int]]:
    for i, row in enumerate(raw_rows[:50]):
        cleaned_row = [str(cell).strip() if cell is not None else "" for cell in row]

        if "Vorname" in cleaned_row and "Name" in cleaned_row:
            try:
                return i, {
                    "reha": cleaned_row.index("TN-Nr"),
                    "last_name": cleaned_row.index("Name"),
                    "first_name": cleaned_row.index("Vorname"),
                    "email": cleaned_row.index("Email2"),
                    "specialization": cleaned_row.index("Massnahme Nr"),
                    "course": cleaned_row.index("Ausbildungsgruppe")
                }
            except ValueError as e:
                raise ValueError(f"Fehler 102|Zeile {i + 1}")

    raise ValueError("Fehler 102")

The backend looks for the columns in the first 50 rows and reports missing ones with an error code and row.

What I learned

Agreeing on interfaces in a team: the backend has to be clear enough for the interface to use it without questions.