Clean a messy CSV export: the problems to check, in order

To clean a messy CSV export, fix it in layers: the characters first, then the layout, then what each column means. A CSV is plain text, so most problems are wrong characters, extra rows or separators, or values that a program reads differently from how you do. Fix the characters first. A hidden space can stop a date from being read as a date, and the later fixes then fail for no clear reason.

CSV cleanerMessy CSV with blanks and copiesDo it free

Characters that look right and are not

Stray spaces. A space at the start or end of a cell, or two spaces in the middle of a value, makes "Berlin " and "Berlin" two different values to a lookup, a filter or a join.

Invisible characters. Some characters take no room on screen:

• A non-breaking space (U+00A0) looks like a normal space but is a different character. Web pages use it to keep words together.
• A zero-width space (U+200B) has no width at all.
• A byte order mark (U+FEFF) can sit at the very start of a file, in front of the first header. The column looks like "id" but is really "id", so a lookup for "id" fails.

Remove hidden characters shows what it finds before it changes anything.

Broken letters. "é" where you expect "é" is an encoding problem called mojibake. The file was saved as UTF-8, where é takes two bytes, and then read as Windows-1252, where those two bytes become two unrelated letters. A "’" apostrophe comes out as "’" the same way. The fix is to read the file as UTF-8, or to export it again from the source with UTF-8 selected. Repairing text that was already saved wrongly is fragile, so keep the original and work on a copy.

Layout: title rows, blank lines and duplicates

Reports often start with a title, a date and a blank line above the real header. Delete everything above the header row, so the first line holds the column names. Each column needs one name, and no name should be empty.

Every row should have the same number of fields. A stray comma inside a value that is not in quotes adds a field, and every value after it moves one column to the right. Count the fields per row. A row with a different count is broken.

Duplicates come in two kinds. Rows identical in every column usually come from exporting or pasting twice, and they are safe to remove. Rows that share a key but differ elsewhere may be updates. Deleting the wrong one can remove the newest version, so check them first.

Dates: 03/04/2026 can be two different days

03/04/2026 is 3 April in one country and 4 March in another, and the text does not say which. Look at the other dates in the same column. If any first number is above 12, the column is day-first. If any second number is above 12, it is month-first. If every value has both numbers at 12 or below, the file cannot tell you, so ask the source system. The data type checker flags such a column as ambiguous instead of guessing.

Once you know the order, convert every date to ISO 8601, the year-month-day form such as 2026-04-03. It is unambiguous, and it sorts correctly as text. Convert mixed formats one by one, because a column with both styles will be read inconsistently by most programs.

Numbers stored as text

A number is stored as text when it carries a currency sign, a percent sign or a space inside it, such as "€ 12", "45%" or "1 234" with a non-breaking space. Thousands separators, as in "1,234.50", often make a program read the value as text too. A spreadsheet shows text numbers aligned to the left, and sums skip them.

Decimal commas are the trap. In "1.234,50" the comma is the decimal point, and the full stop groups thousands. In "1,234.50" it is the other way round. So "1,234" may be 1234 in one export and 1.234 in another. Find the locale of the source before you convert, then remove the thousands separators and turn the decimal comma into a point.

Leading zeros are lost

Postcodes, product codes and account numbers often start with zeros. When a CSV opens in a spreadsheet, "01234" is read as the number 1234, and the zeros are gone. Saving the file keeps the loss, so the source system is the only reliable copy.

An identifier is text, even when it looks like a number, because you never do arithmetic with it. Leave number conversion off for such columns when you clean the CSV, and open the file from the source again rather than from a copy a spreadsheet has saved. Check each column's data type afterwards, so a postcode column does not quietly switch back to numbers. Excel can also change values when it opens a CSV, so check the result in a plain text view.

Check the types, then profile

After cleaning, check the type of each column. A single "N/A" in a column of prices turns the whole column into text, and its total then comes out wrong. The data type checker gives each column a type and a confidence score, and lists the cells that do not fit.

Then profile the columns. The column profiler shows gaps and outliers in each column. An outlier can be a real value, or an error such as a price one thousand times larger than the rest, which often comes from a misplaced decimal point or separator.

Edge cases

• Line breaks inside quotes. A quoted value may contain a line break. A tool that splits the file by lines breaks that row in two, so use a reader that understands quoted fields.
• Semicolon files. Many European spreadsheets save CSV with semicolons, because the comma is the decimal separator in their locale. Check the separator before you read the columns.
• Keep the original. Write the cleaned result to a new file, and keep the export as it came.

The order, as a checklist

1. Keep a copy of the original export.
2. Remove stray spaces and invisible characters. Fix broken letters at the source if you can.
3. Delete the title rows, check the header names, and confirm every row has the same number of fields.
4. Find the duplicates and decide which version to keep.
5. Convert dates to year-month-day, remove separators, and make identifiers text.
6. Check the type of each column, then profile it for gaps and outliers.

Quick answers

Why does my lookup fail when the values look identical?

Usually a space, a non-breaking space or a byte order mark sits in one of the cells. Remove the hidden characters and try again.

Why do my dates change to something else?

A program read a day-first or month-first date in the wrong order, or it converted the text into its own date format. Convert the dates yourself first, to year-month-day.