Clean CSV data

How to clean a CSV file

1. Open a .csv, .tsv or .txt file, drop it on the tool, or paste rows. The delimiter and a UTF-8 or UTF-16 byte-order mark are detected; choose another delimiter or a legacy encoding under Import settings.
2. Under Values, choose the cell fixes. Use Apply to columns to limit them to some columns; none selected means every column.
3. Under Rows and columns, choose how to handle empty rows, duplicates and empty columns.
4. Check the note above the result, compare with the Input tab, then select Download CSV, Copy or Open in editor.

When the data is clean, check it against column rules with Validate CSV, or change the delimiter and quoting with Format CSV.

What each step does, in the order it runs

Each value goes through the enabled steps in this fixed order, so the result is the same however you click the options.

After the values, rows and columns are handled: Remove empty rows drops rows whose cells are all empty or only spaces; Duplicates compares the cleaned values; Remove columns that are completely empty runs last. Row and column removal always looks at every column, whatever Apply to columns says.

Removing duplicate rows

• Remove rows identical in every column is the default. Values must match exactly after the cell steps above.
• Remove rows with the same values in chosen columns treats rows as duplicates when the selected key columns match, for example email, or first_name + last_name + zip. The other columns may differ.
• Keep decides which copy survives: First occurrence, or Last occurrence, which stays at the position of that last row.
• Ignore case and surrounding spaces when comparing treats ANA@example.com and ana@example.com as the same key. The kept row’s text is not changed by this option.
• Choose Keep all rows to turn deduplication off.

Before deduplicating by key, sort with Sort CSV if you want a particular row, such as the newest, to come first.

What is never changed

• The header row is left as it is, and column order never changes except when empty columns are removed. If every column is empty, none is removed.
• Values are not converted: 00731, 1e5 and 03/04/2024 stay text exactly as written, apart from the steps you enabled.
• Quoted delimiters and line breaks inside values are read per RFC 4180 and written back quoted, unless you replace the line breaks.
• A blank header is written as Column 3, and a repeated header as name (2). Rows shorter than the header are padded with empty values.
• The download keeps the file’s delimiter, line endings and final newline, and its encoding when that is UTF-8 (with or without a byte-order mark), UTF-16 LE or Windows-1252; other encodings are saved as UTF-8.

Troubleshooting

• Rows disappeared that I wanted to keep. Duplicate removal is on by default. Choose Keep all rows under Duplicates, or turn off Remove empty rows.
• Duplicates that look the same were kept. They differ by case, spaces inside the value, accents typed two ways or invisible characters. Turn on Ignore case and surrounding spaces when comparing, Collapse repeated spaces inside values, Normalize Unicode (NFC) or Remove invisible characters.
• Title case broke names or codes. Limit it with Apply to columns, for example to a name column only, so emails and product codes keep their case.
• A lowercase n/a wasn’t cleared. The list is case-sensitive; add n/a next to N/A.

Limits and privacy

Files up to 25 MB, 100,000 rows, 250 columns and 2 million cells. For a larger file, split it first with Split CSV; note that duplicates are then only found within each part.

Cleaning runs in your browser. The CSV is not uploaded to a server.

Example

A contact list cleaned with the default trim and empty-row removal, plus Collapse repeated spaces inside values, Treat these values as empty set to N/A, NULL, and duplicates by the email column with Ignore case and surrounding spaces when comparing. The note reports 5 changed values, one empty row and one duplicate row removed. The second Ana row is removed as a duplicate; the first keeps its own spelling.

name,email,city
"  ana lopez ",ANA@example.com,Madrid
Ana Lopez,ana@example.com ,Madrid
Kim   Lee,kim@example.com,N/A
,,
Bao Nguyen,bao@example.com,NULL

name,email,city
ana lopez,ANA@example.com,Madrid
Kim Lee,kim@example.com,
Bao Nguyen,bao@example.com,