How to clean a CSV file

Most CSV problems come from the same handful of places: the system that exported it, the person who last edited it by hand, and whatever program opened it in between. Running through these checks in order catches the majority of what breaks a downstream import, report, or mail merge — and the order matters, because several of these steps will hide each other if you run them the wrong way round.

1. Open it as text before you open it in a spreadsheet

This is the step almost everyone skips, and it is the only one that shows you what is actually in the file. A spreadsheet program does not display a CSV — it parses it, guesses at each column's type, and shows you the result of those guesses. By the time you are looking at a grid, leading zeros may already be gone and dates may already have been rewritten.

Open the raw file first in Notepad, TextEdit, or any code editor, and read the first few lines. You are checking three things:

• The delimiter. Commas are the default, but exports from European systems often use semicolons, and tab-separated files are frequently still named .csv.
• The quoting. Values containing a comma should be wrapped in double quotes. If you see a row with more commas than the header has columns and no quotes in sight, the file is already broken and no amount of spreadsheet cleanup will fix it — go back and re-export.
• The header row. Some systems emit a title line, a blank line, or an export timestamp above the real headers. Delete those before anything else reads the file.

Two minutes here saves you from diagnosing a mangled column an hour later.

2. If the whole file opened in one column

This is the most common CSV complaint there is, and it is almost never a corrupt file. It means the delimiter in the file does not match the delimiter your spreadsheet expects — most often a semicolon-separated export opened on a machine configured for commas, which is the normal state of affairs for files produced anywhere in continental Europe.

Step 1 tells you which of these you have in about five seconds.

Do not fix this with find-and-replace. Swapping every semicolon for a comma will also swap the ones inside quoted values, which silently splits "Nair, Priya" into two columns and shifts every field after it. Instead, import the file and declare the delimiter: in Excel, Data → From Text/CSV, then choose the delimiter in the preview. In Google Sheets, File → Import and set the separator type explicitly.

A tab-separated file is worth renaming to .tsv once you have identified it, so the next person to open it does not repeat the diagnosis.

3. Check for duplicate rows

Sort by the column most likely to repeat — usually email, since it is the closest thing most exports have to a unique key. Duplicates are easy to miss because they almost never match character-for-character:

Four rows, one person. Rows 2 and 3 differ only in capitalization and a trailing space.

Row 3 is the one worth understanding. It ends in a single space, which is invisible in every spreadsheet view, and it is enough to defeat an exact-match duplicate check in any tool. Casing behaviour, by contrast, varies between programs — so the same file can give you different results depending on where you open it. Rather than remembering which does what, normalize casing and whitespace first (steps 4 and 5), then deduplicate against the normalized column.

Row 4 is a different problem entirely. No formatting fix makes "P. Nair" match "Priya Nair"; catching that requires matching on the email column instead, which is why choosing your key matters more than choosing your tool.

4. Fix inconsistent capitalization

Names and free-text fields entered by hand end up in a mix of cases. In Excel or Google Sheets, a helper column fixes most of it:

PROPER() capitalizes the first letter of each word and lowercases everything else. "Most" is doing real work in that sentence — the rule it actually applies is capitalize any letter that follows a non-letter, which is not the same as how names are capitalized:

The apostrophe rule that gets O'Connell right is the same rule that gets Bob's Plumbing wrong.

Worth being blunt about this one: it is a limitation of the rule, not of Excel. Automatic title-casing in any tool — spreadsheet formula, script, or cleaning service, DataMadeClean included — applies the same letter-after-a-non-letter logic and produces the same three failures above. There is no setting that fixes it, because "McDonald" and "Macquarie" cannot be told apart by a rule that only looks at characters.

So the practical approach is to decide which columns should be casing-normalized at all. A column of personal names is a good candidate. A column that mixes people with company names is not — you will spend longer repairing Abc Pty Ltd than you saved. Split the two into separate columns if you can, and if you cannot, leave casing alone there and sort the column to review anything containing an apostrophe or already in capitals.

5. Strip extra whitespace

A trailing space is invisible in a cell but breaks exact-match lookups, duplicate detection, and joins against other data. The usual fix:

TRIM() removes leading and trailing spaces and collapses repeated internal spaces to one. What it does not touch is the non-breaking space, character 160, which is what you get when someone copies a value out of a web page or a PDF. It looks identical to a space and survives TRIM() untouched:

To find out whether you have this problem at all, compare a suspect cell's length against its trimmed length — if they differ after a plain TRIM(), something invisible is still in there:

Run that down the column and sort descending. Anything above zero has whitespace a plain trim did not remove. CLEAN() is sometimes suggested here, but it only strips control characters 0–31 and leaves character 160 in place, so it is not a substitute for the SUBSTITUTE() above.

Zero-width characters are the same problem one step further on. A zero-width space or a stray byte-order mark has no width at all, so the cell looks completely normal and still fails every comparison you make against it. LEN() is the only thing that gives them away.

