CSV import: normalize the field, preserve the structure

Topic illustration for the article: CSV import: normalize the field, preserve the structure
Practical illustration for this guide

When two table rows fail to join on a key, the cause may be U+200B, NBSP, case, encoding, or data type. Start with a pair of values that should match and write down the allowed format for that field. Cleaning the entire CSV before diagnosis can damage quotes, embedded newlines, and values.

Try the example in the cleaner

A reproducible key example

AB-42 and AB-[U+200B]42 look alike but contain different code points. If a technical identifier allows only Latin letters, digits, and a hyphen, removing a confirmed U+200B is appropriate. The same rule is not appropriate for a personal name, product description, or Thai text, where an invisible character may have a valid function.

Inspect field edges too. AB-42[U+00A0] differs from AB-42 even though the trailing NBSP is easy to miss. An importer's trim operation may not cover every spacing character, so verify code points instead of relying on appearance.

Do not clean CSV as one text block

RFC 4180 documents commas, double quotes, and line breaks as structural parts of CSV. A quoted field may legitimately contain a comma or newline. Replacing those characters before parsing can split one record or merge separate fields. First read the file with a CSV parser configured for the intended delimiter, encoding, and quote rules; transform only the selected column afterward.

Keep the original field, normalized field, and reason for change during a controlled migration. This audit trail makes review and rollback possible. For a large import, count every operation: U+200B removals, NBSP replacements, unchanged values, and rejected rows.

A schema is better than universal cleaning

  • Identifier: allowed alphabet, length, case, and significant leading zeroes.
  • Number: decimal and grouping separators, sign, and valid range.
  • Date: an unambiguous format and a time zone when time matters.
  • Free text: paragraph boundaries, language-specific characters, and typographic spaces.

The W3C CSV on the Web model shows why tabular data benefits from metadata: headings, data types, and constraints enable meaningful validation. 0012 may be a product code that must preserve its zeroes; converting it to the number 12 would lose information.

Unicode normalization and collisions

Unicode normalization and invisible-character removal are separate operations. NFC can make canonically equivalent letter-and-mark sequences comparable, but it should not be applied blindly to passwords, cryptographic values, or external identifiers whose exact code points matter. After any transformation, look for collisions: different original keys can collapse to one value.

  1. Group records by the transformed value.
  2. Report groups containing more than one distinct original.
  3. Do not merge automatically; compare an independent identifier.
  4. Import into a staging table and compare row counts, nulls, and unique-key failures.

Encoding, BOM, and round-trip testing

Declare UTF-8 explicitly when reading and writing. An initial U+FEFF can be consumed as a byte-order mark or appear inside the first header, depending on the importer, so inspect the first column name separately. Preserve the delimiter and quote policy on export, then parse the exported file again. A successful write does not prove that the same structure can be read back.

Open the cleaner

Sources and related guides