Skip to content
OmniTools

CSV Cleaner

Parses RFC 4180 by hand, detects the delimiter by column-count consistency not frequency, reports ragged rows by index, keeps destructive fixes off.

All text tools

CSV cleaner

Paste an export and get back a file that parses the way you meant it to. Every change is listed with the number of rows and cells it touched, and the untidy fixes happen by default. Nothing is uploaded: the parsing runs in this page, which matters because a pasted export is usually a customer list.

Tidy up

On by default. None of these can turn a real value into a different real value.

Destructive: off unless you turn them on

These two discard something that was in your file. They are off by default because the value they remove is usually meaningful information, and because the change is not visible in the output once it has been made.

What was changed

Stripped UTF-8 BOM0 rows, 1 cell

The first column name had an invisible prefix on it.

Converted smart quotes and dashes0 rows, 2 cells

Curly quotes and en or em dashes pasted from a word processor replaced their ASCII equivalents.

Replaced unusual spaces0 rows, 1 cell

Non-breaking and thin spaces became ordinary spaces instead of being deleted.

Removed empty rows1 row, 0 cells

Rows where every field was empty, usually trailing padding from a spreadsheet.

Fixed ragged rows1 row, 3 cells

0 rows cut back to 6 columns, 1 padded with empty cells. Rows 3 of the pasted data.

Removed ghost column5 rows, 5 cells

Every row ended with a delimiter and the last header was empty, so the extra column held nothing.

What could not be fixed

  • Row widths were not perfectly consistent under Comma, so the delimiter guess is 80% confident. Set the input delimiter yourself if the columns look wrong.

Cleaned CSV

Read with comma · 4 rows, 5 columns
id,name,email,plan,notes
1,John Smith,john@example.com,Pro,"Needs urgent"" review"""
2,Anita Rao,anita@example.com,Free,ok
3,Ben,ben@example.com,,
4,Priya Shah,priya@example.com,Team,"line one
line two"

This runs entirely in your browser and nothing is uploaded, which is the point: a pasted export is usually a customer list, and it should not travel to anyone to be parsed. Check the change log above before you overwrite the original, because a repair that guessed wrong is still a repair.

What usually goes wrong

Trailing comma on every row

An extra comma at the end of each line, usually from a spreadsheet export, creates a column with an empty header that silently swallows the real last column when the file is re-read.

Here: Removed as a ghost column when every row has the same field count and that field is empty.

Ragged rows

A row with more or fewer fields than the header shifts every value after the gap by one column, so a name ends up in a postcode field and a spreadsheet shows it without complaint.

Here: Short rows are padded with empty cells, long rows are cut back to the header width, and both are listed in the log with row numbers.

UTF-8 byte-order mark

Saved by Windows tools at the start of the file. Every CSV reader treats it as part of the first column name, so the header is 'name' and lookups by 'name' fail.

Here: Stripped from the start of the file.

Non-breaking spaces

Pasted from a web page or a PDF. They look identical to a space, so trimming does nothing, but they stop 'Total ' and 'Total' from matching.

Here: Converted to ordinary spaces rather than deleted, so word boundaries survive.

Zero-width spaces and joiners

Invisible characters inside a value. Two records that look identical on screen differ as far as a database or a de-duplication routine is concerned.

Here: Removed. Note that this also removes zero-width joiners, which carry emoji sequences, so emoji-only cells lose their shape.

Smart quotes from Word

Pasted through a word processor, curly quotes and dashes replace the ASCII ones. The field is no longer the value it looks like, and quotes no longer parse as quoting.

Here: Curly quotes and dashes converted to their ASCII equivalents before parsing.

Blank rows at the end

Left by a bad export or by a spreadsheet that pads the used range. They shift the row count and produce a stray empty record on import.

Here: Removed, along with any row whose fields are all empty.

CRLF mixed with LF

A file edited on two platforms. Parsers that split on one terminator leave a stray carriage return at the end of half the values.

Here: Normalised to a single line ending on output, chosen by the export option.

Unbalanced quotes

An odd number of quotes means a quoted field was never closed, so the parser swallows the rest of the file as one field. This cannot be repaired without guessing where the quote was meant to be.

Here: Not fixable automatically. The affected parse is reported as a warning and the quoted region is treated as closing at the end of the line.

This tool runs entirely in your browser. Nothing you enter is uploaded, stored, or logged.

What this tool does

A bulk import fails on row 1,407 and tells you nothing useful, because the cause is usually a trailing comma, a non-breaking space pasted from a web page, or one short row that shifts every value after it by a column. This tool parses the file properly, lists the defects by row and column, applies the safe fixes, and writes a change log so you know exactly what it did. The two fixes that destroy data are off until you ask for them by name.

How it works

The parser is RFC 4180, written out by hand rather than approximated. A quoted field can contain the delimiter, a newline, and a doubled quote that means one literal quote, and a quote in the middle of an unquoted field is a stray character rather than the start of a quoted field, so it is kept instead of destroying the value. The invisible layer is handled before parsing, because none of it parses correctly: a byte-order mark is stripped from the front of the file, curly quotes and en or em dashes from a word processor are converted to their ASCII equivalents, non-breaking and thin spaces are converted to ordinary spaces rather than deleted so word boundaries survive, and zero-width characters are removed.

