Cleaning Text Before Import: Hidden Characters That Break Pipelines

A UTF-8 BOM is the three bytes EF BB BF. Many Windows tools write it; many Unix parsers choke on it, treating the first field name as an unknown column. Open the file in a hex view or a tool that shows the BOM explicitly. If present, strip it before import. Do not strip a UTF-16 BOM without also converting the encoding, or the rest of the file becomes garbage.

The non-breaking space U+00A0 looks identical to a normal space but is a different code point. Spreadsheets and web copy often insert it. Equality checks fail, split operations leave residues, and trim functions that only know U+0020 leave it in place. Replace non-breaking spaces with ordinary spaces unless the non-breaking behaviour is intentional, such as in French punctuation.

Word and many CMS tools convert straight quotes into curly quotes and hyphens into en or em dashes. Those characters are fine in prose and fatal in CSV fields that a script later compares to ASCII quotes. Normalise to straight quotes and hyphens when the destination is a machine parser; keep smart punctuation when the destination is a published article.

CRLF from Windows, LF from Unix, and occasional bare CR from older Mac files can all appear in one dataset after multiple exports. Most modern parsers accept any of them, but some splitters and checksums do not. Normalise to LF for tools that live on Unix, or CRLF when a Windows specification demands it. Also ensure the final line ends with a newline; some importers ignore a last line that does not.

A null byte U+0000 inside a text field is almost always corruption or an accidental binary paste. Strip nulls. Other control characters below U+0020, aside from tab and newline, are rarely intentional in tabular data. Decide per pipeline whether to strip them or fail the row; silent stripping can hide a broken upstream export.

If a CSV field contains the delimiter and is not quoted, columns shift and every subsequent field is wrong. Cleaning text cannot invent quotes that were never there, but it can report rows whose field count differs from the header. Fix the export quoting, or switch to a format less fragile than CSV when fields regularly contain commas, quotes or newlines.

Detect encoding first, including any BOM. Decode to Unicode. Replace non-breaking spaces and normalise quotes if the destination needs ASCII punctuation. Normalise line endings. Validate field counts. Only then load into the database or spreadsheet. Cleaning after a failed import is slower than cleaning before, because the error messages usually point at the wrong column.

Silent cleaners that rewrite files in place make failures hard to replay. Prefer a step that writes a cleaned copy and a short log of replacements: BOM removed, N non-breaking spaces normalised, line endings converted, M rows with field-count mismatches. When an import still fails, the log tells you whether cleaning already ran and what it touched. That turns a mysterious parser error into a checklist item you can re-run.

If half the rows have the wrong number of columns, the export is broken and no amount of character cleanup will restore meaning. Fix quoting and delimiters at the source. If values contain embedded line breaks that are semantically part of the field, stripping newlines will destroy them. Cleaning is for invisible encoding and punctuation problems, not for repairing a schema mismatch or a truncated download.