By Scott Sumner | AI at Work

Editorial illustration of an office worker sorting record tags while preserving a large purple zero, representing customer IDs that must survive spreadsheet cleanup.

AI spreadsheet cleanup can turn a messy export into something useful. Before you accept the cleaned file, make sure the process has not changed the identifiers your business uses to connect customers, orders and invoices.

Answer: Use AI to inspect and clean a copy of your spreadsheet, with identifier columns preserved as text. Define allowed changes first, keep a change log, and compare original and output values before importing the result into your CRM or website.

The important distinction is between a value you calculate with and a label you match on. A customer ID can contain only digits and still belong in the second group.

Jump ahead

Why customer IDs are not ordinary numbers

Consider a fictional customer record with the ID 00127. As a quantity, 00127 and 127 represent the same number. As identifiers, they may not represent the same record. The receiving system decides how matching works, not the person making the spreadsheet look tidy.

That difference matters when a cleaned file becomes an import. A changed key can fail to match an existing customer, create a duplicate or attach information to the wrong record, depending on the receiving system’s rules.

This is not a problem AI invented. Microsoft documents Excel’s automatic conversions, including removing leading zeros, converting certain strings to dates and truncating numeric data to 15 digits of precision. It recommends treating number codes as text when the original representation needs to be preserved.

AI adds another route through the same problem. A tool may generate a script, choose column types or export a CSV. The model can follow your request to clean the file while making a conversion you did not intend.

My rule: if a field identifies something rather than measures it, do not let the cleanup process reinterpret it without an explicit reason.

A safer AI cleanup workflow

Start with an untouched export

Save the original file separately. Work on a copy and record where it came from. If leading zeros or digits have already disappeared upstream, formatting the damaged column as text will not recover the original values. Go back to the source system.

Only use an AI workspace approved for that data. For a trial, use synthetic records with the same structure instead of uploading a customer list simply because the tool accepts spreadsheets.

Describe each column before asking for edits

You do not need a long specification. A short field map is enough to remove several dangerous guesses:

  • customer_id: identifier, text, preserve exactly.
  • postal_code: text, preserve original value; flag suspicious entries separately.
  • company_name: trim surrounding spaces; do not merge similarly named companies.
  • invoice_total: numeric amount; currency is defined in a separate column.
  • renewal_date: report ambiguous dates instead of choosing a regional format.

These are example rules, not universal definitions. Your CRM may use different fields and matching requirements. Check its import instructions before deciding what a valid output looks like.

Ask for an inspection before a cleanup

Have AI list the columns, inferred types, blank values and potential duplicates first. Ask it to identify uncertain interpretations. A first pass that says “these two records might be duplicates” is more useful than one that silently deletes one of them.

OpenAI’s data-analysis guidance recommends descriptive headers and one record per row. It also says to review generated code, outputs and assumptions before relying on Python-based analysis. Those details matter more than a tidy-looking preview.

Approve narrow changes and keep exceptions

Separate mechanical cleanup from business decisions. Trimming spaces in an approved name column is one operation. Deciding that two branches of a company are really one customer is another.

Ask for an output file, a change log and an exceptions file. The log should identify the original row or stable key, the field changed, the old value, the new value and the rule applied. If a row is removed under an approved rule, it should still be accounted for.

A prompt that defines the job

Here is a starting prompt for a synthetic test file. Replace the field names and rules before using it on real work:

Inspect this spreadsheet first. Do not change it yet. Treat customer_id and postal_code as text and preserve their exact source values, including leading zeros. Report the row count, column names, inferred data types, blank identifiers and duplicate identifiers.

Propose changes only for company_name, where trimming surrounding whitespace is allowed. Do not merge records, fill missing values or interpret ambiguous dates. Put uncertain cases in an exceptions list.

After I approve the plan, create a separate cleaned file and a row-level change log. Compare protected fields against the original, report every mismatch and show the code or method used. Do not describe the file as ready for import until these checks are complete.

A prompt is not a guarantee. It defines the requested behavior. The comparison at the end checks whether that behavior actually happened.

How to check the finished file

Do not stop at “the file downloaded.” Inspect the saved output using the same import method the next system will use. Opening a CSV in a spreadsheet application can trigger another round of automatic conversion.

For Excel, Microsoft’s guidance describes using the Text/CSV import flow and setting protected columns to Text in Power Query. Check the type-conversion steps against the untouched source before loading. Some newer Excel versions also provide Automatic Data Conversions controls; availability varies by version.

Your acceptance check should answer four questions:

  1. Are all source records accounted for? Compare counts and stable identifiers. Any approved deletions or merges need a separate explanation.
  2. Did protected values stay identical? Compare the entire protected column, not just the first few visible rows. A sample is useful for a visual check, not proof that every key survived.
  3. Can every edit be explained? The change log should match the rules you approved. “Standardized” is not a sufficient explanation for altering an identifier.
  4. Does a test import match correctly? Use a sandbox or a small controlled import where available. Check the actual existing record, not merely the import-success notification.

A useful synthetic test includes an ID with leading zeros, a long digit-only identifier, a blank key, a duplicated key and an ambiguous date. You are testing how the workflow handles awkward inputs, not measuring how attractive its output looks.

For larger files, have a developer or analyst review the comparison script independently. Asking the same AI to say “everything is fine” is weaker than checking the values with a repeatable test.

What to do before the next import

This is practical AI work worth doing: a repeatable cleanup that saves manual effort while leaving a trace of what changed. The target is a usable file, not a confident answer.

The same discipline applies when moving product records, customer accounts or service listings into a website. The data model underneath the interface is part of an agent-ready website, even if buyers never see the import file.

Before your next cleanup, pick the columns that must remain untouched and write that rule down. That is the first useful improvement.

If AI-assisted data work feeds your website or customer journey, talk to Sumner Digital about the workflow. We can help identify where a convenient shortcut needs a better check.

Sources

This is evergreen workflow guidance, not a new product announcement. The customer record and field rules above are illustrative. The recommended acceptance checks are my operational advice, not a claim that either vendor guarantees error-free cleanup.