86 lines
3.4 KiB
Python
86 lines
3.4 KiB
Python
import camelot
|
|
import pandas as pd
|
|
import re
|
|
import csv
|
|
|
|
def clean_amount(val):
|
|
"""Removes '=' and whitespace from amount strings."""
|
|
if not val: return ""
|
|
return val.replace('=', '').strip()
|
|
|
|
def extract_account(text):
|
|
"""Extracts IBAN (SI56...) from a text block."""
|
|
match = re.search(r'[A-Z]{2}\d{2}\s?(?:\d{4}\s?){4}\d{1,3}', text)
|
|
return match.group(0).strip() if match else ""
|
|
|
|
def process_bank_statement(pdf_path, output_csv):
|
|
# Load all pages. 'stream' flavor is ideal for this layout.
|
|
tables = camelot.read_pdf(pdf_path, pages='all', flavor='stream', split_text=True)
|
|
|
|
all_transactions = []
|
|
current_tx = None
|
|
|
|
# Regex to identify a date (DD.MM.YY) which anchors a new transaction
|
|
date_pattern = re.compile(r'\d{2}\.\d{2}\.\d{2}')
|
|
|
|
for table in tables:
|
|
df = table.df
|
|
for index, row in df.iterrows():
|
|
# Skip the header rows (usually contains 'Creditor' or 'value date')
|
|
if "Creditor" in row[0] or "value date" in str(row.iloc[-1]):
|
|
continue
|
|
|
|
# Detect if this row contains a date in the last column (Column 4)
|
|
date_val = str(row.iloc[-1]).strip()
|
|
is_new_tx = bool(date_pattern.search(date_val))
|
|
|
|
if is_new_tx:
|
|
# If we were already building a transaction, save it before starting a new one
|
|
if current_tx:
|
|
all_transactions.append(current_tx)
|
|
|
|
# Initialize a new transaction object
|
|
current_tx = {
|
|
'value date': date_pattern.search(date_val).group(0),
|
|
'raw_text': row[0],
|
|
'debit': clean_amount(row[1]),
|
|
'credit': clean_amount(row[2])
|
|
}
|
|
else:
|
|
# If it's not a new date, it's a continuation of the previous transaction's text
|
|
if current_tx and row[0].strip():
|
|
current_tx['raw_text'] += " " + row[0].strip()
|
|
|
|
# Append the last transaction processed
|
|
if current_tx:
|
|
all_transactions.append(current_tx)
|
|
|
|
# Final formatting: Split raw_text into Name, Account, and Details
|
|
final_data = []
|
|
for tx in all_transactions:
|
|
text = tx['raw_text']
|
|
account = extract_account(text)
|
|
|
|
# Simple split logic: First part is Name, rest is Details (excluding account)
|
|
# Note: This logic can be refined based on specific name lengths
|
|
details = text.replace(account, "").strip()
|
|
parts = details.split(" ", 1) # Try to find a larger gap
|
|
name = parts[0].strip()
|
|
payment_details = parts[1].strip() if len(parts) > 1 else details
|
|
|
|
final_data.append({
|
|
"value date": tx['value date'],
|
|
"Creditor / Debtor": name,
|
|
"account": account,
|
|
"payment details": payment_details,
|
|
"debit": tx['debit'],
|
|
"credit": tx['credit']
|
|
})
|
|
|
|
# Export to CSV with semicolon delimiter
|
|
output_df = pd.DataFrame(final_data)
|
|
output_df.to_csv(output_csv, index=False, sep=';', encoding='utf-8-sig', quoting=csv.QUOTE_ALL)
|
|
print(f"Successfully exported {len(final_data)} transactions to {output_csv}")
|
|
|
|
# Execution
|
|
process_bank_statement("MSDataReport (1).pdf", "Processed_Transactions.csv") |