A CRM Export With Multi Line Notes and How to Parse It

CRM exports are where multi-line fields live. A salesperson types a note with a few lines, the system exports it faithfully inside quotes, and every tool downstream that thinks in lines now disagrees about how long the file is. wc -l says 7, the importer says 3 customers, a script that processes line by line sees notes broken in half and grep returns half a record. Nothing is wrong with the file: quoted line breaks are valid CSV. What is wrong is the assumption that a line is a record. This article shows how to count and parse such a file correctly and how to flatten it when a tool downstream insists on one line per row. It is one of the problems in worked CSV problems and the patterns behind them, and it relies on the quoting rules in how CSV quoting works.

The file, and the two counts

Three customers. The first note runs to three lines, the third has a quoted word inside it:

id,company,owner,note
1,Acme Ltd,ada,"Called on Monday.
Wants a quote for 40 seats.
Follow up Friday."
2,Beta Corp,bob,No answer
3,"Cy Foods, Inc",cy,"Said ""maybe"" twice
asked for pricing"

Count it two ways:

wc -l crm.csv

       7 crm.csv

import csv
rows = list(csv.reader(open("crm.csv", newline="")))
print("records:", len(rows) - 1)

records: 3

Seven lines, three records, one header. The lines break down as the header (1 line), customer 1 with its three-line note (3 lines), customer 2 (1 line) and customer 3 with a two-line note (2 lines). Any process that counts, samples or splits by line will be wrong by exactly the number of embedded breaks. That includes head -n 100, which can cut a record in half at the end of its output, and split -l, which can cut one in half between two files (splitting a large CSV into smaller files shows how to cut on record boundaries).

Parse by records

The CSV parser follows the quotes, so a line break inside quotes is data. In Python, open the file with newline="" so the module receives the line endings unchanged and can tell a break inside a field from one that ends a record:

import csv
with open("crm.csv", newline="", encoding="utf-8") as f:
    for row in csv.DictReader(f):
        print(row["id"], repr(row["note"]))

1 'Called on Monday.\nWants a quote for 40 seats.\nFollow up Friday.'
2 'No answer'
3 'Said "maybe" twice\nasked for pricing'

Each note arrived as one string, with \n where the breaks were. The doubled quotes in note 3 became single quotes: ""maybe"" inside a quoted field is the escape for a literal quote, so the value is Said "maybe" twice. Leaving newline="" off is the classic mistake: Python's text mode translates line endings on read, and on a file with Windows line endings you may get doubled or lost breaks inside fields.

DuckDB does the same without any setting, and its read_csv detects the quoting:

SELECT id, company, replace(note, chr(10), ' | ') AS note,
       length(note) - length(replace(note, chr(10), '')) AS breaks
FROM read_csv('crm.csv') ORDER BY id;

id  company         note                                                                  breaks
1   Acme Ltd        Called on Monday. | Wants a quote for 40 seats. | Follow up Friday.   2
2   Beta Corp       No answer                                                             0
3   Cy Foods, Inc   Said "maybe" twice | asked for pricing                                1

Three rows, with the breaks counted per note (2, 0 and 1) and shown as | for display. The count of breaks per row also reconciles the numbers from before: 3 breaks in total, so the physical lines are 4 (the header plus three single-line records) plus 3 breaks, which is 7.

Flatten for tools that want one line per row

Some destinations cannot take line breaks inside a value: a log shipper, a tool that reads the CSV with split('\n'), a fixed-format feed. Do not try to fix those by regex on the raw file, since a regex cannot know whether a line break is inside quotes. Parse first, then replace in the parsed value:

import csv, sys
with open("crm.csv", newline="", encoding="utf-8") as f, open("crm-flat.csv", "w", newline="", encoding="utf-8") as out:
    reader, writer = csv.reader(f), csv.writer(out)
    for row in reader:
        writer.writerow([c.replace("\r\n", " | ").replace("\n", " | ").replace("\r", " | ") for c in row])

The separator | keeps the structure visible, and a space would glue sentences together (Monday.Wants). Replace \r\n first, then the lone \n and \r, so that a Windows break is not turned into two separators. If the exact text matters later, keep the original column and add the flattened copy next to it, rather than replacing it. And tell the people who read the flat version that the pipes are line breaks, because | may also occur in the original text; pick a separator that does not.

In the other direction, a tool that must receive the breaks, such as a text area, can have them back by reversing the replacement, which only works if the separator is unambiguous.

Tools that need care

Spreadsheets handle the breaks, with caveats. Excel shows a multi-line cell as one cell with wrapped text if the wrap setting is on, and on a double-click open it may display only the first line. Sorting or filtering is unaffected, and saving writes them back inside quotes (Excel CSV versus RFC CSV differences). grep and awk see lines, so a search for "Friday" prints the third line of note 1 and not the customer it belongs to; use csvgrep or qsv search, which parse the CSV and print the whole record (working with CSV files in the shell). Database loaders handle quoted breaks if told the format is CSV: PostgreSQL's COPY ... CSV does, and its text format escapes the break as \n instead (PostgreSQL CSV versus text format in COPY).

Parallel readers deserve a warning. A loader that splits a large file into byte ranges and parses each chunk independently can start a chunk in the middle of a quoted field and read the rest as a different structure. Systems that do this either need allow_quoted_newlines style settings and give up parallelism or must verify the split, so check the documentation before loading a 20 GB file of notes with a parallel loader.

When the quotes are not trustworthy

All of this assumes the file's quoting is correct, and exports from some CRM systems are not. Check three things. Every record should have the same number of fields when parsed, which the field-count test finds: a note with a stray unmatched quote swallows the following records, and the count of records comes out low. Look at the record count against what the CRM says it exported. And scan the notes for signs of a broken field: a note that starts with a customer ID and a comma, where the parser merged two records. If the quotes are broken, the problem is the unescaped-quote case, and the repair is a lenient parser setting, shown in a log export with unescaped quotes, applied with a count to prove it.

Finally, consider whether the notes should be in the file at all. Free-text notes are the most sensitive and the messiest column in a CRM, they hold personal data, and an export for analysis rarely needs them (stripping personal data from a CSV before sharing it). If the analysis needs only a count of notes or their length, compute that at the source and leave the text behind. The DuckDB CSV options are documented in the CSV overview and the newline argument in Python's csv documentation.