CSV Data Cleaner

What this tool does

It takes a CSV that came out of a CRM, a spreadsheet or somebody's manual export, and returns one that will import without failing halfway through. The preview shows what changed, row by row, so you are never trusting a black box with data you are about to load into a live system.

It is for the person who has been handed a file and told to get it into HubSpot by this afternoon.

Interpreting the output: a worked example

Here is a fragment of a genuine-looking export from a contacts system:

First Name,Last Name ,Email,Signed Up,Company
 Sarah ,Khan,SARAH@EXAMPLE.COM,01/03/2026,Acme Ltd
Sarah,Khan,sarah@example.com,2026-03-01,Acme Ltd
Tom,Ali,tom@example.com,N/A,

Five separate problems, none of which an import wizard will tell you about:

The header Last Name has a trailing space. Most importers treat that as a different column name from Last Name and either drop it or create a duplicate field. The cleaner trims it and, if you ask, converts to last_name.

Rows one and two are the same person. Not byte-identical, so an exact-match deduplication misses them. Normalising case and whitespace first, then deduplicating on the email column, collapses them to one record. Order matters: deduplicate before normalising and you keep both.

01/03/2026 is ambiguous. That is 1 March in Britain and 3 January in the United States, and nothing in the file says which. The cleaner flags mixed date formats rather than guessing, and only converts once you confirm the source locale. A silent wrong guess here is worse than an error, because it produces a file that looks fine.

N/A is not empty. It is a three-character string that will import as a literal value into a date field and either fail or store nonsense. Placeholder values — N/A, n/a, none, -, NULL — get mapped to genuinely empty cells.

The last row has a trailing empty field. Fine here, but if a row has more fields than the header, something upstream broke the quoting and the file needs looking at rather than cleaning.

The method

The parsing follows RFC 4180, the memo that defines the common CSV format: fields separated by commas, records by CRLF, and any field containing a comma, a quote or a line break wrapped in double quotes with internal quotes doubled. Most breakage traces back to a producer that ignored that last rule.

The wider question of what a column means — its data type, its units, whether it can be empty — is not in RFC 4180 at all. If you need to ship that alongside the data, the W3C's model for tabular data on the web is the standard way to describe it.

Encoding is detected before parsing. Files exported from Excel on Windows are frequently Windows-1252 rather than UTF-8, which is why a curly apostrophe arrives as ’.

Cleaning options compared

Excel deserves the specific warning: opening a CSV in it will convert 007 to 7 and reinterpret anything that looks like a date. If you must inspect a file in Excel, use the import dialogue and set every column to text.

Common mistakes

Deduplicating on the wrong column. Names collide, emails rarely do. Pick the field that is genuinely unique in your data, and normalise case before you compare.

Cleaning after the import fails. By then a partial load has often already created records. Clean first, import once.

Trusting the row count. A file with broken quoting can parse to a smaller number of rows than it has lines and still produce output. Compare the row count in and out.

Stripping whitespace from everything. Trailing spaces in a postcode field are noise; a leading space inside a genuine free-text note is not. Trim identifiers, leave prose alone.

Assuming the file is UTF-8. Check, do not hope.

FAQ

Does the file get uploaded to a server?

The cleaning runs in your browser, so the contents of the file are not sent anywhere. That matters when the CSV is a contact list — under UK and EU data protection rules, that file is personal data and moving it through a third party is a decision, not a detail.

Why did my dates change after cleaning?

Only if you confirmed a source format. The tool flags ambiguous dates such as 01/03/2026 and waits, because there is no way to infer day-first from month-first out of a single value. If dates changed unexpectedly, check whether the file passed through Excel first.

Can it handle semicolon-separated or tab-separated files?

Yes. The delimiter is detected from the header row, which covers the semicolon-separated files that European versions of Excel produce by default.

What happens to rows with the wrong number of fields?

They are reported rather than silently padded. A row with extra fields almost always means a quote is unbalanced somewhere above it, and padding it would hide a real problem in whatever produced the file.

How do I stop having to do this every week?

Automate the pipeline instead of the file. A workflow that receives the export, applies the same cleaning rules, validates the result and loads it into the destination removes the manual step entirely. CRM automation for small businesses covers the shape of that, and Make.com Google Sheets integration covers a common destination. The JSON Formatter is the equivalent tool when your data arrives from an API rather than a spreadsheet.

Next step

If you are cleaning the same export by hand every week, that is a pipeline waiting to be built: receive, clean, validate, load, and alert someone when a file arrives malformed. I build these for CRM and reporting stacks — find me on Fiverr.