Spaces:
Sleeping
Sleeping
File size: 2,145 Bytes
d084ead | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 | """Fix extracted Excel files: convert trailing CR balances into negatives.
Scans all .xlsx files in `data/output` and writes a `.fixed.xlsx` copy
with any `balance` or `balance_after` columns normalized.
Usage:
python scripts/fix_extracted_balances.py
"""
from pathlib import Path
import re
from openpyxl import load_workbook
ROOT = Path(__file__).resolve().parent.parent
OUT_DIR = ROOT / "data" / "output"
CR_RE = re.compile(r"(?i)\s*CR\s*$")
def fix_workbook(path: Path) -> Path:
wb = load_workbook(path)
changed = False
for name in wb.sheetnames:
ws = wb[name]
# find header row (assume first row)
headers = [str(c.value).strip() if c.value is not None else "" for c in ws[1]]
bal_cols = [i for i, h in enumerate(headers) if h and "balance" in h.lower()]
if not bal_cols:
continue
for row in ws.iter_rows(min_row=2, max_col=ws.max_column):
for ci in bal_cols:
cell = row[ci]
v = cell.value
if v is None:
continue
s = str(v).strip()
if CR_RE.search(s):
cleaned = CR_RE.sub("", s).strip()
cleaned = cleaned.replace(",", "")
if cleaned and not cleaned.startswith("-"):
cell.value = "-" + cleaned
else:
cell.value = cleaned
changed = True
if changed:
out = path.with_name(path.stem + ".fixed" + path.suffix)
wb.save(out)
return out
return path
def main():
if not OUT_DIR.exists():
print("No data/output directory found; nothing to do.")
return
files = list(OUT_DIR.glob("*.xlsx"))
if not files:
print("No xlsx files in data/output")
return
for f in files:
out = fix_workbook(f)
if out != f:
print(f"Fixed: {f.name} -> {out.name}")
else:
print(f"No changes: {f.name}")
if __name__ == "__main__":
main()
|