import camelot import pandas as pd import re from pathlib import Path def extract_bank_data(file_path: str, output_csv: str = "bank_export_fixed.csv"): print(f"Reading {file_path}… processing all pages.") # Tune these if needed: # - edge_tol: how aggressively Camelot merges columns # - row_tol: how aggressively it merges rows tables = camelot.read_pdf( file_path, pages="1-end", flavor="stream", strip_text="\n", ) print(f"Found {len(tables)} tables") final_transactions = [] current_tx = None # Regex patterns (adapt as needed for your bank) iban_pattern = r"SI56\s?\d{4}\s?\d{4}\s?\d{4}\s?\d{3}" amount_pattern = r"=(\d+[\.,]\d{2})" date_pattern = r"(\d{2}\.\d{2}\.\d{2})" # Helper: detect which column holds debit vs credit by position # You might want to print some sample rows to confirm indexes. def get_debit_credit_from_row(cells): """ Decide whether the amount we found is debit or credit based on which column index contains '='. You can adapt the column index test to your PDF. """ # Example strategy: # - assume columns are like: [desc, extra, date, debit_col, credit_col] # - if '=' appears first in column <= 3 => debit, else credit eq_indices = [i for i, c in enumerate(cells) if "=" in c] if not eq_indices: return None, None, None # (amount, debit, credit) # Use the first '=' we find idx = eq_indices[0] m = re.search(amount_pattern, cells[idx]) if not m: return None, None, None raw_amt = m.group(1).replace(",", ".").strip() # normalize to dot # Heuristic by index: tweak these numbers based on actual layout # Example: if index <= 2 -> debit, else credit is_debit = idx <= 2 debit = raw_amt if is_debit else "" credit = "" if is_debit else raw_amt return raw_amt, debit, credit for t_index, table in enumerate(tables, start=1): df = table.df # Optionally skip header rows on page 1 # You can detect header by known header text, e.g. "Vrednost" or similar for _, row in df.iterrows(): cells = [str(c).strip() for c in row] row_text = " ".join(c for c in cells if c) if not row_text: continue # --- 1. New transaction row? --- date_match = re.search(date_pattern, row_text) raw_amt, debit, credit = get_debit_credit_from_row(cells) if raw_amt is not None and date_match: # Save any previous transaction before starting a new one if current_tx: final_transactions.append(current_tx) value_date = date_match.group(1) # Creditor/Debtor is usually leftmost column(s). # Here we take cell 0 as the primary label. creditor_debtor = cells[0] current_tx = { "value date": value_date, "Creditor / Debtor": creditor_debtor, "account": "", "payment details": "", "debit": debit, "credit": credit, "is_collecting_details": False, } # IBAN possibly already in the same row for c in cells: iban_search = re.search(iban_pattern, c) if iban_search: current_tx["account"] = iban_search.group(0) # Everything after IBAN is usually detail text rem = c[iban_search.end() :].strip() rem = re.sub(r"[A-Z]{8,11}", "", rem).strip() if rem: current_tx["payment details"] = ( current_tx["payment details"] + " " + rem ).strip() current_tx["is_collecting_details"] = True break continue # go to next row # --- 2. Continuation row of last transaction --- if current_tx: text_line = cells[0].strip() if not text_line: continue # IBAN? iban_search = re.search(iban_pattern, text_line) if iban_search: current_tx["account"] = iban_search.group(0) current_tx["is_collecting_details"] = True remaining = re.sub(iban_pattern, "", text_line).strip() remaining = re.sub(r"[A-Z]{8,11}", "", remaining).strip() if remaining: current_tx["payment details"] = ( current_tx["payment details"] + " " + remaining ).strip() continue # Heuristic: address lines to skip from 'name' and usually not details is_address = any( kw in text_line.upper() for kw in ["CESTA", "ULICA", "TRG", " LJUBLJANA", " LOGATEC"] ) # If we are already collecting payment details or see typical reference markers if ( current_tx["is_collecting_details"] or "SI00" in text_line or "SI12" in text_line ): clean_text = re.sub(r"[A-Z]{8,11}", "", text_line).strip() if clean_text: current_tx["payment details"] = ( current_tx["payment details"] + " " + clean_text ).strip() elif not is_address and not current_tx["account"]: # Continuation of very short creditor/debtor name if len(current_tx["Creditor / Debtor"]) < 20: current_tx["Creditor / Debtor"] = ( current_tx["Creditor / Debtor"] + " " + text_line ).strip() # Append last transaction if present if current_tx: final_transactions.append(current_tx) # --- 3. To DataFrame and CSV --- df_final = pd.DataFrame(final_transactions) # Drop internal flag if "is_collecting_details" in df_final.columns: df_final = df_final.drop(columns=["is_collecting_details"]) # Clean text slightly if "payment details" in df_final.columns: df_final["payment details"] = df_final["payment details"].str.replace( r"[A-Z]{8,11}", "", regex=True ) df_final["payment details"] = ( df_final["payment details"] .str.replace(r"\s+", " ", regex=True) .str.strip() ) # Keep columns in desired order desired_cols = [ "value date", "Creditor / Debtor", "account", "payment details", "debit", "credit", ] df_final = df_final.reindex(columns=desired_cols) # Export as ;‑delimited CSV df_final.to_csv(output_csv, sep=";", index=False, encoding="utf-8-sig") print(f"Success! Exported {len(df_final)} transactions to {output_csv}") if __name__ == "__main__": pdf_path = "MSDataReport (1).pdf" # adjust if needed out_csv = "bank_export_fixed.csv" extract_bank_data(pdf_path, out_csv)