6. Standardize date formats

A date column assembled from more than one source often mixes formats, sometimes within the same column:

Only the first is genuinely ambiguous — but it is also the most common.

Check the date convention before converting values. A value such as 13/04/2026 supports day-first interpretation for that source, but does not prove that rows merged from other sources use the same order. Look for conflicting evidence and confirm the source convention before converting ambiguous dates.

If no value anywhere in the column has a first number above 12, the file genuinely cannot tell you, and you have to check the exporting system's settings. Guessing here silently corrupts up to twelve days in every month, and produces a file that looks completely reasonable afterward.

The last row is a separate failure. If a cell shows a five-digit number where a date should be, the value is stored as a date and displayed as a number; formatting the cell as a date restores it. The reverse — a date stored as text — is the more common problem, and step 8 covers how to spot it.

7. Strange characters where accented letters should be

If names or addresses show "café" where "café" should be, the file was written as UTF-8 and then read as Windows-1252 somewhere along the chain. The tell is a capital Ã, Â, or †appearing immediately before an otherwise-sensible character. This is called mojibake, and the useful thing to know is that not all of it is equally damaged:

Mojibake is reversible. Replacement characters are not.

The distinction is worth knowing before you spend time on it. Mojibake like "café" means the original bytes are all still present and merely being decoded with the wrong table — re-importing the file and explicitly choosing UTF-8 recovers it exactly. A literal question mark or a � replacement character means something already converted the byte and discarded it, and nothing recovers that; the only fix is a fresh export from the source system.

Resist fixing this with find-and-replace. It is tempting, because a single file usually contains only a handful of distinct broken sequences, but you are guessing at a mapping that a re-import performs exactly. In Excel, use Data → From Text/CSV rather than double-clicking the file, which lets you set the encoding explicitly instead of accepting a guess.

8. Look for empty and mistyped required fields

Filter each required column for blanks before importing anywhere. A blank email or phone number usually means the row failed to capture properly upstream rather than the value being genuinely optional, and it is worth knowing which of the two it is before the import decides for you.

Watch also for values that are present but stored as the wrong type — the classic being numbers held as text, which Excel flags with a small green triangle in the corner of the cell. A date column where some cells left-align and others right-align is showing you the same thing: the right-aligned ones are real dates, the left-aligned ones are text that merely looks like dates, and any sort or filter will treat them as two different things.

9. Check that Excel has not eaten your leading zeros

Cleaning a CSV in a spreadsheet and saving it back out is itself a lossy operation, and it is the step that undoes people's work most often. Excel decides each column's type on open, and a column of digit strings looks like numbers to it:

Phone numbers, postcodes and account IDs are the usual casualties — all digit strings that are not numbers.

Two things make this worse than it first looks. The damage happens on open, before you have done anything, so the file you are editing is already wrong. And reformatting the column as Text afterwards does not bring the zeros back — the value is now genuinely the number 800, and formatting only changes how it is displayed.

The fix is to control the type at import: Data → From Text/CSV, then set the affected columns to Text in the preview rather than General. If the file has already been opened and saved, go back to the original export; if the original is gone, a zero can sometimes be re-derived from context — an Australian postcode is always four digits, so =TEXT(A2,"0000") repairs a column of them — but only where the correct width is genuinely known.

Then re-open the saved file as text (step 1) and check that the columns you did not touch still look the way they did.

The checklist

1. Read the raw file as text — delimiter, quoting, header row.
2. If it opened in one column, set the delimiter on import rather than find-and-replacing it.
3. Normalize casing and whitespace before deduplicating, not after.
4. Only casing-normalize columns that hold personal names, not company names.
5. Use LEN() against TRIM() to find invisible characters.
6. Prove your date order from one decisive value, then apply it column-wide.
7. Fix encoding by re-importing as UTF-8, not by find-and-replace.
8. Check blanks and cell types in every required column.
9. Import digit-string columns as Text so leading zeros survive.

Common questions

You can clean a CSV without opening it in Excel. Use a text editor to inspect the raw file, or upload it to a cleaning tool and review the changes. If you use a spreadsheet, configure its import options to preserve identifiers and check the interpreted dates.

Why does my CSV look fine in Notepad but wrong in Excel? Because Notepad shows you the file and Excel shows you its interpretation of the file. If the two disagree, the file is fine and the import settings are wrong — which is good news, since it means nothing has been lost yet.

How big a CSV can Excel handle? A worksheet stops at 1,048,576 rows. A larger file will open truncated, and in some versions without an obvious warning, so check the row count against the source system rather than trusting what you see.

Should I clean the CSV or fix the export? Fix the export where you can. Everything in this guide has to be redone every time a new file arrives; a corrected export setting — UTF-8, quoted fields, ISO dates — solves it once. Cleaning is for data you do not control the source of.