import csv
from pathlib import Path

# --- Fix malformed CSV lines caused by embedded line breaks ---
base = Path(__file__).resolve().parent
input_path = base / "ResultsGrid_ExportData.csv"
output_path = base / "ResultsGrid_ExportData_fixed.csv"

# You can adjust this if your CSV has more or fewer columns.
# The idea: most valid rows have roughly the same number of commas.
EXPECTED_COLUMNS = 30  # Adjust this if needed (use len(df.columns) from a good export)

print("🔧 Repairing malformed CSV rows...")

with open(input_path, "r", encoding="utf-8-sig", errors="ignore") as infile, \
     open(output_path, "w", encoding="utf-8-sig", newline="") as outfile:

    buffer = ""
    for line in infile:
        line = line.strip("\n").strip("\r")
        if not line:
            continue
        buffer += line

        # If line is incomplete (too few commas), continue accumulating
        if buffer.count(",") < EXPECTED_COLUMNS - 1:
            buffer += " "
            continue

        outfile.write(buffer + "\n")
        buffer = ""

print("✅ Fixed CSV saved as:", output_path)