I sell a small Python tool that takes a folder of messy exports — CSV, TSV, Excel — and turns them into one clean workbook. Its whole pitch is that nothing is changed silently: every alteration is written to a Summary sheet so the client can check the work instead of trusting it.
Two days ago I found out it had been silently destroying money columns. Then I found the same defect in two more tools. Then a different defect, in all three. This is what happened, in order, because the order is the lesson.
Day one: 4141.98 becomes the year 4141
The tool guesses column types. A column of text that looks like dates becomes dates, text that looks like numbers becomes numbers. To decide whether a value might be a date before spending a parser on it, there was a cheap regex:
DATE_HINT = re.compile(r"\d[-/.]\d|\d{1,2}:\d{2}|[A-Za-z]{3,}")
Digit, separator, digit. 03.02.2026 matches. So does 4141.98.
That alone would be harmless, because the parser should reject 4141.98 as a date. It doesn't. pd.to_datetime("4141.98", format="mixed") happily returns the year 4141. And a column that has already been read as float — 4821.55 — goes through a different path and comes out as 1 January 1970 plus 4821 nanoseconds.
Nothing crashed. The workbook opened. The Summary said "columns to dates: 1". The invoice amounts were gone.
How it was found: not by the 123 tests, not by the 52 adversarial inputs, not by the 600,000-row performance run. A client-facing demo needed fresh sample data, and this time I wrote the amounts with "{:.2f}" — no thousands separator. Every previous fixture had written 4,141.98, and the comma breaks the date heuristic before it starts. A hundred and twenty-three tests had been checking the same shape of number over and over.
The fix: a date written in digits needs two separators, and a column of bare numbers is vetoed before any parsing is attempted.
DATE_HINT = re.compile(r"\d[-/.]\d+[-/.]\d|\d{1,2}:\d{2}|[A-Za-z]{3,}")
BARE_NUMBER = re.compile(r"^[-+]?[\d\s]*[\d][\s\d]*(?:[.,]\d+)?$")
Fourteen new tests. Done, I thought.
Day two: two separators are necessary but not sufficient
The next evening I was auditing a second tool — a web scraper that writes the same kind of workbook — and tried a column of product sizes: 10.5.2, 11.5.3, 12.5.4.
['Widget A', 1234.56, datetime.datetime(2002, 5, 10, 0, 0)]
Ten May 2002. Two separators, exactly as required. The fix from day one was correct and useless here.
Worse: it was unstable. Article numbers like 10.20.30, 11.20.31, 12.20.32 — three of them became dates. Add a fourth, 13.20.33, and month 13 fails to parse, the share of parseable values drops below the threshold, and the whole column stays text. Same site, same selector: scrape three items and lose the column, scrape four and keep it. No test will ever see a bug that depends on how many rows the page had that day.
The rule that actually separates a date from a size is not the number of separators. It is the year: written with dots, a date carries a four-digit year. 03.02.2026 is a date. 10.5.2 is a size. Slashes and dashes keep the two-digit year, because 12/31/26 is how half the world writes it.
DATE_HINT = re.compile(
r"\d{1,2}[-/]\d{1,2}[-/]\d{2,4}(?!\d)" # 31/12/26, 31-12-2026
r"|\d{4}[-/.]\d{1,2}[-/.]\d{1,2}(?!\d)" # 2026-01-12
r"|\d{1,2}\.\d{1,2}\.\d{4}(?!\d)" # 03.02.2026 - dots need a full year
r"|\d{1,2}:\d{2}"
r"|[A-Za-z]{3,}")
The part that changed how I work
The scraper had the original one-separator regex. So did a third tool that pulls JSON from APIs. All three had been written from the same template, and a defect in the template had been copied faithfully into every copy.
Once I looked, it wasn't just the regex:
| defect | found in | also present in |
|---|---|---|
| one-separator date heuristic | CSV cleaner | scraper, API tool |
requirements.txt says pandas>=1.5; code uses format="mixed", which needs 2.0 |
CSV cleaner | scraper, API tool |
float(Retry-After) — crashes when the header is an HTTP date, which the spec allows |
scraper | API tool |
10.5.2 read as a date |
scraper | CSV cleaner |
Four times in twenty-four hours, the same sentence: found in one, present in the others.
The rule I now follow, written on the wall: when you find a defect in one tool, check every sibling the same day. Not next sprint. The same day, while you still remember exactly what the defect looks like.
Day three: not a date bug at all
With the date rule fixed in all three tools, I ran the API tool against a real public API and opened the result to look at it. The phone column read:
8,025,285,988
8,028,575,318
Money formatting on a telephone number. The API had sent "8025285988" as a string. The tool saw ten digits, called it a number, and Excel added the commas. Any leading zero would have been dropped for good.
Then I checked the CSV cleaner with a column of postal codes:
['05401', '05672', '05344'] -> [5401.0, 5672.0, 5344.0]
The leading zero is gone and it is not coming back. Of everything in this post, that is the one you cannot recover from.
The guard is two cheap signals. A value that starts with 0 and has more digits after it is an identifier at any length — no quantity is written 05401. And a long unbroken run of digits where every value in the column is the same width is an identifier too: phones, accounts and barcodes are fixed-width, populations and amounts are not.
LONG_DIGITS = re.compile(r"^\d{7,}$")
LEADING_ZERO = re.compile(r"^0\d+$")
def is_identifier(vals, threshold=0.8):
if vals.map(lambda v: bool(LEADING_ZERO.match(v))).mean() >= threshold:
return True
longs = vals.map(lambda v: bool(LONG_DIGITS.match(v)))
if longs.mean() >= threshold:
widths = {len(v) for v, k in zip(vals, longs) if k}
return len(widths) == 1
return False
In the API tool there is a third signal, and it is the strongest: the source sent the digits as a string. A JSON number arrives as a number and never reaches this code. If the API bothered to quote it, it is telling you it is not a quantity.
What the tests were doing all this time
Passing. All of them. 123, then 137, then 150.
None of these defects crashed. Every one finished, wrote a workbook that looked correct, and was wrong. That is the only kind of failure that reaches a user, and it is the kind a test suite is worst at, because a test checks what you thought of, and you can only think of what you have already seen.
Three of the four were found by looking at the output — a demo image that happened to use different numbers, a preview of a listing where the phones had commas, a summary sheet opened in LibreOffice where a label was cut in half. None of those are tests. All of them are now.
The suite is at 167 checks, 35 of them marked REGRESSION — each one a bug that was in the code and shipped nothing, because it was caught. I keep the count honest by counting them with a command, after discovering that a number I had typed into the README from memory was wrong by two.
If you type-coerce columns, three questions
-
Does your date heuristic accept a bare decimal? Try
4141.98. Try a float4821.55. Try10.5.2. -
What happens to
05401? If the answer is 5401, you are destroying postal codes, account numbers and product codes, and nobody will tell you. - Do your fixtures all write numbers the same way? Mine did. A hundred and twenty-three tests, one shape of number.
And the one that isn't about code: do you have a sibling tool built from the same template? Go and check it. Today.
The tool, its tests and the adversarial suite are public: github.com/kerimff7-rgb/csv-excel-cleaner. Every number in this post can be reproduced from it. If you find one that can't, I would like to know.
Top comments (0)