CSV differences caused by spaces, letter case and NULL
Decide which differences matter before cleaning values that only look the same.
Two CSV values can look identical while containing different characters. A space at the end of an ID, a nonbreaking space inside a name or a difference in letter case can affect matching. Missing-value markers add another problem: an empty field and the text NULL are different source representations.
Inspect one record before cleaning a whole file
Choose a known ID and open its complete row on both sides. Check the exact imported values. If the ID itself differs, the record may appear only in each file. If the ID matches but another field differs, it should be reviewed as a matched-record change under the selected rules.
For synthetic values, “North” and “North ” differ by a trailing space. “New York” and “New York” differ inside the value. Trimming the ends does not remove the extra internal space. A cleanup rule that fixes the first pair does not necessarily fix the second.
Know the app’s comparison rules
Spreadsheet Comparison trims surrounding whitespace and normalizes several common missing-value representations, including NULL, NA, N/A and nil. It also applies field-aware rules to phone numbers, boolean-like fields and US country names. The Ignore letter case option controls case-insensitive comparison. Original values remain available for inspection.
These rules can hide source-formatting differences intentionally. A result saying values match means they match under those rules; it does not mean the original files are byte-for-byte identical. A legitimate code of NA also deserves care because a missing-value rule can treat it as blank.
Do not assume every invisible space is ordinary
Excel’s TRIM function does not remove the nonbreaking space character by itself, as Microsoft’s TRIM documentation explains. If cleaning in Excel does not fix a mismatch, identify the actual character before applying another replacement.
Case also depends on field meaning. Uppercase and lowercase may be interchangeable for a human-readable city name but significant in a product code. Review the implications before enabling a comparison-wide case rule.
Keep cleanup separate from your evidence
When a transformation is necessary, apply it to working copies and record it. Do not overwrite the originals. Spot-check an expected match and a value that should remain different, so the cleanup does not silently erase the change you were looking for.
If every field seems different, revisit column mapping. If only identities fail to match, check leading zeros and the selected row key.