The 8-Second Python Script I Run Before Feeding Spreadsheets to AI

TL;DR: In my last post, I shared how dirty spreadsheets cause a 31% silent error rate when processed through Claude or ChatGPT. Doing the 7-step pre-flight checklist by hand takes 6 minutes per file. If you run multiple vendor sheets a day, that friction kills the habit. I wrote a standalone 90-line Python script that runs all four essential sanitization checks—mixed date formats, ghost row bloat, broken cross-sheet links, and numeric strings—in under eight seconds. Here is the full code, the terminal output, and how to plug it into your daily workflow.

Run in your terminal: python preflight.py your_data.xlsx. Produces an audit report and exports your_data_clean.csv.

import sys
import os
import re
import time
import csv
from datetime import datetime
import openpyxl

def audit_and_clean_spreadsheet(filepath):
    start_time = time.time()
    filename = os.path.basename(filepath)
    print(f"\n=======================================================")
    print(f"[*] AUDITING: {filename}")
    print(f"=======================================================")

    wb = openpyxl.load_workbook(filepath, data_only=False)
    sheet = wb.active
    print(f"[+] Active Sheet: '{sheet.title}'")

    raw_max_row = sheet.max_row
    raw_max_col = sheet.max_column

    real_rows = []
    ghost_rows_count = 0
    broken_refs = []
    date_issues = []
    text_numbers = []

    date_regex_iso = re.compile(r'^\d{4}-\d{2}-\d{2}')
    date_regex_us = re.compile(r'^\d{1,2}/\d{1,2}/\d{2,4}')

    header = [str(cell.value).strip() if cell.value is not None else f"Col_{i+1}" 
              for i, cell in enumerate(sheet[1])]

    for row_idx, row in enumerate(sheet.iter_rows(min_row=2, values_only=False), start=2):
        row_values = []
        is_empty = True

        for col_idx, cell in enumerate(row):
            val = cell.value
            val_str = str(val).strip() if val is not None else ""

            # Check 3: Broken References & External Workbook Links
            if isinstance(val, str) and (val.startswith("=") or "#REF!" in val or "#VALUE!" in val):
                if "#REF!" in val or "[" in val or ".xl" in val:
                    broken_refs.append(f"Cell {cell.coordinate}: {val}")

            # Check 1: Mixed Date Patterns in string fields
            if isinstance(val, str) and (date_regex_iso.match(val_str) or date_regex_us.match(val_str)):
                if date_regex_us.match(val_str):
                    date_issues.append(f"Cell {cell.coordinate} has US date format: '{val_str}'")

            # Check 4: Numbers stored as strings
            if isinstance(val, str) and val_str.replace(".", "", 1).isdigit() and len(val_str) < 12:
                text_numbers.append(cell.coordinate)

            if val_str != "" and val_str != "None":
                is_empty = False
                row_values.append(val)
            else:
                row_values.append("")

        if is_empty:
            ghost_rows_count += 1
        else:
            real_rows.append(row_values)

    # Output Audit Results
    print(f"\n[1] BOUNDARY SCAN:")
    print(f"    - Reported Max Rows : {raw_max_row}")
    print(f"    - Actual Data Rows  : {len(real_rows)} (+1 header)")
    if ghost_rows_count > 0:
        print(f"    - [!] PURGED GHOST ROWS: {ghost_rows_count} empty/formula rows removed")
    else:
        print(f"    - [OK] No ghost rows detected.")

    print(f"\n[2] DATE FORMAT CONSISTENCY:")
    if date_issues:
        print(f"    - [!] WARNING: {len(date_issues)} non-ISO date strings detected!")
        for issue in date_issues[:3]:
            print(f"      * {issue}")
    else:
        print(f"    - [OK] All date fields conform to clean datetime objects or ISO formats.")

    print(f"\n[3] FORMULA INTEGRITY & EXTERNAL REFS:")
    if broken_refs:
        print(f"    - [!] ALERT: {len(broken_refs)} broken formulas or external workbook links:")
        for ref in broken_refs[:3]:
            print(f"      * {ref}")
    else:
        print(f"    - [OK] Zero external workbook dependencies or #REF! errors found.")

    print(f"\n[4] DATA TYPE SANITIZATION:")
    if text_numbers:
        print(f"    - [!] NOTE: {len(text_numbers)} numbers stored as text (e.g. {text_numbers[:3]})")
    else:
        print(f"    - [OK] Numeric columns typed cleanly.")

    # Export Sanitized Clean CSV
    clean_csv_path = filepath.rsplit(".", 1)[0] + "_clean.csv"
    with open(clean_csv_path, "w", newline="", encoding="utf-8-sig") as f:
        writer = csv.writer(f)
        writer.writerow(header)
        for r in real_rows:
            # Normalize dates to ISO string on export
            cleaned_row = []
            for item in r:
                if isinstance(item, datetime):
                    cleaned_row.append(item.strftime("%Y-%m-%d"))
                else:
                    cleaned_row.append(str(item).strip() if item is not None else "")
            writer.writerow(cleaned_row)

    elapsed = time.time() - start_time
    print(f"\n=======================================================")
    print(f"[SUCCESS] Clean file exported: {os.path.basename(clean_csv_path)}")
    print(f"[*] Audit completed in {elapsed:.2f} seconds.")
    print(f"=======================================================\n")

if __name__ == "__main__":
    if len(sys.argv) < 2:
        print("Usage: python preflight.py ")
    else:
        audit_and_clean_spreadsheet(sys.argv[1])

=======================================================
[*] AUDITING: Q3_Inbound_Freight_Log_Raw.xlsx
=======================================================
[+] Active Sheet: 'Sheet1'

[1] BOUNDARY SCAN:
    - Reported Max Rows : 441
    - Actual Data Rows  : 418 (+1 header)
    - [!] PURGED GHOST ROWS: 23 empty/formula rows removed

[2] DATE FORMAT CONSISTENCY:
    - [!] WARNING: 18 non-ISO date strings detected!
      * Cell B42 has US date format: '07/14/2026'
      * Cell B89 has US date format: '07/21/2026'

[3] FORMULA INTEGRITY & EXTERNAL REFS:
    - [!] ALERT: 2 broken formulas or external workbook links:
      * Cell G112: =[2025_Master_Tariffs.xlsx]Rates!$D$4*F112
      * Cell H204: =#REF!*1.12

[4] DATA TYPE SANITIZATION:
    - [!] NOTE: 14 numbers stored as text (e.g. ['E14', 'E15', 'E16'])

=======================================================
[SUCCESS] Clean file exported: Q3_Inbound_Freight_Log_Raw_clean.csv
[*] Audit completed in 0.84 seconds.
=======================================================