Import Builder/Guides/Invisible Characters in CSV
Data Quality

Fix Invisible Characters Breaking Your CSV Imports

Invisible characters—BOM markers, zero-width spaces, non-breaking spaces, and hidden control characters—corrupt CSV data without being visible. Learn what causes them and how to remove them.

What are invisible characters?

Invisible characters are whitespace or control characters that don't display visibly but affect how computers read and process data. They're often inserted unintentionally when copying from web pages, PDFs, Word documents, or certain tools.

The most common culprits are:

A BOM can hide the first column

A 3-byte marker (EF BB BF) at the start of a UTF-8 file. Excel uses it to recognise UTF-8, but some importers read it as part of the first header, so a column called Name isn't found.

Zero-width spaces break matching and search

Invisible character copied from web pages that prevent 'John' from matching 'John' in your database. Breaks deduplication.

Non-breaking spaces cause validation failures

Spaces that look like regular spaces (U+00A0) but aren't removed by Excel's TRIM. Email validation fails: 'john@example.com ' (with invisible space) != 'john@example.com'.

Control characters corrupt entire rows

Hidden characters like NULL (U+0000) or line separators can break CSV parsing entirely, making rows unreadable.

Stray line endings break rows

Most importers accept Windows (CRLF) or Unix (LF) line endings. A file that mixes them, or a stray carriage return inside a value, can leave blank or broken rows.

Real example:

How invisible characters break imports:

What you see:          | What the system sees:
----------------------|------------------------
Name, Email, Status    | [BOM]Name, Email, Status
John, john@ex.com, ✓   | John, john@ex.com[NBS], ✓
Jane, jane@ex.com      | Jane, jane@ex[ZWS].com

[BOM] = Byte Order Mark (invisible at start)
[NBS] = Non-Breaking Space (invisible at end)
[ZWS] = Zero-Width Space (completely invisible)

❌ First column "Name" not found
❌ Email validation rejects valid emails
❌ Deduplication fails (same person looks different)

The tricky part: You can't see these characters visually, so the CSV looks fine in Excel or Google Sheets. But the import system sees them and rejects the data.

Common invisible character problems

BOM at the start of the file

Where it comes from:

Excel's CSV UTF-8 format, and editors set to save 'UTF-8 with BOM'

What you see:

The first header shows as 'Name', or the import says the first column is missing

What breaks:

Only the first column. Many importers handle a BOM without trouble, so remove it only when one doesn't.

Non-breaking spaces (U+00A0) in data

Where it comes from:

Copied from web pages, PDF documents, or Word

What you see:

Looks like a regular space but won't trim or match

What breaks:

Emails won't validate. Deduplication misses duplicates. Search fails.

Zero-width spaces (U+200B)

Where it comes from:

Copied from web pages, chat apps or formatted documents

What you see:

Characters look connected but validation treats them as separate

What breaks:

Name matching breaks. 'John Smith' won't match duplicate 'John​ Smith' (invisible space)

Tab characters instead of spaces

Where it comes from:

Text copied from formatted documents or code editors

What you see:

Data looks aligned but contains tab characters

What breaks:

Tools that guess the delimiter can mistake the file for tab-separated.

Mixed line endings (LF vs CRLF)

Where it comes from:

Edited on both Mac/Linux and Windows, or concatenated files

What you see:

Invisible but affects how rows are read

What breaks:

Some importers show blank rows or split a row in two.

NULL bytes (U+0000) in the middle of text

Where it comes from:

Corrupted data from database exports or improper encoding conversions

What you see:

Text appears truncated but no visible character is missing

What breaks:

CSV parsing breaks entirely for that row. Data becomes unusable.

Types of invisible characters

Here's a reference of common invisible characters and what they do:

BOM (Byte Order Mark)

Unicode:

EF BB BF (UTF-8)

Appears as:

First byte of file

Impact: Some importers read it as part of the first header.

Non-breaking space

Unicode:

U+00A0

Appears as:

Looks like a space, but Excel's TRIM leaves it

Impact: Won't match regular spaces. Validation fails. Breaks email matching.

Zero-width space

Unicode:

U+200B

Appears as:

Completely invisible, no width

Impact: Breaks string matching and deduplication.

Zero-width joiner

Unicode:

U+200D

Appears as:

Invisible, used to connect characters

Impact: Causes matching failures in international text.

Tab character

Unicode:

U+0009

Appears as:

Looks like spaces

Impact: Breaks delimiter detection if not expecting tabs.

NULL byte

Unicode:

U+0000

Appears as:

Completely invisible

Impact: Truncates data. CSV parsing breaks.

Carriage return (CR)

Unicode:

U+000D (\r)

Appears as:

Invisible line break (Windows uses CR followed by LF)

Impact: A stray one inside a value can split a row.

Line feed (LF)

Unicode:

U+000A (\n)

Appears as:

Invisible line break

Impact: Rarely a problem on its own; most importers accept LF or CRLF.

