A messy CSV rarely has one problem. It has five, layered on top of each other: duplicate rows, blank cells, stray spaces, inconsistent headers, and dates written three different ways. Each problem alone is a nuisance; together, they block whatever comes next — an import that fails, an analysis that miscounts, a chart that plots nonsense. The temptation is to fix the mess in a spreadsheet with a flurry of formulas, but that route is slow, error-prone, and hard to repeat when the next export arrives just as messy.

CSV Cleaning Studio handles the whole pass in the browser: remove duplicates, fix missing values, trim whitespace, rename and reorder columns, standardise dates, and export safely — for files up to 20 MB, without the file leaving your device. This post works through all five messes on a single fictional file, in the order that makes each fix stick.

Mess 1: duplicates that inflate every count

Duplicate rows are the most expensive mess because they distort everything downstream. A customer list with 200 rows, 30 of them duplicates, overstates the customer count by nearly a fifth, and any total built on it — orders, revenue, responses — is inflated by the same logic. Duplicates usually arrive innocently: an export run twice, a form submitted twice, a merged list with overlapping entries.

Removing them sounds simple, but the definition matters: what counts as a duplicate? Two rows identical in every column are easy. Two rows identical except for a stray space or a different capitalisation are duplicates in meaning but not in exact text — which is why trimming and standardising should happen before or alongside deduplication, so near-duplicates become exact duplicates and are caught. Deduplicate on the columns that define identity for your purpose (often an email address or an order ID), not blindly on the whole row, because two genuinely different orders from the same customer are not duplicates.

Mess 2: blanks, and the question of what they mean

A blank cell is ambiguous: it can mean "not applicable," "not yet known," "the respondent skipped it," or "the export dropped it." Cleaning does not resolve that ambiguity — only the data's owner can — but it can make the ambiguity explicit and consistent. The choices are usually: leave blanks blank, fill them with a standard placeholder (such as "Unknown"), or fill them from another column or a default value. What matters is choosing one rule per column and applying it everywhere, rather than leaving a mixture of blanks, dashes, "N/A," and "none" that downstream tools will treat as five different values.

A practical rule: for columns that will be grouped or counted, standardise blanks to one representation. For columns that will be averaged, think before filling — filling blanks with zero, for example, drags an average down and invents data that was never measured. When in doubt, keep the blank and let the analysis handle missing values explicitly; a visible gap is more honest than a fabricated number.

Mess 3: stray spaces and messy headers

Stray spaces are invisible and therefore treacherous. "London" and "London " look identical in a spreadsheet view but are different values to a computer, so grouping by city splits London in two, and a lookup for "London" misses the spaced version. Trimming whitespace from the start and end of every cell fixes this in one pass, and it should be done early, because it turns near-duplicates into exact duplicates for the deduplication step.

Headers deserve the same treatment. Exports often arrive with headers like "First Name," "first_name," and "FIRSTNAME" across different files, or with trailing spaces and inconsistent capitalisation within one file. Renaming columns to a single consistent style — lowercase with underscores, for example — and reordering them into a logical sequence makes the file usable by the next person and the next tool without a translation step. A clean header row is also what makes a file importable: many import failures trace back to a header the destination did not recognise.

Mess 4 and 5: mixed dates, then a safe export

Dates written in mixed formats are the mess most likely to corrupt silently. A column containing 03/04/2026, 4 March 2026, and 2026-03-04 may look like three different dates to an importer, or worse, may be read as day-month in one row and month-day in another, swapping March 4 and April 3 without any error message. Standardising every date in a column to one format — ISO format (YYYY-MM-DD) is the safest choice — removes that ambiguity. (The date-format detective post in this series works through this mess in full.)

Finally, export safely. A cleaned CSV should be exported with proper quoting so that commas inside values (an address, a product name) do not split a field in two on re-import, and with a consistent character encoding so accented characters survive. Checking the export by re-opening it and spot-checking a few rows — especially rows that contained commas or special characters — closes the loop: the file that leaves the cleaner should be the file the next tool can actually use.

Worked example: a fictional mailing list, cleaned

A fictional charity, Example Community Trust, exports a mailing list of 240 rows for a newsletter import. The file's problems are typical: 18 duplicate rows from a double export, 40 blank cells spread across the donation column, city names with trailing spaces ("Leeds " versus "Leeds"), headers mixing "Email Address" and "email," and a signup-date column mixing 12/01/2026, 12 January 2026, and 2026-01-12.

The cleaning pass runs in this order: trim whitespace first, so spaced duplicates become exact; rename headers to a consistent style (first_name, last_name, email, city, signup_date, donation); remove duplicates on the email column, dropping the 18 repeats; standardise blanks in the donation column to a single explicit marker the charity chose, "not recorded," rather than inventing amounts; standardise all signup dates to YYYY-MM-DD; and export with quoting. The result is 222 clean rows with consistent headers, one city spelling, one date format, and no hidden duplicates. The import that failed twice before now succeeds — and the newsletter counts, for the first time, describe the charity's actual supporters.

Honest limitations

Cleaning improves a file; it does not verify it. A misspelled name, a wrong email address, or a donation amount typed as £500 instead of £50 will survive every cleaning step, because those are content errors, not format errors. Cleaning also involves judgement calls — what counts as a duplicate, what a blank means — and different choices produce different files from the same input. Document the choices you made, especially for a file others will reuse, and keep the original export untouched so any decision can be revisited. For the next step, a cleaned file is ready for Chart Builder to visualise, and JSON Studio can help if the data also needs a structured format. The background guide is how to clean CSV files.

FAQs

How do I remove duplicate rows from a CSV?

Identify the column or columns that define identity for your purpose — often an email address or an order ID — trim whitespace first so near-duplicates become exact, then deduplicate on those columns. Deduplicating on the whole row can miss duplicates that differ by a space or a capital letter.

What should I do with missing values in a CSV?

Choose one rule per column and apply it consistently: leave blanks blank, use a single explicit placeholder such as "Unknown," or fill from a default. Avoid filling numeric blanks with zero unless zero is genuinely the value, because it distorts averages.

Why does trimming whitespace matter?

Because "London" and "London " are different values to a computer, even though they look identical. Trailing spaces split groups, break lookups, and hide duplicates. Trimming every cell is a small step that fixes several downstream problems at once.

Should I keep the original messy file?

Yes. Keep the original export untouched alongside the cleaned version, so any cleaning decision — what counted as a duplicate, what a blank was filled with — can be revisited or reversed later.

Related reading: Once the file is clean, chart it.

human review pending client designation