89 lines
2.5 KiB
Python
Executable File
89 lines
2.5 KiB
Python
Executable File
#!/usr/bin/env python3
|
|
import csv
|
|
import re
|
|
from decimal import Decimal
|
|
from datetime import datetime
|
|
from pathlib import Path
|
|
|
|
RAW_DIR = Path("/home/zaine/master-folder/attachments/bank-statements/nationwide/raw")
|
|
CLEANED_DIR = Path("/home/zaine/master-folder/attachments/bank-statements/nationwide/cleaned")
|
|
|
|
# Only keep digits, dot, minus for money
|
|
MONEY_REGEX = re.compile(r"[^\d\.-]")
|
|
|
|
def clean_money(value: str):
|
|
if not value:
|
|
return None
|
|
# Remove all non-numeric characters
|
|
value = MONEY_REGEX.sub("", value)
|
|
if not value:
|
|
return None
|
|
return Decimal(value)
|
|
|
|
def parse_date(value: str):
|
|
# Input format: 27 Feb 2026
|
|
return datetime.strptime(value.strip(), "%d %b %Y").strftime("%m/%d/%Y")
|
|
|
|
def convert(in_path: Path, out_path: Path):
|
|
rows_written = 0
|
|
|
|
# Read raw bytes and decode aggressively
|
|
with open(in_path, "rb") as f:
|
|
lines = f.read().decode("utf-8", errors="ignore").splitlines()
|
|
|
|
with open(out_path, "w", newline="", encoding="utf-8") as f_out:
|
|
writer = csv.writer(f_out, lineterminator="\r\n")
|
|
writer.writerow(["Date", "Payee", "Description", "Amount"])
|
|
|
|
for row in csv.reader(lines):
|
|
if not row:
|
|
continue
|
|
first = row[0].strip()
|
|
if first.startswith("Account") or first == "Date":
|
|
continue
|
|
if len(row) < 5:
|
|
continue
|
|
|
|
try:
|
|
date = parse_date(row[0])
|
|
except:
|
|
continue
|
|
|
|
desc = row[2].strip()
|
|
paid_out = clean_money(row[3])
|
|
paid_in = clean_money(row[4])
|
|
|
|
if paid_out is not None:
|
|
amount = f"{-paid_out:.2f}"
|
|
elif paid_in is not None:
|
|
amount = f"{paid_in:.2f}"
|
|
else:
|
|
continue
|
|
|
|
writer.writerow([date, desc, desc, amount])
|
|
rows_written += 1
|
|
|
|
return rows_written
|
|
|
|
def main():
|
|
CLEANED_DIR.mkdir(parents=True, exist_ok=True)
|
|
|
|
files = sorted(RAW_DIR.glob("*.csv"))
|
|
if not files:
|
|
print("No CSV files found.")
|
|
return
|
|
|
|
print("\nSelect a Nationwide CSV to clean:\n")
|
|
for i, f in enumerate(files, 1):
|
|
print(f" [{i}] {f.name}")
|
|
|
|
choice = int(input("\nEnter number: "))
|
|
in_path = files[choice - 1]
|
|
out_path = CLEANED_DIR / f"{in_path.stem}-cleaned.csv"
|
|
|
|
n = convert(in_path, out_path)
|
|
print(f"\n✔ {n} rows written to:\n {out_path}\n")
|
|
|
|
if __name__ == "__main__":
|
|
main()
|