Clean a messy CSV

Your files and your privacy

Your spreadsheet is never uploaded. The file is read by the browser, parsed by a Web Worker on your own device and written back out by the browser’s download mechanism. The page’s Content-Security-Policy limits connect-src to this origin, and Clear data discards the table at once.

What this tool does

Repair a spreadsheet export that will not open cleanly: the delimiter is detected rather than assumed, a UTF-8 byte-order mark is removed, rows whose cell count disagrees with the header are reported by number, and the table is written back out as clean RFC 4180 CSV.

Cleaning works on the structure of the file, not on what the data means. The tool detects which character separates the fields, strips a leading UTF-8 byte-order mark, reads quoted fields containing the delimiter or a line break, and names the row number of every row whose cell count disagrees with the header. You can then trim whitespace, collapse runs of spaces, drop blank rows and remove duplicates, before writing the table back out with RFC 4180 quoting. Renaming and reordering columns is a separate job and lives on the toolkit index, linked below.

How to use it

1. Select Choose a CSV or JSON file and pick the export you want to fix, up to 50 MB. Pasting the text in works too.
2. Leave the delimiter on Auto-detect; the detected value is written back into the control. If the preview shows one column, set it by hand.
3. Read the row and column summary and the warnings, each naming a row number as a text editor counts it.
4. Apply Trim whitespace, Collapse spaces, Remove empty rows and Remove duplicate rows in whatever order suits the file, and Undo anything you did not want.
5. Choose the export options, then select Download CSV and Clear data when you are finished.

Example use cases

• An export from European software that is semicolon-separated and collapses into one column elsewhere.
• A download whose first header name picked up an invisible byte-order mark, so a lookup on it never matches.
• A file a hand edit broke, where one row gained a comma and every column after it is shifted.

Supported input and output

Accepts

• CSV, TSV or plain text separated by comma, semicolon, tab or pipe, up to 50 MB
• JSON containing an array of objects, which is read back into a table

Produces

• CSV, UTF-8, CRLF line endings, minimally quoted, named with -cleaned appended

What this tool will not do

• Detection considers only comma, semicolon, tab and pipe and scores a ten-row sample; if no candidate yields at least two columns it falls back to comma.
• The first row is always the header, so a file that begins straight into data loses its first record to the header line.
• Ragged rows are reported, never repaired: short rows are padded with blanks, and long rows keep their extra cells past the last named column.
• Only the UTF-8 byte-order mark is understood. A file saved as Windows-1252 or Shift-JIS is read as UTF-8 and shows replacement characters.
• An unclosed double quote is reported against the row that opened it, and everything after it is read as one field.

Common mistakes

• Removing duplicate rows before trimming: two rows differing only by a trailing space are not duplicates, so both survive.
• Writing the UTF-8 BOM into a file that feeds a script or a database import. The mark joins the first header name, and lookups on that column match nothing.
• Turning off the formula-injection-safe option on data that came from a form. The download succeeds, and the payload runs when the file is opened in a spreadsheet.
• Ignoring a ragged-row warning because the preview looks right: only the first 100 rows are shown, and a shifted column usually starts below them.

Technical notes

Parsing is a hand-written RFC 4180 state machine, so quoted delimiters, newlines inside quoted fields, doubled quotes as an escape and CRLF, LF or lone-CR endings are handled by construction. It runs in a Web Worker, so a large export does not freeze the page. Delimiter detection parses a ten-row sample with each candidate and scores it by column count weighted by how rectangular the result is, so a comma inside a quoted field cannot outvote a real semicolon.

Frequently asked questions

Is my spreadsheet uploaded anywhere?

No. There is no upload endpoint: the file is read, parsed in a Web Worker and written back out on your device, and the page’s Content-Security-Policy limits connect-src to this origin.

Why did my file open as a single column?

The delimiter is wrong. Detection falls back to comma when no candidate splits the sample into two or more columns. Set it by hand: semicolon for European exports, tab for a file that was really TSV.

What does the formula-injection-safe option change?

It prefixes an apostrophe to every cell starting with =, +, -, @, a tab or a carriage return, so a spreadsheet treats it as text rather than a formula. It changes your data: a cell you meant as a working formula becomes literal text. Values that parse as ordinary numbers are exempt, so -42 is untouched.