Cleaning Messy Text Data Before You Import It
Every data import that goes wrong goes wrong in roughly the same way. The file looks fine on screen, the import reports success, and then a week later somebody notices there are two of everything, or that a column of postcodes has lost its leading zeros. The problems were present in the source data all along; they were simply invisible.
What follows is an order of operations for cleaning exported text data. The order matters more than any individual step, because several of these operations interfere with each other if done in the wrong sequence.
1. Look at the file before you touch it
Before cleaning anything, find out what you actually have. Three questions answer most of it: how many lines are there, what does the first line contain, and are the line endings consistent?
The line count tells you whether the export completed. A suspiciously round
number — exactly 1,000 or 5,000 rows — often means you hit a pagination limit and
are looking at a partial export. The first line tells you whether there is a header
row, which almost every subsequent step needs to know about. Line endings matter
because a file produced on Windows uses \r\n and one from macOS or
Linux uses \n; mixed endings inside a single file are a reliable sign
that it has been edited by several tools in sequence, and they cause trailing
carriage returns to end up inside your values.
2. Normalise whitespace first
This step comes first because everything downstream depends on exact string comparison, and whitespace is what breaks exact comparison.
Trailing spaces are the classic offender. They are invisible, they survive
copy-paste, and they mean that "Milan " and "Milan" are
two different values as far as any computer is concerned. Deduplication will not
catch them. Lookups against a reference table will fail on them. Grouping in a
pivot table will produce two rows that look identical.
Non-breaking spaces deserve a special mention. Text copied from a web page or a Word document frequently contains U+00A0 instead of a regular space. It renders identically, it is not matched by a search for a normal space, and it will quietly defeat every cleanup you attempt until you specifically look for it.
The one thing to be careful about at this stage is indentation. If your data is actually structured text — YAML, Python, Markdown — leading whitespace is content, not noise, and a cleaner that collapses all runs of spaces will destroy it. Use a tool that separates leading whitespace from the rest of the line.
3. Fix encoding before you fix anything else textual
If you see è where è should be, or ’
where an apostrophe belongs, you are looking at mojibake: UTF-8 bytes that have
been interpreted as a single-byte encoding, usually Windows-1252. This is not a
find-and-replace problem, even though it is tempting to treat it as one. Every
accented character produces a different mangled sequence, and patching them one at
a time guarantees you will miss some.
The correct fix is to re-export or re-open the file with the right encoding declared. If that is genuinely impossible, a systematic transcoding pass is the next best option — but do it before deduplication, because mojibake creates near-duplicates that differ only in their mangled characters, and deduplicating first will leave you with both versions.
4. Then deduplicate
Deduplication belongs after whitespace and encoding cleanup, for the reason just given: those two problems create pairs of records that are semantically identical but textually different. Deduplicate too early and you keep both.
Decide deliberately whether matching should be case-sensitive. For email addresses it should not be — the domain part is definitively case-insensitive, and in practice the local part is treated that way by essentially every mail provider. For anything that is a genuine identifier, such as a Base64 token or a case-sensitive product code, it must be.
Also decide what "duplicate" means for your data. Two rows with the same email address but different names may be a genuine duplicate with a typo in one copy, or two people sharing a family address. Line-level deduplication cannot tell the difference, so for records rather than simple lists, extract the key column first and inspect what repeats before deleting anything.
5. Convert structure last
Only once the text is clean should you convert it into its target structure — JSON, a database table, a spreadsheet import. Structure conversion is the step that locks in whatever mistakes remain, because after it the data is no longer a flat list you can easily re-clean.
The trap at this stage is type inference. Anything that helpfully guesses types
will convert 01234 to 1234, 1.10 to
1.1, and a product code like 2024-01 to a date. All three
are lossy and none of them are reversible. When a value is an identifier rather
than a quantity, keep it as text — even when it consists entirely of digits. The
test is simple: would you ever do arithmetic with it? If not, it is text.
6. Verify with counts, not with eyes
Finish by checking numbers rather than scrolling. Compare the line count before and after each step and make sure every change is one you intended. If deduplication removed 4,000 of 10,000 rows, that is either a genuinely duplicated export or a sign that you deduplicated on the wrong field — both are worth knowing before you import.
Count how many rows have empty values in the fields that matter. Check that the first and last rows still look like data rather than headers or footers. If you started with a header row, confirm it is still there — or deliberately gone.
The short version: whitespace, then encoding, then duplicates, then structure, then verify by counting. Every one of those steps done out of order creates work for the next one.
A note on doing this in the browser
For files up to a few megabytes, browser-based tools are a reasonable choice for these steps, and they have one specific advantage worth naming: the data never leaves your machine. That matters when the export contains customer records, addresses or anything else you would rather not paste into a service whose retention policy you have not read. It is also the reason DevClean processes everything locally.
Beyond a few hundred thousand rows, move to a proper tool — a script, a database, or a dedicated data-preparation application. The order of operations above stays exactly the same; only the implementation changes.