Preserve Multiline CSV Fields in Python Automation

When an exported support ticket contains a note spanning two lines, it can still be one CSV record. Python automation should open CSV text files with newline='' and let csv.reader or csv.writer handle record boundaries and quoting. Counting physical lines or splitting on commas is not a reliable way to count or parse those records.

This tutorial focuses on preserving field contents during a read/write round trip. It does not import anything into a database or change an existing export. Use a small, non-sensitive copy when investigating a real integration.

Understand why a note can span several lines

CSV fields may contain commas, quotation marks and newline characters. A properly quoted multiline note occupies more than one physical line, but the CSV parser assembles it into a single field in a single record. Reading the file with a loop and treating each line as one ticket breaks that relationship.

The official Python csv documentation recommends opening file objects with newline='' because the module performs its own newline handling. It also warns that omitting this setting can mishandle newlines within quoted fields and introduce an extra carriage return on platforms using carriage-return/line-feed output endings.

Encoding is a separate choice. The following example explicitly uses UTF-8; changing the newline setting does not identify an unknown encoding, fix a delimiter mismatch or repair a malformed export.

Run a disposable round-trip example

Save this complete example as a Python script and run it with Python 3. It creates files only inside an automatically cleaned temporary directory. The note deliberately contains a newline and a comma, while the second note contains quotation marks. Neither field needs manual escaping before being passed to the writer.

import csv
from pathlib import Path
from tempfile import TemporaryDirectory

records = [
    ['ticket_id', 'note'],
    ['T-101', 'First observation\nFollow-up, still the same ticket'],
    ['T-102', 'Technician said "check the cable"'],
]

with TemporaryDirectory() as folder:
    source = Path(folder) / 'tickets.csv'
    exported = Path(folder) / 'tickets-copy.csv'

    with source.open('w', encoding='utf-8', newline='') as handle:
        csv.writer(handle).writerows(records)

    with source.open('r', encoding='utf-8', newline='') as handle:
        loaded = list(csv.reader(handle, strict=True))

    if loaded != records:
        raise ValueError('Initial CSV read changed the records')

    with exported.open('w', encoding='utf-8', newline='') as handle:
        csv.writer(handle).writerows(loaded)

    with exported.open('r', encoding='utf-8', newline='') as handle:
        checked = list(csv.reader(handle, strict=True))

    if checked != records:
        raise ValueError('Exported CSV did not preserve the records')
    print('Round trip preserved', len(checked) - 1, 'data records')

The comparisons check the actual parsed field strings, including the embedded newline. They do not require the two files to have identical bytes. Equivalent CSV can use different valid quoting or record terminators, so semantic preservation and byte-for-byte identity are different acceptance criteria.

Adapt the check to an actual export

1. Keep the original file unchanged and identify its documented encoding, delimiter and quoting convention.
2. Read the copy using the agreed dialect. For a semicolon-delimited feed, explicitly pass the correct delimiter rather than replacing punctuation inside the file.
3. Verify the expected header, field count and required values before sending records to another system. See the related guide to validating CSV headers before an inventory import.
4. Write to a separate preview path, reopen it with the same intended contract and compare parsed records or approved transformed values.
5. Confirm the receiving application supports quoted multiline fields before replacing its production feed.

For large exports, use a streaming validation design rather than loading the entire dataset into lists. Define how failures prevent partial downstream imports. The small example intentionally trades scalability for a clear, inspectable comparison.

Troubleshoot without destroying meaningful whitespace

Blank lines appear after export: check both file openings for the explicit newline setting, then inspect the actual bytes or a text editor that displays line endings. Do not delete every empty line automatically; an empty line inside a quoted note can be meaningful content.

The parser reports a later line than the ticket number: Python documents that reader.line_num counts source lines consumed, not logical records returned. Keep separate record counters when reporting validation failures.

Strict mode succeeds but the import is wrong: strict CSV parsing is not business validation. It does not prove that required headers, column counts, dates or identifiers satisfy your receiving system. Avoid logging sensitive note contents while collecting diagnostics.

FAQ and final checks

Can I use splitlines followed by split on commas? Not for a general CSV feed containing quoting or multiline fields. Use the CSV parser on the opened file instead.

Will this make spreadsheet imports safe? No. Preserving CSV structure does not neutralise spreadsheet formulas or validate untrusted data. Treat spreadsheet interpretation as a separate security requirement.

Before deployment, test multiline notes, embedded quotes, empty values, the documented encoding and a deliberate malformed sample. Preserve the original export so rollback means returning to the unchanged source rather than guessing which newline replacements were applied.

Educational use only. Test carefully before production. The site owner is not responsible for misuse, damage, data loss or production issues.