Start

Home / Big ideas

Idea6-min read+5 pts

Clean Data Before AI: Why Messy Exports Give Messy Answers

With Josh Schultz ·

Answer. Clean data is a table where every row is one real record, every column holds one kind of thing written one way, and nothing extra sits around the table. AI can read messy files, but it has to guess at the messy parts, and you will not see its guesses unless you ask. A few small fixes first protect every answer that comes after.

The picture

Diagram: three problems on the left, "Extra rows on top," "Mixed date styles," and "Duplicate parts," all lead into a box labeled "Fix first," which is circled, and then to "One clean table."

On the left are the three problems that show up most often in an ERP export (the report your business system prints out). Each one feeds into the box in the middle, "Fix first," because the repairs are small and they happen before the AI sees anything. What comes out on the right is one clean table, which is the only thing you want the AI to read.

Think of it like this

Imagine you are counting parts in a stockroom where the same part sits in two bins, one labeled with a capital "MR-1002" and the other with a lowercase "mr-1002." You are a careful counter, and you still get the wrong total, because the labels made one part look like two. The fix is to relabel the bins before you count, not to count harder.

An export works the same way. The AI is the careful counter, and the messy labels, repeated rows, and loose dates are the bins. If you tidy them first, the count comes out right without any guessing.

Why does it matter at work?

A messy file does not usually make AI fail in an obvious way. It makes AI give you a confident answer built on a guess, and you may not find out until the number matters.

Here is an example. Hector Lee, the production planner at Mesquite Ridge Metal Works, pastes the open-orders export into a chat and asks how many pieces are open. The export has a title and a run date above the table, a page footer in the middle, a header row that repeats after the page break, and two lines for the same order. If the AI counts both lines, it says 150 pieces are open when the real number is 85, and a planner who believes 150 may order steel for work that does not exist.

How does it work?

Clean does not mean perfect. It means that a person or a program can read the table without guessing, and four small fixes do most of the work.

Keep one header row and nothing else around the table. Remove the title lines, the run date, the blank rows, and the page footers, because an AI can mistake a footer such as "Page 1 of 2" for a record. If you cannot remove them, tell the AI which rows to ignore.

Write each column one way. A date column that mixes 11/18/2026, 2026-11-20, and "Nov 25" forces a guess, and a date like 12/02/2026 could mean December 2 or February 12. Writing every date as year, month, day, such as 2026-12-02, removes the doubt because only one reading is possible. The same goes for numbers, which should be plain numbers with no words like "pcs" attached.

Keep one row for each real record. Duplicate rows inflate totals, but first decide whether a repeated row is a double print or two real orders, because deleting a real order is its own mistake. An order or line number column makes this easy to settle, and without one you will have to ask someone.

Spell things the same way. Part numbers that differ only in capital letters or a stray space will be counted as different parts by a program, and an AI may or may not notice.

Once the file is clean, you can still ask the AI to look before it calculates. Asking it to list every problem it sees, without fixing anything, is a quick way to find what you missed.

If the same mess shows up every week, treat that as a warning sign, because the better fix may be at the source, which is the point of automating a broken process. When you do have a repeating job, make bad data stop the script shows how to have the job refuse a file that is not clean.

Diagram: the date 12/02/2026 has two arrows, one to "December 2" and one to "February 12," with a note that says "AI has to pick one." Below it, 2026-12-02 has a single arrow to "December 2."

The second diagram shows why date format matters so much. A slashed date can be read two ways, so something has to pick one, while a year-first date can only be read one way.

Where do people get it wrong?

Most mistakes come from assuming the AI will quietly sort out a mess it never told you about. These four are the common ones.

  1. Assuming the AI fixes it silently. It makes a choice and may mention it, but it will not always say so unless you ask what it assumed.
  2. Cleaning the only copy. Keep the original export untouched and clean a copy, so you can always go back and check.
  3. Deleting duplicates without checking. Some repeated lines are real, and removing them hides real orders.
  4. Cleaning by hand every week. If the same cleanup repeats, fix the export itself or automate the cleanup, and do not spend an hour each Monday on it.

Try this in 2 minutes

This test gives AI a messy export and asks the same kind of question two ways. You can use any chat tool.

  1. Copy this export. It has a title and run date on top, an empty row, a repeated header after a page break, one exact duplicate, a lowercase part number, three date styles, and one date with no year:
ShopLine ERP - Open Orders Export
Run date: 11/12/2026
,,,
Part No,Description,Qty Open,Due Date
MR-1001,Mounting bracket small,40,11/18/2026
MR-1002,Mounting bracket large,25,2026-11-20
MR-1001,Mounting bracket small,40,11/18/2026
MR-1015,Drive shaft 2 in.,12,Nov 25
Page 1 of 2
Part No,Description,Qty Open,Due Date
MR-1020,Hydraulic manifold block,8,12/02/2026
mr-1002,Mounting bracket large,25,2026-11-20
  1. In a new chat, paste the export and ask: "How many total pieces are open, and how many pieces are due before December 1, 2026? Give the two numbers first, then one line on how you got them."
  2. In a second new chat, paste the export again and ask: "Before doing any math, list every problem you see in how the data is laid out that could cause a wrong answer. Short bullets, no fixes yet."

A chat where the user pastes the messy export and asks for every problem before any math. The reply lists the junk rows above the header, the repeated header, mixed date formats, and the missing order number, with "No unique key" circled.

When I tried this, the first question gave 85 pieces open and 77 due before December 1, which is right if the repeated lines are double prints. The reply was open about its guesses: it said it counted the repeated MR-1001 row and the lowercase mr-1002 row once, took "Nov 25" as 2026, and added, "If those repeats are separate orders, the totals would be 150 and 142." The numbers were right this time, but only because the guesses happened to be right, and only because it told me what they were.

The second question found the problems before any math. It listed the junk rows above the header, the repeated header, and the three date styles, and it pointed out that "12/02/2026 could be Dec 2 or Feb 12." It also made the most useful point of all: "No unique key: there's no order, PO, or line ID column to tell duplicates apart from separate orders." On a file with 20 rows you will spot a guess, but on a file with 2,000 rows you will not, so it is worth asking the AI to list its concerns first.

Frequently asked questions

What is clean data?

Clean data is a table with one header row, one record per row, and one format per column, with nothing extra around it. A person or a program can read it without guessing what anything means.

Can AI clean my data for me?

Yes, it can do much of the work, such as unifying dates or removing repeated header rows. You still need to check the result against the original, because it will make choices about anything unclear.

What date format is safest?

Year, month, day, written as 2026-12-02. Only one reading is possible, so neither a person nor an AI has to guess which number is the month.

Should I delete duplicate rows?

Only after you confirm that they are really duplicates. A double print is safe to remove, but two real orders for the same part and quantity are not.

Use it today

From my training: we clean the export before we ask a single question about it.

Ideas are worth 5 points. Guides are worth 10 and up.

Get your first win this week.

One minute. Three guides picked for your job.

Start my first win