Clean up a CSV

How to use it

Paste the CSV in the box or drop the file in, and the tool re reads it every time you change an option: the preview shows the first 20 rows of the result, so what you see is already what you download. Cells that were modified stay highlighted in yellow, so you can check them before trusting them. At the end you pick separator, encoding and line ending and download the file, or copy it to the clipboard. The file never leaves your device, there is no server behind this page.

Why a CSV opens all in one column

CSV has no single standard. Excel in most European settings expects a semicolon, almost every English speaking program expects a comma, and back office software exporting from old databases often uses a tab or a pipe. When the separator is not the expected one, the spreadsheet opens with everything crammed into the first column. To recognise it the tool tries the four most common separators, looks at the first 20 non empty rows with a parser that honours quotes (the RFC 4180 rule) and keeps the one giving the highest and most consistent column count. It is also why a comma inside "Rossi, Mario" does not fool it: between quotes the separator is plain text, and it stays that way in the file you download.

Numbers: 1.234,56 against 1,234.56

A value with a single separator followed by exactly three digits, like 1.234 or 1,234, can be read in two opposite ways and no program can tell from the value alone. The tool decides column by column: it looks for a value in the same column that settles the question (one with two decimals, one carrying both separators, one with more than one separator) and applies that reading to the whole column. When the column offers no evidence it reads the separator as a thousands mark and says so in a warning, and the How numbers are written menu lets you force the right convention. Cells made of digits only, with no dot and no comma, are never touched, so postcodes, customer codes and phone numbers with a leading zero stay intact. Do watch out for a phone number written exactly like a grouped number, say 800.123.456: it is indistinguishable from a million, so check the highlighted cells in the preview before downloading.

Duplicates, ragged rows and empty columns

Duplicates are compared on cells that have already been trimmed and always keep the first row met, so the original order does not change. If you pick a key column (the email, the customer code, the VAT number) rows with that cell empty are never treated as duplicates of each other, otherwise all but one would disappear. A column is removed only when it is both empty and unnamed, that is the familiar trail of separators Excel adds at the end of the rows; columns with a name but no data are kept, because the program receiving the file often insists on them, and they are reported in a warning. Rows with too many or too few cells are padded or trimmed to the header, and if a filled cell ends up cut the tool tells you instead of dropping it in silence.

Encoding, accents and the stated limits

If the source file is not UTF-8 it is read again as Windows-1252, the encoding of older exports, and you get a note about it. On the way out the UTF-8 mark at the start of the file (BOM) is what tells Excel how to read accented letters: when it is missing, e with an accent turns into two odd symbols. Windows-1252 is only there for legacy software that understands nothing else, and since that table holds just 256 characters, emoji and non Latin alphabets become a question mark, counted and declared at download time. The limits are 10 MB per file, 100,000 rows and 1,000 columns. The tool does not open .xlsx files (Excel to CSV does that), does not guess where an unclosed quote should end, and does not modify cells starting with = or +, the ones Excel reads as formulas: it only counts them, because fixing them would mean changing your data.