How to Clean a Data File Without Writing a Script

Every export has the same twenty things wrong with it. Not because anyone was careless — because a CSV has no types, every system writes dates its own way, and somewhere between the database and your desk a file passed through a tool that made a guess.

The short answer

Clean a data file by finding the problems first and fixing them as one previewable pass, rather than by editing cells. In DataTray, open Clean & Anonymize, drop the file in, and it lists what it found — numbers stored as text, dates in four formats, trailing whitespace, inconsistent casing, mojibake, lost leading zeros — each with a count of affected rows. You pick the fixes, reorder them if you want, preview the result, and run it once. A modified copy is written next to your original, which is never touched.

The twenty things, and what causes them

Most of what people call "messy data" is one of a small number of recurring faults:

None of these are hard to fix individually. The difficulty is that there are twenty of them, they are spread across forty columns, and finding them by eye means scrolling.

Find first, then fix

The order matters more than the technique. A tool that lets you apply fixes without first telling you what is wrong turns cleaning into guesswork — you fix what you noticed and ship what you did not.

DataTray scans the file and reports findings ranked by severity, each with the number of rows affected and a preview of them. That is the part worth insisting on regardless of which tool you use: you should be able to see the problem, and its size, before you agree to change anything.

Fixes that are one click each

Once a finding is confirmed, the fix is a single operation:

• Trim leading and trailing whitespace; collapse runs of internal spaces.
• Normalize case — upper, lower, title or sentence.
• Coerce types, with anything that will not convert written to a rejects file rather than silently nulled.
• Normalize dates to ISO-8601, so 12/03/2026 stops being ambiguous.
• Normalize numbers, locale-aware, so both separator conventions land in one format.
• Find and replace, literal or by regular expression, per column.
• Split a column by delimiter, fixed width or pattern — the text to columns job, on a file Excel could not open.
• Merge columns, rename, reorder, drop.
• Fill blanks with a constant or by carrying the previous value down.
• Standardise a category column against its own values, so twelve spellings of "California" collapse to one.

They apply in order, and the order is yours to change. Coercing types before trimming whitespace fails on values that only look non-numeric because of a trailing space — so the stack is reorderable, and the preview shows you the difference.

What "smart" should and should not mean

Detection here is deterministic. "More than 95% of values in this VARCHAR column cast cleanly to a number" is a rule you can check, and it is explainable in one sentence. That matters more than it sounds: a cleaning tool that cannot tell you why it flagged something is a tool you have to audit by hand anyway.

There is no model involved and no API key to enter. Nothing about the file is sent anywhere to be analysed, which is the other reason to do this locally — the file that most needs cleaning is usually the one you would least like to upload.

Personal data, found in the same pass

Because the scan is already reading every column, it also flags the personal data: emails, phone numbers, national IDs, payment cards (validated with a Luhn check, so a 16-digit order number is not flagged), IBANs, IP addresses, dates of birth and likely names. Each is reported at column level with a confidence and a row count.

You can mask, hash, tokenize or generalize any of them in the same run. A word of caution worth stating plainly: this finds and masks personal data, it does not certify a file as anonymous. The rules can miss things and can flag things that are not personal, and name detection in particular is low-confidence. Whether the result is adequate for your purpose is your call, not the tool's.

Do it once, then do it every month

Cleaning is rarely a one-off. The export that needed twelve fixes this month will need the same twelve next month, because the system producing it has not changed.

Save the stack as a recipe and run it again on next month's file — same fixes, in the same order. That turns a recurring hour into a recurring minute.

Nothing is lost quietly

Two guarantees worth checking for in any cleaning tool, because their absence is what makes cleaning risky:

• Your original is never modified. The output is a new file with a dated, self-describing name, so you can always go back and compare.
• Anything that did not survive is written down. Values that would not coerce go to a rejects file beside the output. Losing rows silently is the failure mode that makes people distrust cleaned data, and it is entirely avoidable.

---

Every other free way to do this asks you to upload the file first. DataTray does it on your own machine, on a file of any size, and hands you back a report of exactly what it changed.