CSV cleaner and column transformer

How the parser reads a CSV file

CSV looks like a format you could split on commas, and for perhaps eight files in ten you could. The other two are the reason this page has a real parser. A field wrapped in double quotes may contain the delimiter, and a quoted field may contain line breaks, so a single record can span four lines of the file. Inside a quoted field a literal double quote is written twice. Splitting on commas turns any of those into silent corruption: a row gains columns, a record splits in half, and the damage only surfaces later, in whatever consumed the file.

The parser here is a single pass over the characters with one piece of state, whether it is currently inside a quoted field. That is enough for the whole of RFC 4180 plus the leniencies real files need. A byte order mark at the start of the file is dropped rather than being glued onto the first header name. Rows end at either a line feed or a carriage return followed by a line feed, so files written on Windows and files written on a Mac both work without a conversion step. A trailing newline at the end of the file does not invent an empty final row. A quote that appears in the middle of an unquoted field — the 5" pipe problem — is kept as ordinary text rather than treated as a syntax error, because that is what the file plainly means.

Delimiter detection works by parsing rather than by counting. Each of the four candidates is used to parse the first 64 kilobytes, and each result is scored on how many columns it produces and how consistent that column count stays across the first twenty rows. A semicolon-separated European export read as comma-separated collapses to a single wide column and scores zero, so it loses to the semicolon. Counting raw characters would get that wrong whenever the values themselves contain commas, which in a semicolon file they very often do. If detection still picks the wrong one, the select box overrides it.

What each cleaning option changes

The options apply in a fixed order, and the order matters. Byte order marks go first. Then internal whitespace collapses — only runs sitting between two non-space characters, so a leading run of spaces is left alone for the trim step to deal with rather than being quietly turned into a single space. Then each cell is trimmed. Only after the cells are clean does anything look at rows: padding and truncating to the header width, then dropping blank rows, then dropping duplicates. That ordering is what makes deduplication useful, because two rows that differ only by a trailing space become identical once the cells have been trimmed, and only then can they be recognised as the same row.

Header normalisation applies to the first row only. It converts a name such as Total (GBP) into total_gbp: camel case is split at the boundary, apostrophes and quotes are deleted rather than replaced so that Buyer's Name becomes buyers_name and not buyer_s_name, and every remaining run of non-alphanumeric characters becomes a single underscore. Accented and non-Latin letters are treated as separators rather than transliterated, so Prénom becomes pr_nom. That is deliberate: guessing at a transliteration is how a column silently ends up with a name nobody expected. If you want something else, rename the column by hand.

Column selection runs last, after the cleaning, and it is keyed to the original column positions. That means a rename you typed against the third column stays on the third column even if you then drop the first one. Renames are stored separately from the header text, which is why toggling the snake_case option does not wipe out the names you typed. The preview shows the header plus the first fifty rows; the copy button and the download button both give you every row, not just the ones on screen.

Questions people ask

Is my file uploaded anywhere?

No. The file you drop or choose is handed to the browser's FileReader, which reads it from disk into a JavaScript string inside this tab. Nothing on this page makes a network request of any kind, and the result is not stored in a cookie, in local storage or anywhere else. You do not have to take that on trust: open developer tools, go to the Network panel, clear it, then load a file and clean it. Nothing appears. That said, if the data is genuinely sensitive, the strongest position is still that it never touches a browser tab pointed at the public internet, however the tab behaves.

Why is my file being split on the wrong character?

Detection scores each candidate by how many columns it produces and how consistently it produces that number, and a file can be genuinely ambiguous. A two-column semicolon file whose values are full of commas is the usual culprit, and so is a file where the first twenty rows are unrepresentative of the rest. Set the delimiter by hand in the select box and the detection is skipped entirely. If your separator is something else again — a caret, a record separator character, a multi-character sequence — this page will not read it, and a command-line tool with an explicit delimiter flag is the better answer.

What happens to rows that have the wrong number of columns?

Nothing, unless you tick the option to pad or truncate them. Ragged rows are preserved exactly as they were parsed, because a short row is often a real signal that something upstream went wrong and quietly padding it would hide that. When the option is on, the header row's width becomes the target: shorter rows gain empty cells on the right, longer rows lose their extra cells. The counters above the preview show the row count before and after, so you can see how much the cleaning actually removed.

Will cleaning damage fields that contain commas or quotes?

It should not, and the checks that ship with this page exist specifically to prove it. Every field is parsed into a plain string with the quoting removed, cleaned as a string, then written back out with whatever quoting it now needs. A field containing the delimiter, a double quote, or a newline is re-quoted on the way out, and internal quotes are doubled again. Parsing the output gives back exactly the rows that went in, for every delimiter and for fields containing all three awkward characters at once. The one thing that does change is line endings: rows are written back with CRLF, as RFC 4180 specifies.

How large a file can this handle?

Files up to eight megabytes will load, and up to two million characters will clean. The parser itself is a single pass and copes with far more than that, but the preview table and the per-column controls are ordinary DOM elements, and rebuilding them on every keystroke is what gets slow. If your file is bigger, the honest answer is that a browser tab is the wrong tool: split it, or reach for csvkit, Miller, DuckDB or a few lines of Python, all of which stream rather than holding the whole file in memory.