CSV cleaner — trim spaces, drop empty rows and fix ragged columns

How to use the CSV cleaner

1. Press Choose a file (or drop a file on the box) to load a .csv, .tsv, .txt, .xlsx, .xls or .ods file, or paste text into CSV data.
2. Leave Delimiter on Detect automatically unless the guess is wrong, and keep First row is a header ticked if the file has column names.
3. Choose the clean-up steps: Trim spaces around values, Collapse repeated spaces inside values, Remove empty rows, Remove empty columns, Give every row the same number of columns and Normalise header names (lower case, underscores).
4. Pick the Output delimiter, the Line endings and, if needed, Quote every field.
5. The result updates as you change options. Copy the Cleaned CSV or press Download to save <name>-clean.csv (data-clean.csv for pasted text).

What it does and when to use it

Exports from forms, shops and old spreadsheets often arrive untidy: names with a leading space, blank lines between blocks, an empty last column from a trailing comma, or rows with one field too few. These faults break imports and duplicate checks, because “ Ben” and “Ben” are different values to a computer.

Run the cleaner first, then hand the file to the next step: the CSV duplicate remover to remove repeated records, the CSV column extractor to keep only the fields you need, or the CSV viewer to check the result as a table.

How it works

The file is parsed with the PapaParse library. A BOM is removed, quoted fields may contain commas and new lines, and the delimiter is guessed among comma, semicolon, tab and pipe. Non-UTF-8 files are read as Windows-1252 with a notice, and Excel files use their first sheet as displayed.

The steps then run in a fixed order: trim and collapse spaces, drop empty rows, pad short rows, drop all-blank columns, and lower-case the header with spaces turned into underscores. On output a field is quoted only when it contains the delimiter, a double quote or a line break, and inner quotes are doubled, as RFC 4180 describes. Quote every field wraps all of them.

Worked examples

The sample file. Six lines with padded names, two blank lines and a trailing comma that creates an empty fourth column. The stats read: rows 6 → 4, columns 4 → 3, cells changed by trimming 6, empty rows removed 2. The output starts:

Name,Email,Notes
Asha Verma,asha@example.com,
Ben   Carter,ben@example.com,VIP

“Ben Carter” keeps its inner spaces until you tick Collapse repeated spaces inside values.

Semicolons to commas for Windows. The input a;b then "x ";" y" with Output delimiter Comma, CRLF and Quote every field becomes "a","b" and "x","y" separated by CRLF.

Tidy headers and a ragged row. First Name , Last Name,Signup Date with Normalise header names becomes first_name,last_name,signup_date,. The trailing comma comes from a data row with an extra fourth value, which is kept; the other rows are padded to match.

Limits and tips

• The preview table shows the first 1,000 rows; the download always contains every row.
• To split a very large cleaned file into smaller parts, use the CSV splitter.