How Often AI Loses Rows From a Spreadsheet

Every assistant now accepts a spreadsheet. You can hand a messy CSV to ChatGPT, Gemini or Copilot, ask it to clean the file, and get something back that looks right. The question almost nobody publishes an answer to is how often the thing handed back is no longer your data.

We run a cleaning pipeline on top of a large language model, and we have spent months measuring how it fails on real customer files rather than on benchmarks. What follows is that catalogue: what went wrong, how often, and why the obvious check does not see it. Every number below came from a file that was actually processed.

We publish it because these failures are not ours alone. They are properties of how language models handle tabular data, and every product built on one inherits them. A tool that never mentions them is not a tool without them.

The row count is not a safety check

The most common assumption in AI data cleaning is that if the model returns the same number of rows it was given, nothing was lost. It is the first check most people write, and it is close to worthless on its own.

On a 500 row register of building permits, the model returned exactly 500 rows and slid the permit counts one column to the right on 154 of them. Every row count check passed. Roughly a third of that file now had numbers sitting under the wrong heading.

The rows that slipped were the sparse ones, where a run of empty cells leaves the model nothing to anchor on. We tried the obvious remedies. Sending the same file as CSV, as CSV with the trailing empty column removed, and as JSON arrays slipped between 99 and 151 rows out of 500 in each case. The input format was not the cause.

A retry does not fix it either, because the failure is systematic rather than random. The same rows slip again.

What catches it now: every returned row is held against the line it came from, cell by cell. A returned value is kept only where it can be proved to be the source value written another way, such as a date reformatted or capitalisation changed. Anything else is replaced with what was in the file.

A summary row looks like furniture to a model

A row labelled Grand Total reads to a language model like a heading or a footer, not like data. With reasoning turned off to control cost, the model left the Grand Total row out of 12 of 16 runs on the same file.

On a financial export, that is the one row somebody is going to check. It is also the failure most likely to be noticed weeks later, because the rest of the table is correct.

What catches it now: the cleaning rules state in writing that a total, subtotal or summary row is a data row. That one sentence removed the failure.

Columns move when a header cell is blank

One sheet in the same register opens with an empty header cell. On 3 of 12 runs, the model moved the sector name to the end of every row. Not some rows. Every row.

That is the dangerous shape of this bug. Because the whole table shifted consistently, there was no odd row to notice and no pairing that looked wrong. The file passed as a clean result.

What catches it now: two things, and both were needed. A written rule to keep every column where the file has it, including one under a blank header, and a separate check that looks for the same columns arriving in a different order. The rule prevents most of it and the check catches what the rule does not.

Valid JSON is not a guarantee of valid JSON

Asking the model for structured output, and setting the response type to JSON, does not mean you get JSON. In roughly a third of single pass runs, the model closed compact JSON with a stray backtick, one or two rather than the three that open a code fence.

We replayed one sample through the real prompt fourteen times. Five failed, every one of them at the identical byte offset. Two production jobs died on the same fault on separate days before it was handled.

The opposite happens too. Sometimes the model finishes a correct table and simply keeps writing past the end of it. That occurred in 6 of 76 batches of one large file, and because each of those batches had to be run again, every one of them was paid for twice.

What catches it now: the reply is read up to the point where its first complete JSON value closes, and anything after that is discarded. A reply that was cut off mid structure stays unreadable on purpose, because repairing that would mean inventing data.

Deduplication invents duplicates

This is the failure with the worst consequences, because the output of a deduplication pass is rows that are gone.

On a company register with no duplicates in it at all, an unchecked fuzzy pass deleted 13 distinct companies. The reason is quietly logical. In register data, numeric columns are often sparse and full of zeros, so two companies whose names differ by a few characters can genuinely agree on every other field in the row. A fuzzy matcher reads that as one record entered twice.

What catches it now: the model's answer is treated as a claim, not an instruction. Every proposed duplicate is checked back against the original rows, and a near match on a name is only accepted when another text column matches outright.

A change report can invent its own changes

If you show a user what the AI changed, that report needs to pair each new row with the row it came from. Pairing by position breaks the moment a row is removed.

On a 500 row sheet where a deduplication step correctly removed 2 rows, a position based pairing reported 244 corrections that never happened. Every row after the removals was paired with its neighbour. The person reading the report then reverted those corrections, which wrote each neighbour's values into the wrong row.

