CSV Imports: How Commas, Quotes, and Line Breaks Create Bad Data

A CSV import can finish with a green status and still put the wrong values in the wrong columns. The usual cause is not the destination system. It is a parser that treats a comma, quote, or line break inside a field as a record boundary.

That matters whenever a spreadsheet export feeds a CRM, email platform, donation system, catalog, or reporting workflow. One shifted column can turn a company name into an email address, split a note into a new record, or make later rows impossible to match. Reliable CSV imports start by treating the file as a structured format, not as plain text that can be split on commas.

Why a comma is not always a separator

CSV stands for comma-separated values, but the comma is only a delimiter when it appears outside a quoted field. A value such as Acme, Inc. must be enclosed in quotes if it is stored in one column. A note can contain a line break and still belong to one record when the entire field is quoted.

RFC 4180 documents the common CSV rules: records are separated by line breaks, fields are separated by commas, fields containing commas or line breaks should be quoted, and a quote inside a quoted field is escaped by another quote. It also notes that CSV has multiple real-world interpretations, which is why “the file opens in a spreadsheet” is not enough proof that another system will parse it the same way.

The failure usually begins before the import

CSV problems are often introduced during export. A source system may use a comma delimiter while the receiving system expects a semicolon. A spreadsheet may save a date in a locale-specific format. An export may omit a header, rename a column, or turn an empty value into a literal string such as N/A. A user may also open and resave the file, changing encoding or formatting without realizing it.

These differences are manageable when they are explicit. They become dangerous when the workflow assumes that every CSV file has the same dialect, column order, encoding, and meaning.

Before importing, document the contract for the file:

• Delimiter, quote character, and line-ending convention
• Whether the first row is a header
• Expected column names and order
• Character encoding, usually UTF-8 unless the source requires another choice
• How blank, null, zero, and “not applicable” values are represented
• Which field is the stable identifier for matching existing records

Use a CSV parser, not split()

A common implementation mistake is reading each line and calling split(','). That works only for the simplest files. It fails as soon as a quoted field contains a comma, a quote, or an embedded line break.

Use a parser that understands CSV quoting rules and configure it deliberately. For example, Python’s standard csv module expects files to be opened with newline='' so embedded newlines in quoted fields are interpreted correctly. The Python documentation also describes dialects, delimiters, quote characters, and field-size limits.

import csv

with open("contacts.csv", newline="", encoding="utf-8") as file:
    reader = csv.DictReader(file)
    for row in reader:
        email = row["email"].strip()
        # Validate and stage the row before writing to the destination.

The code is small, but the important design choice is the boundary around it. Parse first. Validate second. Write to the destination only after the row has passed the rules for identity, required fields, formats, and allowed values.

Stage rows before they reach production

A staging step gives the import somewhere to prove what it understood. Store the parsed row, the source row number, the import run ID, and a validation status. Keep rejected rows with a reason that an operator can act on, such as “expected 8 columns, received 9” or “email format invalid.”

For a CRM import, staging also lets you test matching before updating a live record. A stable customer or constituent ID should take priority over a name or email-only guess. For a product feed, stage the SKU, price, inventory value, category, and image reference before the catalog is updated. The same principle appears in CRM import staging layers and in product feed QA: make the data reviewable before the destination treats it as real.

Validate structure and meaning separately

A parser can produce a syntactically valid row that is still unusable. Test at least two layers:

• Structural validation: header presence, expected column count, delimiter, encoding, quote handling, duplicate headers, and record boundaries.
• Semantic validation: required values, email and date formats, stable IDs, allowed status values, numeric ranges, duplicate keys, and relationships between fields.

For example, a row with 8 fields may pass structural validation even when the “amount” field contains a currency symbol the destination cannot accept. A row with a valid email may still be matched to the wrong person if the identifier is missing or reused.

Build a small fixture file that breaks weak parsers

Do not test only with clean names and short values. Keep a fixture file that includes the cases your real workflow sees:

• A name containing a comma, such as a company name with a suffix
• A note containing a line break
• A value containing a double quote
• An empty field in the middle and at the end of a record
• Non-ASCII characters
• A duplicate stable ID
• A row with one extra or missing field
• A final record with and without a trailing line break

After parsing the fixture, assert the expected number of records, the expected number of fields per record, and the exact value of the difficult fields. Then run the same fixture through the staging and destination workflow. A parser test that never checks the write step can miss mapping, truncation, or encoding problems later in the process.

Make import results explainable

A successful import should produce more than a success message. Record the source file name or checksum, import run ID, start and end time, row counts, accepted rows, rejected rows, updated records, created records, and warnings. Keep enough evidence to answer three questions:

• What file did the system process?
• What did the parser and validator decide about each row?
• What changed in the destination?

This evidence is especially useful when an integration runs on a schedule. If the source sends 4,000 rows today and 3,200 tomorrow, the difference should be visible before someone assumes that the smaller file represents a real business change. If the import reports success but the destination count does not reconcile, the workflow needs an exception path rather than a green checkmark.

Keep sensitive data out of casual exports

CSV files are easy to copy, email, upload, and leave in download folders. Treat them as data assets with an owner and a retention rule. Remove columns the import does not need. Avoid placing full personalized URLs or unnecessary personal information in a file that will be handled by multiple people. Protect files in transit and at rest, and delete temporary copies according to the workflow’s retention policy.

Security and data quality often reinforce each other. A narrower export is easier to inspect, easier to validate, and less likely to expose information that does not belong in the destination.

A practical preflight checklist

Before a CSV import is allowed to update production, confirm:

• The source and destination agree on delimiter, quoting, encoding, headers, and line endings.
• The importer uses a real CSV parser and has tests for quoted commas, quotes, and embedded line breaks.
• Rows are staged and validated before writes occur.
• Stable identifiers and matching rules are explicit.
• Rejected rows are preserved with actionable reasons.
• Counts reconcile from source file to parsed rows to accepted and written records.
• A test file covers both structural edge cases and real field values.
• Import logs identify the file, run, outcome, and exceptions.
• Temporary files and sensitive columns follow an agreed retention and access policy.

Reliable imports are designed, not assumed

CSV remains useful because it is portable, inspectable, and supported almost everywhere. Its weakness is that the format is simple enough to be handled casually and structured enough to fail when assumptions differ.

The remedy is a small amount of discipline at every boundary: define the file contract, parse with a format-aware library, stage and validate rows, reconcile the outcome, and keep a useful audit trail. When a CSV workflow crosses a website, CRM, email platform, catalog, or reporting system, those checks turn a fragile file exchange into an integration that can be tested and supported.

DigitalWerks can review an import workflow, map the source and destination fields, test difficult CSV cases, and add validation and reconciliation steps before bad rows reach production.