Import Builder/Guides/Commas in CSV Data
CSV Basics

Commas Breaking Your CSV Imports?

When your data contains commas, CSV files get parsed incorrectly. Learn how CSV quoting and escaping work, and how to properly format data with commas.

How do you escape a comma in a CSV?

Wrap the whole field in double quotes. A comma inside quotes is part of the value, not a separator:

Name,Address,City
"Smith, John","123 Main St, Apt 4B",New York

There's no backslash escape

Standard CSV has no \, escape. A backslash is read as an ordinary character, so "123 Main St\, Apt 4B" still splits in two. Quoting is the only way.

Double any quotes inside a quoted field

A quote mark in the value is written twice: "She said ""hi"", then left" is read as She said "hi", then left.

No space before the opening quote

Write ,"Apt 4B, Floor 2" not , "Apt 4B, Floor 2". With a space first, strict CSV readers treat the quote as an ordinary character and split the field at the comma.

These rules come from RFC 4180, the closest thing CSV has to a standard: “Fields containing line breaks (CRLF), double quotes, and commas should be enclosed in double-quotes,” and “a double-quote appearing inside a field must be escaped by preceding it with another double quote.”

You rarely need to do this by hand. Excel and Google Sheets quote fields automatically when they save a CSV. Broken files usually come from a script that joins values with commas, or a file edited in a text editor. In code, use a CSV library, such as Python's csv module or Papa Parse in JavaScript, rather than joining strings.

The problem: Commas are the delimiter

CSV stands for "Comma-Separated Values." The comma is the character that splits one column from another. But what happens when your actual data contains commas?

If you don't quote the field, the import system thinks the comma is a delimiter and splits your data across multiple columns. This causes data to import into the wrong cells.

Data splits into wrong columns

Address "123 Main St, Apt 4B" becomes two columns: "123 Main St" and "Apt 4B".

Entire rows become misaligned

One row with a comma in one field shifts all following data left or right.

Multiple commas multiply the damage

"Smith, John, Jr." splits into 3 columns instead of 1.

Import counts are wrong

You import 100 rows but end up with 120+ columns because rows split unexpectedly.

Data ends up in unrelated columns

A company name "Apple, Inc." ends up with "Apple" in Name column and "Inc." in Email column.

Real example:

What your data looks like in Excel:

Name              | Address           | City
------------------|-------------------|----------
Smith, John       | 123 Main St, Apt 4B| NYC
Johnson, Mary     | 456 Oak Ave, Suite 200 | LA

What the raw CSV looks like (WRONG):

Name,Address,City
Smith, John,123 Main St, Apt 4B,NYC
Johnson, Mary,456 Oak Ave, Suite 200,LA

CSV parser sees this as:

Col1    | Col2 | Col3        | Col4      | Col5
--------|------|-------------|-----------|-----
Smith   | John | 123 Main St | Apt 4B    | NYC  ❌
Johnson | Mary | 456 Oak Ave | Suite 200 | LA   ❌

Solution: Wrap fields containing commas in double quotes. This tells the CSV parser "treat everything between these quotes as one value, even if there are commas inside."

How CSV quoting and escaping work

CSV has a standard way to handle special characters: wrap the entire field in double quotes. This is called "quoting."

Rule 1: Quote fields that contain commas

❌ WRONG: Name,Address,City Smith, John,123 Main St, Apt 4B,NYC ✓ CORRECT: Name,Address,City "Smith, John","123 Main St, Apt 4B",NYC

Notice: The field "Smith, John" is quoted, so the comma inside doesn't split the field.

Rule 2: Quote fields that contain quotes

If your field contains a double quote, wrap it in quotes and escape the inner quote with another quote.

❌ WRONG: Company,Notes "Apple, Inc.",They said "hello" ✓ CORRECT: Company,Notes "Apple, Inc.","They said ""hello"""

The inner quotes are doubled: "" = one quote in the actual data

Rule 3: Quote fields that contain line breaks

Multi-line cell content must be quoted to keep it in one cell.

❌ WRONG - will split into 2 rows: Name,Notes John,Line 1 Line 2 ✓ CORRECT - stays as 1 row: Name,Notes John,"Line 1 Line 2"

Rule 4: It's safe to quote any field

You don't HAVE to quote fields without special characters, but you can. It won't hurt.

Both are valid: Name,Address,City Smith,123 Main St,NYC "Smith","123 Main St","NYC"

Real-world examples with commas

Here are common data types that contain commas:

Names (Last, First format)

Example:

"Smith, John"

Why:

Names formatted as Last, First have a comma.

Addresses

Example:

"123 Main St, Apt 4B"

Why:

Street number and apt number are comma-separated.

Cities and States

Example:

"New York, NY" or "Los Angeles, CA"

Why:

City, State format uses a comma.

Company names

Example:

"Apple, Inc." or "Smith, Johnson & Associates"

Why:

Many companies have commas in their legal names.

Product descriptions

Example:

"Shirt, Blue, Large"

Why:

Comma-separated attributes.

Lists

Example:

"Red, Green, Blue" or "Item1, Item2, Item3"

Why:

Any comma-separated list in a single cell.

Quotes/testimonials

Example:

"He said, ""This is great!"""

Why:

Text with punctuation including quotes.

Common mistakes with comma handling

Forgetting to quote fields with commas

❌ Wrong:

Smith, John,123 Main St, Apt 4B,NYC

✓ Right:

"Smith, John","123 Main St, Apt 4B",NYC
Result: Data splits into wrong columns

Using single quotes instead of double quotes

❌ Wrong:

'Smith, John','123 Main St',NYC

✓ Right:

"Smith, John","123 Main St",NYC
Result: CSV standard requires double quotes, not single

Forgetting to escape quotes inside quoted fields

❌ Wrong:

"He said "hello" today"

✓ Right:

"He said ""hello"" today"
Result: Parser gets confused by unescaped quote in the middle

Putting a space before the opening quote

❌ Wrong:

"Smith, John", "123 Main St, Apt 4B"

✓ Right:

"Smith, John","123 Main St, Apt 4B"
Result: With a space first, strict CSV readers treat the quote as text and split the address at its comma

Using backslash to escape quotes (common in other formats)

❌ Wrong:

"He said \"hello\""

✓ Right:

"He said ""hello"""
Result: CSV doesn't use backslash escaping; it uses doubled quotes

How to fix comma problems

Step 1: Check if your fields need quoting

Before exporting, identify fields that contain commas or special characters:

  • Names (Last, First format)
  • Addresses
  • Company names
  • Product descriptions
  • Any list or comma-separated values in a field

Step 2: Quote fields in Excel or Google Sheets

Excel doesn't show quotes in the cell, but when you save as CSV:

  1. Excel automatically quotes fields with commas
  2. Excel automatically quotes fields with quotes or line breaks
  3. Just save normally as CSV — Excel handles quoting

✓ Good news: Excel does the right thing by default. When you save to CSV, it automatically quotes fields that need it.

Step 3: Verify the CSV in a text editor

Before importing, open the CSV in a text editor to verify quoting is correct:

Name,Address,City "Smith, John","123 Main St, Apt 4B",NYC "Johnson, Mary","456 Oak Ave",LA Brown,"123 Elm St",Boston

Fields with commas should be quoted. Fields without commas don't need to be.

Step 4: If a CSV is broken, re-export from the source

If the CSV has unquoted commas causing import failures:

  • Go back to the source (spreadsheet, database, form tool)
  • Re-export as CSV
  • The export tool should automatically handle quoting
  • Manually editing CSV files with lots of commas is error-prone

Step 5: Test with a small sample first

Before importing 10,000 rows, test with 10-20 rows that contain commas to make sure the quoting is correct and the import system handles it properly.

Best practices for handling commas in CSV

Let Excel/Google Sheets handle quoting—don't manually edit CSV files
Use standard double quotes (") not single quotes (')
When exporting, verify quoting is correct by opening in a text editor
Test imports with sample data that contains commas before importing everything
If exporting from a database, ensure it properly quotes fields with commas
Document which fields contain commas so data providers know to quote them
Don't try to 'fix' a broken CSV manually—re-export from the source instead
When copying data from one system to another, ensure proper quoting is preserved
If you receive a CSV with unquoted commas, don't trust it—ask for a re-export
Know your import system's CSV standard—most follow RFC 4180 (standard CSV format)
For field names (headers), quote them if they contain commas or special characters
When creating a CSV template for clients, explain that commas will be auto-quoted
Use a CSV validator tool to check formatting before importing critical data
Remember: commas MUST be quoted in CSV, but not all commas in the file are delimiters
Never manually add quotes—let the export tool handle it automatically

Related CSV topics:

Prevent comma problems at the source

When you collect data through forms instead of spreadsheets, you avoid common CSV formatting problems. Structured data collection prevents commas, quotes, and line breaks from causing import headaches.

Learn more about Import Builder