Delimiter detection scores column-count consistency, not frequency, and that single decision is what makes it work. Frequency detection is the standard approach and it is wrong in both directions: a comma inside a single quoted address field beats every semicolon in the file, and a file exported from a European locale legitimately uses semicolons throughout. So each candidate is fully parsed, quoting-aware, and scored on how many rows end up with the most common number of fields. A semicolon file whose addresses contain commas scores a clean three columns under semicolon and a ragged mess under comma, and the consistent parse wins. The confidence figure is the share of rows matching the winning width, so a file that parses inconsistently scores low and says so, with an option to set the delimiter yourself.

The structural defects are reported with their row numbers, because that is what makes them findable in the original file. Ragged rows are detected by comparing each data row's field count against the header's: short rows are padded with empty cells and long rows are cut back to header width, and both are listed with the row number of the pasted data. A trailing comma on every row is removed as a ghost column, but only when every row agrees on the width and that last field is empty, so a genuinely empty final column in a file that simply had no trailing comma is left alone. Duplicate rows are judged on the whole joined row, so two records that share a first column are not wrongly treated as duplicates. Header names are trimmed, lowercased, runs of punctuation collapsed to an underscore, and genuine repeats given a _2 suffix, because two columns silently both called id is a disaster three weeks later in a spreadsheet.

Then there are the two options that are off, and they are off for a reason worth stating plainly. Stripping leading zeros turns 00542 into 542, and an Indian PIN code written with a leading zero, a GTIN barcode, a SKU that starts with zeros, and any postcode with a leading zero are all identifiers rather than numbers — changing them produces a different identifier and there is no way back once the file has been saved over. Dropping empty columns is off because an empty column is often a real second address line or a field someone deliberately reserved for later, and removing it changes your schema. Everything else on is on, because it is merely untidy rather than lossy.

Worked example

A 2,000-row product export that fails a bulk import, with a BOM, a smart-quoted note, a non-breaking space, a blank line, a short row and a trailing comma.

  1. Paste the file: it opens with a byte-order mark, so the first column name is actually \uFEFFid rather than id
  2. Row 2 has curly quotes inside an already-quoted note and row 3 has a non-breaking space in "Anita Rao"
  3. There is a blank line, and row 3 of the data is short because the trailing comma is missing from it
  4. Every row ends with a comma and the last header is empty, so the file has six columns where it should have five
  5. Clean: 6 changes logged — BOM stripped, 2 smart quotes converted, 1 unusual space converted, 1 empty row removed, 1 ragged row padded, ghost column removed
  6. The delimiter guess is reported at 80% confidence because row widths were not perfectly consistent, and a PIN code in the data is left exactly as it was

A five-column file that imports, a change log naming every edit, and a warning that the delimiter was only 80% certain — with the PIN code intact because stripping leading zeros is not on by default.

Accuracy and limitations

  • Stripping leading zeros is off by default because it destroys data. Indian PIN codes with a leading zero, GTIN barcodes, SKUs and postcodes are identifiers, not numbers, and turning 00542 into 542 gives you a different code that cannot be recovered once the file is overwritten.
  • Unbalanced quotes cannot be repaired automatically. An odd number of quotes means a quoted field was never closed, so the parser has to guess where it should have ended; the affected parse is reported as a warning and the quoted region closes at the end of the line, so the columns after it may be wrong.
  • Removing zero-width characters also removes the joiners that hold emoji sequences together, so cells containing only emoji lose their shape. For a product feed this is irrelevant; for a file where emoji are data, turn that option off.

Frequently asked questions

Why did my CSV import fail on one row out of two thousand?
Almost always a ragged row — one data row with more or fewer fields than the header. A missing value at the end is the most common cause, and it shifts every value after the gap by a column, so a name lands in a postcode field and the spreadsheet shows it without complaint. This tool reports the row number, what it expected and what it found, then pads or cuts it to match.
How does it decide whether the delimiter is a comma or a semicolon?
By consistency, not frequency. Each candidate is fully parsed with quoting rules applied, and scored on how many rows come out with the same number of fields. That is why a European file full of semicolons, whose addresses contain commas inside quotes, is detected correctly where a frequency-based detector would pick the comma and produce a ragged mess. The confidence percentage tells you how cleanly it parsed.
What is the trailing comma problem?
An extra comma at the end of every line, usually from a spreadsheet export, which creates a column with an empty header name. When the file is re-read, that ghost column swallows the real last column, and depending on the tool either the last field is lost or a column of blanks appears. The cleaner removes it, but only when every row has the same field count and that last field is empty, so a legitimately empty final column is left alone.
Why is stripping leading zeros turned off?
Because 00542 and 542 are different identifiers, not the same number written twice. Indian PIN codes, GTIN barcodes, SKUs and any postcode with a leading zero all depend on those zeros, and rewriting them is unrecoverable once the file has been saved over. The option exists for files where a column genuinely is numeric — receipt amounts, quantities — and you have to turn it on deliberately for that file.
What is a BOM and why is it breaking my first column?
A byte-order mark is a character Windows tools write at the start of a file to declare its encoding. It is invisible, but every CSV reader treats it as part of the first column name, so the header is actually \uFEFFname rather than name and any lookup by "name" silently fails. It is stripped here by default, since it is a Windows artefact rather than data.
Does it upload my file anywhere?
No, parsing and cleaning both run in your browser tab. For a product export that may contain supplier costs, stock levels or customer records, that matters — and it is also why a very large file takes as long as it takes, because there is no server doing the work for you.