Data Cleaner

Tidy a messy CSV in one pass — whitespace, smart quotes, placeholder nulls and untidy headers.

Runs on your deviceDataWorkingNo account needed

What this tool does

Data Cleaner reads your pasted text as a delimited table, rewrites every cell through the checks you leave switched on, and writes the table back out. Because it is a real parse and re-emit rather than a search and replace, quoting is repaired along the way and ragged rows are padded with empty cells to the width of the widest row.

The cleaning is a stack of small, named rules. Trimming removes leading and trailing whitespace; collapsing turns runs of spaces and tabs inside a value into one space; tidy text drops zero-width and control characters, turns four invisible-width spaces — the non-breaking, figure, narrow no-break and thin space — into ordinary ones, and replaces curly quotes with straight ones, en and em dashes with a hyphen, and an ellipsis character with three full stops. Separately, a fixed list of placeholder words becomes an empty cell, whole-cell currency amounts can be reduced to bare numbers, and the data cells can be pushed to lower, UPPER, Title or Sentence case. Header names have their own setting: leave them, fill blanks and make them unique, or rewrite them as snake_case, lower case or Title Case — the last three fill and de-duplicate as well.

The rules are blunt on purpose, and it is worth knowing where that bites. The placeholder list is fixed — NULL, N/A, NA, #N/A, nil, none, -, --, undefined and (blank) — matched case-insensitively against the value the earlier rules have already left behind, so a legitimate "None" category or a hyphen that means something is emptied along with the junk. Title Case capitalises the first letter of every run of letters and lowercases the rest, which turns IBM into Ibm. Number normalisation only touches a cell that is entirely an amount, optionally prefixed with $, €, £ or ¥ and using a full stop for the decimal, so a European "1.234,50" is left as it is. Tidy text is a list rather than a category too: an en, em, hair or ideographic space is not one of the four it converts. There is no date parsing, no column type detection, no row deduplication, and no repair of text that was already decoded with the wrong character set.

Everything runs in this browser tab, so the table is not uploaded anywhere and the tool still works with the network off. The trade is that it is a paste-in, copy-out tool rather than a file converter: there is no file picker and no download, and a very large paste is limited by what the tab can hold in memory.

How to use it

  1. Paste the messy CSV. Drop it into the input box, or press Load sample to see the sort of mess it is built for. The delimiter is guessed by counting commas, semicolons, tabs and pipes across the first five lines — set it by hand when the guess is wrong.
  2. Say whether the first row is a header. A header row is exempt from the placeholder, number and text-case rules, so column names are not emptied or recased by accident. It gets the separate Header names setting instead.
  3. Pick the rules you want. Trim, collapse spaces, tidy text, placeholder nulls and removing completely empty rows are all on by default. Normalising numbers, dropping empty columns, renaming headers and every text-case change are off until you switch them on.
  4. Run it and copy the clean CSV. The summary counts the cells that changed and any rows or columns dropped. Output keeps the input delimiter unless you choose a different one.

Processing & privacy

Runs on your device. Your file is processed directly in this browser.

Common use cases

A spreadsheet export nobody tidied

Values padded with spaces, a stray non-breaking space from a web paste, and a row of nothing at the bottom. One pass removes all three.

Text that came through a word processor

Curly quotes, en dashes and ellipsis characters break a downstream parser or import. Straightening them to ASCII is what tidy data usually means here.

Preparing a file for an importer

Many importers reject N/A in a numeric column but accept an empty one. Blanking the placeholders first turns a failed import into a clean one.

Making headers safe for code

snake_case turns "First Name" into first_name and appends _2 to a repeated name, so columns can be addressed without quoting. A nameless column comes out as plain column under that style, and as its position — column_2, column_3 — under the other three.

Questions

Does the table I paste leave this tab?
No. The parsing and rewriting happen in this browser tab, which is why the tool keeps working with the network disconnected. It is also why there is no file picker — you paste the table in and copy the result out.
Does it remove duplicate rows?
No. Data Cleaner never reorders, merges or de-duplicates rows; the only rows it drops are ones that are completely empty. Use Remove Duplicates when you need rows compared against each other.
Why did my "None" or "-" cell go empty?
Both are on the fixed placeholder list, and with tidy text on an em dash becomes a hyphen before that list is checked. Turn off the placeholder setting when those values mean something in your data.
Why was $1,204.50 left as it is?
Normalising numbers is off by default, and even switched on it only rewrites a cell that is entirely an amount. Anything carrying a unit, a suffix or a comma decimal separator is passed through untouched.
Does Title Case understand names and acronyms?
No. It uppercases the first letter of each run of letters and lowercases everything after it, so IBM becomes Ibm and McDonald becomes Mcdonald. Every text-case option leaves the header row alone.
What happens to rows with the wrong number of fields?
They are padded with empty cells to match the widest row, so the output is rectangular. Nothing is discarded — a row with extra fields widens the whole table rather than being truncated.
Why did a whole column disappear?
Removing empty columns judges each column on the data rows alone, so one that carries a name but no values is dropped together with its header. Blanking placeholders runs first, which means a column of nothing but NULL becomes eligible in the same pass.
Can it fix characters that already came out wrong?
No. Tidy text handles a specific set of nuisances — invisible characters, control codes, four narrow spaces, curly quotes, dashes and the ellipsis. Text decoded with the wrong character set, where é arrives as é, is beyond what this does.

Related tools

  • JSON to CSV — Flatten a JSON array into a CSV table.
  • CSV to JSON — Turn a CSV file into structured JSON.
  • CSV Viewer — Open and browse a large CSV without a spreadsheet app.
  • CSV to Excel — Convert CSV files into an XLSX workbook.
  • Excel to CSV — Export spreadsheet sheets to CSV.
  • CSV Merge — Combine multiple CSV files with matching columns.