CSV Row Count Is Wrong: Count Records with Quoted Newlines

Separate physical lines from CSV records

The first physical line is the header. The next two physical lines belong to T101 because its note opens a quoted field before Leave at desk and closes it after Call on arrival. T102 occupies the fourth physical line. Nothing is missing: the parser assembles those lines into the intended records.

Use distinct names in your checks. Record count includes the header unless stated otherwise. Data record count excludes a confirmed header. Physical line count describes the file layout and is useful for finding an error, but it is not automatically a count of tickets, customers, or sales.

Count with a CSV-aware script

Save the displayed sample as input.csv. The following Python 3 script assumes UTF-8, comma separation, and a header. It streams records, so it need not load the entire file into memory. The list of record-ending line numbers is included for this small diagnostic example; omit that list for a very large file.

import csv

with open("input.csv", encoding="utf-8-sig", newline="") as f:
    reader = csv.reader(f, delimiter=",", strict=True)
    header = next(reader, None)
    if header is None:
        raise ValueError("The file is empty")
    count = 0
    end_lines = []
    for fields in reader:
        count += 1
        end_lines.append(reader.line_num)
    print("data records:", count)
    print("data records end on lines:", end_lines)

# data records: 2
# data records end on lines: [3, 4]

Make counting conventions explicit

Before comparing two totals, write down what each total includes. An export screen may count business records while a text utility counts line endings. A worksheet may contain a header, a totals row, filters, or blank records. Agreement is meaningful only when those conventions match.

Distinguish valid notes from broken quoting

A complete quoted note is valid multiline data. A missing closing quote is different: it may cause an error or make a permissive reader absorb later lines. Inspect the reported error location and the earlier record where the quote began. Do not assume the last line mentioned in an error is where the defect started.

Power Query documents a quoted-line-break option in Csv.Document: QuoteStyle.Csv keeps those breaks inside the field, while QuoteStyle.None treats them as row endings. Check this setting if a valid note becomes multiple rows after import.

Related help: Understand quoted fields and embedded line breaks

Reconcile an export against the imported table

For this sample, verify T101 and T102 appear once each and inspect the complete T101 note. On a larger export, compare the parser count with the loaded table count and check a stable identifier column. Matching counts cannot detect a duplicated record paired with a missing record, so investigate identifiers when the data is important.

Keep a decision about blank records in the import notes. A completely blank physical line, a record containing only delimiters, and a quoted field containing an empty string are different inputs. Readers can handle blank lines differently. Do not silently discard them if empty records have meaning in the source system.

Keep multiline data unless the receiver requires a change

If a receiving system requires one physical line per record, confirm its accepted replacement for a note newline, such as a space or a literal escape sequence. Apply that transformation after parsing fields and document that the note text changed. Deleting every newline from the file would also erase record boundaries.

Column Harbor can inspect supported UTF-8 CSV and selected structural risks. Its results are not a business-record reconciliation or a newline conversion. The sample checks here were run in Python; no Excel UI execution is claimed.