Testing a CSV Export for Truncated Last Lines in Python
A cut-off CSV loaded in pandas without a warning, last row 200000,Ana,Smith,...,2. Check the final byte, the last record's width, quotes, row count and gzip.
The load succeeded. Pandas read 193,708 rows, there were no warnings, and the row count matched what the sender had written in their email. A week later someone noticed that customer 200000 had no status, no country and a signup date of 2. The file had been cut off in the middle of its last row when the transfer dropped, and every check that had been made was a check that a cut-off file passes. This article builds the checks that do not, and measures which kind of cut each one catches.
How a file ends up cut off
An export is written by a process, and any of several things can end that process early: the connection to the SFTP server drops, a disk fills up, a scheduler kills a job that ran too long, a user cancels a download. The file system does not mark the result as incomplete. It is a file with some number of bytes in it, and the last byte could be anywhere. That gives four situations that need different checks:
Cut inside a row, in the middle of a field. The last record is short.
Cut inside a quoted field, so a quote is never closed.
Cut between rows, so the file is perfectly well-formed and has fewer rows.
Cut inside a compressed stream, where the damage is in the container.
There is also a fifth, which is not damage: a complete file that just lacks its final newline. RFC 4180 says the last record may or may not end with a line break, so this one is legal and a check has to cope with it.
Look at the last byte
The cheapest check is one byte:
for f in customers.csv customers-truncated.csv customers-nonl.csv customers-short.csv; do printf '%-26s' $f; tail -c 1 work/val/$f | xxd -p; donecustomers.csv 0a
customers-truncated.csv 32
customers-nonl.csv 65
customers-short.csv 0aThe intact file ends in 0a, a newline. So does the file cut between rows. The file cut 37 bytes short ends in 32, the character 2. And the complete file with only its final newline removed ends in 65, an e (from active). Two things follow. A last byte that is not a newline is a reason to look closer, but not proof of damage: it flagged the complete file too. And a newline as the last byte is no proof of health, since the file cut between rows has one.
To tell damage from a missing terminator, look at the last record.
Look at the last record
tail -n 2 work/val/customers-truncated.csv
echo ---
tail -n 1 work/val/customers-truncated.csv | awk -F, '{print NF " fields"}'
head -n 1 work/val/customers.csv | awk -F, '{print NF " columns in the header"}'199999,Ana,Kiev,[email protected],+18763085110,,2025-08-27,-150.73,USD,2133,US,inactive
200000,Ana,Smith,[email protected],+15263524438,,2---
7 fields
12 columns in the headerThe last two lines of the cut file are customer 199999, complete, and 200000,Ana,Smith,[email protected],+15263524438,,2, which stops in the middle of the signup_date, the date 2025-... reduced to 2. A field count on that line gives 7 where the header gives 12. A last record narrower than the header is the signature of a cut inside a row. (This uses awk -F,, which is fine here because the customer file has no quoted commas; the Python version below uses a real parser.)
What the readers do with it
This is the part that surprised me. The same cut file, read three ways:
python - <<'PY'
import pandas as pd
df = pd.read_csv('work/val/customers-truncated.csv', dtype=str, keep_default_na=False)
print('pandas rows:', len(df))
print(df.tail(1).T.to_string())
PY
duckdb -box -c "SELECT count(*) AS rows, count(*) FILTER (WHERE status IS NULL OR status = '') AS no_status FROM read_csv('work/val/customers-truncated.csv', all_varchar=true)" 2>&1 | head -n 8pandas rows: 193708
193707
id 200000
first_name Ana
last_name Smith
email [email protected]
phone +15263524438
phone_unparsed
signup_date 2
balance
balance_currency
units
country
status
Invalid Input Error: CSV Error on Line: 193709
Original Line: 200000,Ana,Smith,[email protected],+15263524438,,2
Expected Number of Columns: 12 Found: 7
Possible fixes:
* Disable the parser's strict mode (strict_mode=false) to allow reading rows that do not comply with the CSV standard.
* Enable null padding (null_padding=true) to replace missing values with NULL
* Enable ignore errors (ignore_errors=true) to skip this rowpandas read 193,708 rows without a word. The last row is there, with signup_date set to 2 and the five fields after it empty: pandas pads a short row with missing values and carries on, which is the same lenient behavior I noted in the counting article. It is also the right number of rows, so any check on the count passes. The record is wrong in a way that fits in every column's type, because 2 is a valid text value, and a loader that expects a date column would only notice at the cast.
DuckDB refused it: Invalid Input Error: CSV Error on Line: 193709, with the original line, Expected Number of Columns: 12 Found: 7, and a list of options. The options include null_padding=true and ignore_errors=true. Either would make the error go away and produce what pandas produced, so treat them with suspicion in a validation: the default error is the finding.
The lesson is not that pandas is bad. The lesson is that the reader's willingness to load a file is not evidence the file is complete. Truncation has to be tested for explicitly.
A check that does all of it
This script reads the file, checks the end, parses it strictly and compares the count:
"""Is the end of this CSV the end of the data? Checks the last bytes, the last record and the row count."""
import csv, gzip, io, sys, zlib
def check(path, expect_rows=None):
findings = []
opener = gzip.open if path.endswith(".gz") else open
try:
with opener(path, "rb") as f:
raw = f.read()
except (EOFError, zlib.error, OSError) as e:
return [f"cannot read the whole file: {type(e).__name__}: {e}"]
if not raw.endswith(b"\n"):
findings.append(f"no line terminator after the last record; last bytes {raw[-12:]!r}")
text = raw.decode("utf-8")
try:
rows = list(csv.reader(io.StringIO(text, newline=""), strict=True))
except csv.Error as e:
return findings + [f"csv.Error: {e}"]
header, body = rows[0], rows[1:]
if body and len(body[-1]) != len(header):
findings.append(f"last record has {len(body[-1])} fields, header has {len(header)}: {body[-1][:3]}...")
if expect_rows is not None and len(body) != expect_rows:
findings.append(f"{len(body)} records, expected {expect_rows} ({len(body) - expect_rows:+d})")
return findings or ["end of file looks complete"]
if __name__ == "__main__":
n = int(sys.argv[2]) if len(sys.argv) > 2 else None
for line in check(sys.argv[1], n):
print(" ", line)It reads bytes, so that it can see the last one, and handles a .gz by decompressing it with Python's gzip module, where a cut stream raises an error instead of returning a partial result. It then parses with csv.reader(..., strict=True), which raises on malformed input where the default mode returns what it has, compares the last record's width with the header's, and compares the record count with the number the sender gave. Run on four files, with 193,708 as the expected count:
for f in customers.csv customers-truncated.csv customers-nonl.csv customers-short.csv; do echo "$f"; python lab/val/tail_check.py work/val/$f 193708; donecustomers.csv
end of file looks complete
customers-truncated.csv
no line terminator after the last record; last bytes b'263524438,,2'
last record has 7 fields, header has 12: ['200000', 'Ana', 'Smith']...
customers-nonl.csv
no line terminator after the last record; last bytes b'53,US,active'
customers-short.csv
193703 records, expected 193708 (-5)The intact file:
end of file looks complete.The cut file: no terminator, with the last twelve bytes shown, and a last record with 7 fields, the first three printed. Two findings that agree. The record count was 193,708, equal to the expectation, so a count-only check would have passed it.
The file missing only its final newline: one finding, the missing terminator, and no width complaint. Because the last record is complete, I treat this as a warning to mention to the sender, not a reason to reject.
The file cut between records: no terminator problem and no width problem. The only finding is
193703 records, expected 193708 (-5).
That last one is the case worth remembering. A file cut at a row boundary is a perfectly valid CSV. No test of its structure can tell it from a file that was meant to be that size. Only information from outside, the sender's count, a checksum or a trailer, can. This is why the validation guide asks for a manifest.
Cut inside a quoted field
When a field is quoted, a cut can leave an open quote. A small file shows it, with the last bytes removed from a note:
cat work/val/quoted-cut.csv; echo '<end of file>'
python lab/val/tail_check.py work/val/quoted-cut.csv
python - <<'PY'
import pandas as pd
try:
pd.read_csv('work/val/quoted-cut.csv')
except Exception as e:
print('pandas:', type(e).__name__, '-', str(e).splitlines()[0])
PY
python - <<'PY'
import csv
print('csv module, default settings:', list(csv.reader(open('work/val/quoted-cut.csv', newline=''))))
PYid,name,note
1,Ana,"likes tea, no sugar"
2,Bo,"calls on <end of file>
no line terminator after the last record; last bytes b'o,"calls on '
csv.Error: unexpected end of data
pandas: ParserError - Error tokenizing data. C error: EOF inside string starting at row 2
csv module, default settings: [['id', 'name', 'note'], ['1', 'Ana', 'likes tea, no sugar'], ['2', 'Bo', 'calls on ']]The file ends in 2,Bo,"calls on with no closing quote and no newline. The three readers disagree:
pandas raised
ParserError ... EOF inside string starting at row 2. Good: loud.**The
csvmodule with default settings** returned['2', 'Bo', 'calls on '], quietly. It reached the end of the file inside a quoted field and handed back what it had collected. This is the worst case, since there is no row-width signal at all: a one-field value is exactly as wide as it should be.**
strict=True** raisedcsv.Error: unexpected end of data, which is the behavior you want from a check.
The script reports both findings for that file, the missing terminator and the strict-mode error. If you validate with the csv module, turn on strict mode.
Cut inside a compressed file
Exports are often gzipped, and a cut stream is a different failure:
gzip -t work/val/customers.csv.gz; echo "gzip -t, good file: exit $?"
gzip -t work/val/customers-cut.csv.gz; echo "gzip -t, cut file: exit $?"
zcat work/val/customers-cut.csv.gz | wc -l; echo "pipeline exit (no pipefail): ${PIPESTATUS[0]} ${PIPESTATUS[1]}"
python lab/val/tail_check.py work/val/customers-cut.csv.gzgzip -t, good file: exit 0
gzip: work/val/customers-cut.csv.gz: unexpected end of file
gzip -t, cut file: exit 1
gzip: work/val/customers-cut.csv.gz: unexpected end of file
96942
pipeline exit (no pipefail): 1 0
cannot read the whole file: EOFError: Compressed file ended before the end-of-stream marker was reachedgzip -t on the intact archive printed nothing and exited 0. On the archive cut at half its length it printed unexpected end of file and exited 1. That is the check to run before anything else on a .gz. The trap is in the second experiment. zcat file | wc -l printed 96,942, about half the lines, and reported unexpected end of file on the way, but the status of a pipeline is the status of its last command. wc succeeded, so the pipeline's status was 0 and a script that does not check would continue on half a file. In bash, use set -o pipefail (described in the Bash manual) so that any failing stage fails the pipeline, and check PIPESTATUS if you need to know which. The Python script above handled it with an EOFError and said so.
What the sender can do
The best fix is on the sending side, and most of it is free:
Write to a temporary name and rename on success. A file only appears under its real name when it is complete, and the consumer never sees a partial one. A rename within a file system is atomic.
Publish a checksum and a row count next to the file, after the file. A missing manifest means the file is not finished.
End with a trailer that the consumer can recognize, or use a format that has an end marker built in.
If you receive files from someone else and cannot ask, record the byte size and the checksum as soon as the file arrives, and compare each delivery's size with the last one. A file 40% smaller than usual is worth a look before you parse it.
How to tell it worked
Create each kind of damage on a good file and make sure your check names it. Cut at ten random byte offsets. Every one should fail, with one exception you can predict: a cut that lands exactly on a record boundary, which the structure checks cannot see and the count must. If your check passes all of them, it is checking the wrong thing. Then run it on the good file and on one with a final newline removed, to confirm it does not reject files that are only unusual.
A complete file that is still short
All of these checks establish that the file ends where the sender meant it to. None establishes that the sender meant it to end there. An export can finish cleanly after a query that timed out, a filter that was off by a day or a job that ran before the data was loaded, and produce a perfect, complete, short file. The cure is the same as for the cut between rows: compare with something independent. Last week's size, the source system's count, the highest id or the latest timestamp in the file. For anything that matters, ask the sender to include a count in the delivery and make it a contract term, so that a mismatch is a finding and not a surprise.
