How to clean a messy CSV file

Open the CSV Cleaner and load the file with the delimiter on auto-detect. Check the row and column counts first: if the column count is 1, the delimiter is wrong, and setting it to semicolon or tab usually fixes the whole file.

Then work through the parser warnings, which name specific row numbers, before you touch the data. Clean, then export as CSV or JSON. Everything runs in a worker in your browser; the rows are never uploaded.

Last reviewed: 2026-09-19. Checked against the tool’s own source on that date.

Why exports arrive broken

CSV is a family of conventions rather than a format. Two applications can both claim CSV support and disagree about the delimiter, the quoting style, the line ending and the character encoding.

The delimiter is the most common surprise. Software sold in countries that use a comma as the decimal separator often exports semicolon-separated files, because the comma is already occupied. Those files still end in .csv, and a tool that assumes commas collapses every row into a single column.

Encoding is the second. A UTF-8 file frequently begins with an invisible three-byte byte-order mark. Excel on Windows uses it to recognise UTF-8; many other tools do not expect it, so it gets glued to the first header name and a column called id silently becomes one that no lookup will ever match.

Diagnose in three numbers

Auto-detection parses a sample with each candidate delimiter and picks the one producing the most consistently rectangular result, which is why it is not fooled by commas inside quoted fields of a semicolon-separated file. It is right most of the time; the preview tells you when it is not.

  • Column count. If it is 1, the delimiter is wrong. Try semicolon, then tab.
  • Row count. If it is far higher than expected, the file probably contains quoted newlines that something upstream has already mangled.
  • Warning count. Each warning names a row number, counted the way a text editor counts, so you can go and look.

Reading the warnings

A ragged row means a row had a different number of cells than the header. Nothing is discarded to tidy the grid: short rows are padded with blanks and long rows keep their extra values. Silently deleting somebody’s data to make a table look rectangular is the worst thing a cleaning tool can do, so it is not done.

An unclosed quote means a double-quote was opened and never closed, so everything after it was read as one enormous field. This is nearly always genuine corruption in the source, and it is worth fixing where the file was produced rather than patching downstream.

The tool reports these rather than repairing them, because both cases have several possible correct fixes and only you know which applies.

Clean in the right order

The formula-injection-safe export is on by default and is the one option here with a genuine security consequence. A cell beginning with =, +, - or @ is treated as a formula by spreadsheet applications, so a value arriving from an untrusted source can execute when somebody opens your file. The safe export prefixes those cells with an apostrophe, which stops them calculating — including any you intended to calculate, which is inherent to the mitigation rather than a bug.

  1. Trim whitespace first. Two rows differing only by a trailing space are not duplicates until you trim them.
  2. Remove duplicate rows second. The first occurrence is kept, so surviving order is unchanged.
  3. Rename headers and choose the columns to export.
  4. Sort if you need to. Numeric columns are detected and sorted numerically, so 9 comes before 10.
  5. Check the export options before downloading, especially the formula-injection setting.

Choosing the export encoding

Turn the UTF-8 byte-order mark ON if the file is destined for Excel on Windows, which uses it to detect the encoding; without it, accented characters and non-Latin scripts can appear as mojibake on a double-click.

Leave it OFF if the file feeds a script, a database import or another program, because some strict parsers treat the mark as data.

Worked example: a 12,000-row export that opens as one column

A customer export from a European CRM opens in your editor as 12,000 rows and a single column. The header row reads Name;Email;Country;Signup date.

  1. Load the file. Auto-detect reports 4 columns; if it does not, set the delimiter to semicolon manually.
  2. Read the warnings: say 3 ragged rows at 418, 2,905 and 7,110.
  3. Look at those three rows. They contain an unescaped semicolon inside a company name — the extra cells are kept, so nothing was lost.
  4. Trim whitespace, then remove duplicates.
  5. Export as CSV with comma as the delimiter and the byte-order mark on, because the file is going to a colleague who uses Excel.

Result: A comma-separated UTF-8 file with a byte-order mark, four columns, and the duplicate rows gone. The three problem rows are still present with their extra cells intact, flagged for you to fix at source rather than deleted to make the table tidy.

Open the tool

Clean a CSV in your browser

Parses in a Web Worker on your device, keeps every cell it finds, and reports problems it will not guess at.

What this does not cover

  • The whole file is held in your device memory. The cap is 50 MB, and a phone will struggle well before a laptop does.
  • Only the first 100 rows are shown in the preview, though every row is cleaned and exported.
  • Encoding detection is limited to the UTF-8 byte-order mark. A file saved as Windows-1252 or Shift-JIS is read as UTF-8 and may show replacement characters; re-export it as UTF-8 from the source application.
  • Only UTF-8 is written. Legacy encodings are not produced.
  • There is no multi-sheet support, because CSV has no concept of sheets.
  • A file whose quoting is already corrupt is reported, not repaired.
  • Undo is limited to the last 20 operations.