How to Find and Remove Hidden Characters in a CSV

A CSV that will not import correctly, or where a lookup mysteriously fails, is often carrying hidden characters. Here is how to find and remove them.

How hidden characters break a CSV

CSV is plain text, so invisible characters sit right in your data:

• A byte-order mark (BOM) at the start of the file attaches to the first column header, so id becomes an invisible-character-plus-id that does not match your code's id.
• A zero-width space in a header or key breaks column matching and joins between tables.
• A non-breaking space in a numeric field can stop it from parsing as a number.
• Trailing whitespace in cells causes lookups and comparisons to fail.

Because these are invisible, the file looks correct in a spreadsheet while imports and joins quietly break.

How to find them

• Check for a BOM. Open the file in an editor that shows encoding; "UTF-8 with BOM" is the classic offender that breaks the first header.
• Reveal whitespace. In a code editor, enable "show whitespace" or non-breaking-space highlighting to spot odd spaces in cells.
• Search by code point. Use a regex-capable editor to search for \uFEFF, \u200B, and \u00A0.
• Watch for a header mismatch. If your code cannot find a column that clearly exists, an invisible character in the header is a prime suspect.

How to remove them

• Save as UTF-8 without BOM. In VS Code, click the encoding in the status bar and choose "Save with Encoding" > "UTF-8" (not "with BOM").
• Strip cell-level characters. For zero-width and non-breaking spaces inside cells, run a find-and-replace by code point, or process the file in a script that trims and strips invisible characters from each field.
• Clean copied data. If you are pasting CSV content from somewhere, run it through a cleaner first. textscrubr strips the BOM and invisible characters and normalizes spaces, so the data you save is clean.

Prevent it in a pipeline

If CSVs come from exports or other tools, add a normalization step, strip BOM, trim fields, remove zero-width characters, before the data is loaded. It turns a class of baffling import and join failures into a solved problem.