How to clean up messy spreadsheets with AI

What this automation does

A messy sheet goes in, and a clean, import-ready sheet comes out with a log of every change and a short list of rows a person needs to decide on.

1. Ingest. The file arrives in a watched Google Drive or OneDrive folder, or someone drops it in a form. The workflow reads it and assigns every row a permanent row ID.
2. Profile. Code computes per-column statistics: blanks, distinct values, types detected, outliers and suspected duplicates. The model reads the profile and a sample of rows, not the whole file.
3. Propose a plan. The model returns a structured cleaning plan: which columns to trim, which date formats to parse, which values look like the same category, which columns to split, and which rows look like totals or notes rather than data.
4. Apply with code. Deterministic steps do the bulk work: trimming, case, date parsing, phone normalization, splitting columns and removing exact duplicates.
5. Judge the leftovers. Only the values code cannot handle go to the model in small batches, with a fixed list of allowed outputs, for example mapping 300 distinct industry labels to your 12 standard ones.
6. Verify and deliver. Row counts, numeric totals and required fields are checked. The clean sheet, a change log and a review list of low-confidence decisions are written to new tabs.

When it is worth building

Build it when the same kind of mess arrives on a schedule: a weekly supplier price list, a monthly export from a franchisee, event registration lists, or CRM exports before every campaign. The second time you clean the same file by hand, the workflow starts paying back.

For a one-time cleanup before a migration, do not build a pipeline. Use a chat assistant that runs code on the file, keep the original, and verify counts yourself. If the real problem is that data keeps arriving messy, fix the source with validated forms or a form-to-database workflow so you stop cleaning the same issues. And if the cleaned data feeds another app on a schedule, the data sync guide covers keeping it consistent afterward.

Architecture

How to build it

1. Define “clean” for the destination. Write down the target columns, types, allowed values and required fields, usually from the import spec of the system the data goes into, such as your CRM.
2. Lock the source. Read the original read-only. Add a row ID column in the working copy before anything else happens.
3. Write the profiler. Distinct counts, blank rates, inferred types, the top 20 values per column, and rows that do not look like data, such as subtotal lines and repeated headers.
4. Ask for a plan, not a result. Give the model the profile, a 50-row sample and the target spec, and ask for a list of operations in a fixed JSON format. Review the plan once by hand for each new file type, then reuse it.
5. Implement operations as code. Each operation in the plan maps to a tested function. The model chooses operations; it never edits cells directly.
6. Batch the judgment calls. Send distinct messy values, not rows, to the model with the allowed output list. Cache answers so the same value is never sent twice.
7. Verify before writing. Compare row counts and column totals, confirm required fields, and fail loudly if anything is off rather than writing a partial result.

What breaks in production

• Leading zeros and long numbers. With default settings, opening a CSV in Excel strips leading zeros from ZIP codes and truncates IDs longer than 15 digits, showing them in scientific notation. Current Microsoft 365 and Excel 2024 versions let users turn these conversions off, but you cannot count on every sender having done so. Read those columns as text from the start.
• Excel date conversions. Text like “3-4” or certain product codes are converted to dates on open, and dates arrive as serial numbers in exports. Detect date-like columns explicitly instead of trusting the file’s types.
• Rows silently dropped by the model. This is why rows never pass through the model as a whole and why row counts are checked at the end.
• Over-eager deduplication. Two “John Smith” rows at different companies merged into one. Only auto-merge exact matches on strong keys, and send fuzzy matches to the review list.
• Header rows that move. A supplier adds a logo row or a second header line. The profiler should find the real header row instead of assuming row one.
• Category lists that change. New messy values appear every month. Unknown values go to review and, once decided, join the cached mapping.

Timeline and cost

A recurring cleanup workflow for one file type, with profiling, a reviewed cleaning plan, value mapping and verification, typically takes one to three weeks. Lumeor builds this in the range of $3,000 to $10,000, depending on the number of file types, data volume and how many destination systems need specific formats. Model costs are low because only distinct values and profiles are sent, not whole files. See what workflow automation costs for how projects are priced.

How to measure success

• Analyst hours per file, before and after
• Share of rows that pass destination validation on first import
• Size of the human review list per run, which should shrink as the cached mapping grows
• Row count and total mismatches caught by verification (each one investigated)
• Duplicate records created in the destination system after import