A wrong report is worse than no report, because it is acted on.

What catches it now: rows are paired by identity rather than by position, and when the pairing cannot be trusted the report lists nothing at all rather than guessing.

One record on two lines

Almost every tabular pipeline assumes that one line of a file is one row. Export a table of customer messages and that assumption breaks immediately, because a message contains line breaks.

Split such a file on newlines and one record becomes two lines. The model, reading the content, correctly rebuilds it into a single row and returns fewer rows than it was given lines. A row count guard then reads that as data loss and rejects the answer. The model was right and was penalised for it.

We measured it on a 520 record file containing 83 two line messages. Before the fix the run produced 285 rows and left 273 of 284 dates uncleaned, because most of the file had fallen back to raw text. After the fix it produced 283 rows with every date cleaned.

What catches it now: line breaks inside quoted fields are collapsed before anything counts a row. The important part is what we did not do, which was to tell the model to match the line count. That asks it to invent a row for half a record.

Half a cleaned table is worse than none

Large files are processed in chunks, and a chunk that fails can fall back to the file's original lines. That slice is then uncleaned while the rest of the table is cleaned. It looks fine on screen.

It is not fine, and the reason is worth understanding. A cleaned row carries a normalised date and its uncleaned twin carries whatever the file had. Those two rows no longer match, so a deduplication pass cannot pair them, and identical records survive in the output. This reached us as a paying customer's bug report: duplicates left behind by a duplicate remover.

What catches it now: every reply reports whether it was actually cleaned or fell back. An uncleaned slice is split and retried, and if it still fails the user is told plainly which part of the table is raw.

The limit is not the number of rows

Output token limits break tabular work in a way that is easy to misread as a row limit. One of our features asked the model to return a value for every row. Measured against the live model it produced about 112 output tokens per row against a cap near 65,500, which means it broke at roughly 585 rows.

Our free tier caps files at 500 rows. The feature therefore worked perfectly for every user who could not exceed the limit that was hiding the bug.

Sizing by row count alone is not enough either. A 41 column payroll file once truncated, so we started sizing batches by the number of cells. A month later a six column sheet of long text truncated without ever coming close to that cell budget, because nothing was measuring how large a cell was.

What catches it now: batches are sized by width and by text length together, and whichever gives the smaller batch wins.

The file changes before the model ever sees it

Not every corruption is the model's fault. Several of the worst ones happen in the spreadsheet library that reads the file.

Read a CSV with date parsing enabled and the text 2025-04-22 becomes midnight UTC, which displays as 05:00 in Pakistan and as the previous day across the Americas. A phone number written as +16648820517 loses its plus sign. A currency value of $499.00 becomes the number 499, and the two decimal places are gone before any AI or any report is involved.

Text files also get decoded by guesswork. Given raw bytes with no byte order mark, a spreadsheet library will read a UTF-8 file in its own default codepage and corrupt every accented character in it.

What catches it now: text files are read as typed, and decoded as UTF-8 strictly before any fallback is allowed.

What this means if you use AI on a spreadsheet

None of the above is an argument against using a language model on tabular data. We use one, deliberately, because for messy real world files it is better than the alternatives. The argument is against trusting the output because it looks clean.

If you are pasting a spreadsheet into an assistant, these are the checks worth doing every time:

• Count the rows, then check the values as well. A correct row count is compatible with every cell being in the wrong column.
• Look at the last row. Totals and summary lines are the ones most often dropped.
• Check a date and a phone number. These are the fields most likely to have been silently reformatted or truncated.
• Check two rows that were near a deleted row. Misaligned reports and misaligned pairings show up there first.
• Never accept a deduplication you cannot inspect. Ask what was removed and why, and check two of them by hand.

The scarce thing in this category is not software that cleans your data with AI. Every tool now claims that, and most of them are calling the same handful of models we are. The scarce thing is evidence that nothing was lost on the way.

That is what the checks above became in our own product. Returned rows are held against the file's own lines cell by cell, proposed duplicates are verified against the original rows before anything is deleted, and every change is listed so it can be reverted one at a time. You can try it on a file of your own with the AI Excel cleaner, the CSV cleaner, or the duplicate remover.

If you measure something similar on your own files, we would like to see it. The numbers in this post exist because we kept a record every time something went wrong, and there is no reason that record should stay private.