How to detect invisible characters

Since invisible characters don't display, you need tools to find them:

Method 1: Use a text editor with special characters visible

VS Code (free):

  1. Open your CSV file
  2. Go to View → Render Whitespace
  3. Invisible spaces and tabs will show as dots (·) and arrows (→)

Notepad++ (free, Windows):

  1. Open your CSV file
  2. View → Show Symbol → Show All Characters
  3. Invisible characters appear as visible symbols

Method 2: Check the file encoding

In VS Code:

  1. Look at bottom right corner
  2. It shows the file encoding (UTF-8, UTF-8 with BOM, Latin-1, etc.)
  3. "UTF-8 with BOM" only matters if the importer can't find your first column

On Mac: Use Terminal command: file -b your-file.csv

Method 3: Check for BOM specifically

Open the file in a hex editor or Terminal to see the first bytes:

# On Mac/Linux Terminal: hexdump -C your-file.csv | head -1 # Shows: # ef bb bf = BOM present (remove it if the first column isn't found) # 2c = comma (✓ normal)

Method 4: Copy & paste test

In Excel or Google Sheets, compare a value's length with what you can see:

  • Enter =LEN(A2) next to the value
  • "john@example.com" is 16 characters. If LEN says 17 or more, something invisible is in there

How to fix invisible characters

Step 1: Remove BOM if present

In VS Code:

  1. Click the encoding indicator (bottom right)
  2. Select "Save with Encoding"
  3. Choose "UTF-8" (without BOM)
  4. File is resaved without the BOM marker

From Excel:

Excel's CSV UTF-8 format always adds a BOM. If an importer can't handle it, resave the file in a text editor as above, or download it as CSV from Google Sheets, which doesn't add one.

Step 2: Remove non-breaking and zero-width spaces

In Excel, with a formula:

=TRIM(SUBSTITUTE(SUBSTITUTE(A2,CHAR(160)," "),UNICHAR(8203),""))

This turns non-breaking spaces into normal ones, removes zero-width spaces, then trims. Copy it down a helper column and paste the results back as values.

In Excel, with Find & Replace:

  1. Press Ctrl+H
  2. In Find what, hold Alt and type 0160 on the number pad (Windows) to enter a non-breaking space
  3. In Replace with, type a normal space
  4. Click Replace All

Or in a text editor (VS Code):

  1. Find & Replace (Ctrl+H) and turn on regex (the .* icon)
  2. Find \u00A0, replace with a normal space
  3. Find [\u200B\u200C\u200D], replace with nothing

Don't replace \s+ across a whole file: it matches line breaks too, and joins every row into one.

Step 3: Fix line endings (Windows vs Mac/Linux)

Only needed if the importer shows blank or broken rows. Most accept either style; what matters is one style throughout:

In VS Code:

  1. Look at the bottom right corner
  2. Click where it shows "CRLF" or "LF"
  3. Pick one, CRLF or LF, and save
  4. Every line now ends the same way

Step 4: Use TRIM to clean leading and trailing spaces

In Excel or Google Sheets:

# In Excel, create a helper column: =TRIM(A1) # Copy down for all rows, then: 1. Select the TRIM results 2. Copy (Ctrl+C) 3. Paste Special → Values only into original column 4. Delete the helper column # TRIM removes spaces before and after, and turns double # spaces inside a value into one. It doesn't remove # non-breaking spaces: use the formula in step 2 for those.

Step 5: For serious corruption, re-export from the source

If invisible characters are embedded throughout the file, the safest approach is to re-export from the original source:

  • Export from your database or system directly as CSV
  • Don't copy/paste from web pages or PDFs
  • Save directly to CSV, don't open in Excel first
  • Verify the file opens correctly before importing

Best practices to prevent invisible characters

Save CSV files as UTF-8, and remove the BOM only if an importer can't find the first column
When pasting from web pages, PDFs or Word, use Paste Special and choose text or values only
Export directly from your database or system to CSV—don't go through Excel
Use one line-ending style per file (most importers accept CRLF or LF)
Use a text editor like VS Code to view the raw file before importing
Enable 'Show Whitespace' in your editor to spot invisible spaces
Clean text with TRIM plus SUBSTITUTE for non-breaking spaces, since TRIM alone leaves them
Test imports with a small sample file first before importing thousands of rows
If data fails validation, check individual cells for invisible characters
When documenting CSV exports, state the encoding, delimiter and line endings
Ask data providers to export directly instead of having you copy-paste
For international data, verify UTF-8 support throughout your import pipeline
Use Find & Replace with regex to systematically clean up bad characters

Related issues: Invisible characters are often related to encoding problems. See also:

Avoid invisible character problems at the source

The best way to prevent invisible character issues is to collect data through forms instead of spreadsheets. Users fill out a clean form, you get data without file-level corruption (no BOM markers, encoding mismatches, or line ending issues).

Learn more about Import Builder