Fixing Broken CSV Exports: A Field Guide for Messy Data

A logistics coordinator once forwarded me a shipment export insisting it was 'ready to go,' and within thirty seconds of opening it in a code editor rather than a spreadsheet app, three separate problems jumped out: a byte-order mark sitting invisibly before the header row, a tracking-number column that silently switched from eight digits to eleven partway through the file, and an address field with an unescaped comma that had shoved every subsequent value in that row one column to the right. The spreadsheet program had hidden every one of these on open, quietly repairing what it could and rendering the rest just well enough to look fine. None of it was fine.

Trust the raw bytes before you trust the pretty view

The single habit that catches the most CSV problems early is opening the file in a plain text editor before ever opening it in spreadsheet software, and reading the first ten to twenty lines exactly as they're stored. Spreadsheet programs apply a layer of silent correction — reformatting suspicious dates, hiding a leading BOM, guessing at encoding — that's genuinely convenient for a human glancing at data but actively dangerous when you need the file to import cleanly into a script or database that offers none of that forgiveness.

The column-count test that catches shifted rows instantly

Every legitimate data row should contain exactly the same number of fields as the header row. A quick script — or even a spreadsheet formula counting delimiters per row while respecting quoted sections — that flags any row deviating from that count will point directly at the exact line numbers where an unescaped comma, a stray line break, or a missing value has thrown things off, which is dramatically faster than scrolling through thousands of rows hunting for something that looks slightly wrong.

What a byte-order mark actually breaks

A byte-order mark is a handful of invisible bytes some programs prepend to a UTF-8 file to signal its encoding, and while harmless to a human eye, it frequently attaches itself to the very first header name in the file — so a column meant to be read as 'id' actually gets parsed as an unprintable character followed by 'id', which fails to match anything your import script is looking for. This single invisible artifact is responsible for a disproportionate share of 'my column names don't match' bug reports, and it's invisible in a normal spreadsheet view precisely because the spreadsheet software strips it silently on open.

When text turns into garbage: recognizing mojibake fast

Values like 'Résumé' where you expected 'Résumé' are the signature of a file saved in one character encoding and read back using another. This shows up constantly in exports crossing between older systems and modern ones. The fix isn't guessing — check what encoding the file actually claims or appears to be in, then re-save it consistently as UTF-8 without a BOM, which is the safest common denominator for virtually every tool that will touch the file downstream.

The whitespace you can't see but your filters can

A trailing tab character, a non-breaking space copied in from a web page, or extra spaces padding a value all render identically to a clean string in most viewers, but they break exact matching, deduplication, and joins against other tables completely. Running text fields through a dedicated whitespace cleaner before touching the actual values catches this systematically instead of relying on spotting it by eye, which is nearly impossible for characters designed to be invisible.

One date column, four different formats

Mixed date formats are the most common structural mess in real-world exports — 04/03/2025, 2025-03-04, and 'March 4, 2025' sitting in the same column depending on which system or employee generated a given batch of rows. None of these individual values are wrong, but a script assuming a single format will silently misparse the others rather than throwing a helpful error, which is far more dangerous than an obvious failure. Standardize on one format explicitly — ISO 8601's year-month-day ordering removes the ambiguity entirely — rather than trusting automatic date parsing to guess correctly across every row.

Casing inconsistency turns one value into three

'chicago', 'Chicago', and 'CHICAGO' look like the same city to a human but count as three distinct values to any tool doing exact matching, which quietly wrecks pivot tables and grouped reports. Passing text columns through a case converter as a standard cleanup step, applied consistently across the whole file, prevents this specific and very common category of silent data-quality bug.

Confirm structural integrity by converting up, not just cleaning in place

Once row and column integrity look solid, running the cleaned file through a csv-to-json converter is a strong final sanity check, because a flat table can hide problems that become obvious the moment the data needs a real nested structure. Oddly nested arrays where you expected simple scalar values almost always mean a quoting or delimiter issue is still hiding somewhere in the source that the flat CSV view successfully concealed.

Deduplicating without deleting legitimate repeats

Rows that look identical aren't always true duplicates — two separate customers can share a name and city without being the same record. Define uniqueness explicitly across the specific combination of columns that actually determines it for your dataset (order ID plus date, for instance), and only then run a dedicated line-level deduplication tool like a remove-duplicates utility, rather than deleting anything that merely looks repetitive at a glance.

The order to run through, every time

Open the raw text and check for a BOM and mixed line endings; verify every row's field count against the header; confirm the actual character encoding rather than assuming UTF-8; strip invisible whitespace; standardize dates and numeric formats to one convention; normalize casing where matching matters; deduplicate only on a deliberately chosen column combination; and convert to a stricter structured format as a last integrity check before the data goes anywhere that matters.

Questions that come up constantly with messy exports

Why does the file look completely fine in my spreadsheet app but break my script? Spreadsheet software silently auto-corrects dates, encoding guesses, and some whitespace on open, which masks exactly the problems that surface once a stricter tool reads the raw file.

What's the safest default encoding for a new export? UTF-8 without a byte-order mark — it's near-universally supported and avoids the header-matching failures a BOM regularly causes.

Is it ever fine to fix a broken CSV by hand? For a handful of rows, sure. Past a few dozen, a short script or a dedicated tool is faster and far less likely to introduce a second layer of errors on top of the first.

How do unescaped commas actually cause a column shift? A text field like an address containing a comma but no surrounding quotes gets split into two fields during parsing, and every column after it shifts by one position for that single row only, which is why a per-row field-count check finds it instantly.