Reading and Writing CSV Files in Python the Right Way

Python is where a lot of CSV files are first read, and where a lot of them are first damaged. The tools are good. The standard library module is small and exact, pandas is fast and convenient, and newer engines like polars and DuckDB are faster still. The damage comes from defaults that are right for the happy path: a file object opened without newline="", an encoding left to the machine, a column of ZIP codes inferred as integers, a warning that someone silenced. This guide is the one I would want to hand a colleague before they write their first import. It covers how to choose a tool, the five habits that prevent most data loss, reading and writing with the csv module, pandas for analysis, a typed loader that rejects bad rows instead of dropping them, large files, and a table that maps each symptom to its cause and the article that fixes it.

Every number below was measured on one machine, and every output is the output I got. The related articles go deeper on each subtopic, and are linked where they apply.

Choosing the tool

Five tools read a CSV from Python without any extra system, and they differ in what they cost and what they give you. I summed one column of the 2,000,000 row orders file with each, in a fresh process, and recorded the time and the peak resident memory of the process:

for m in reader dictreader pandas pandas-cols pandas-pyarrow polars duckdb; do python /batches/csv-0001-0200/lab/py/bench_py.py $m; done

reader          total=160174646.36 time= 1.52s peak_rss=    13MB
dictreader      total=160174646.36 time= 3.10s peak_rss=    13MB
pandas          total=160174646.36 time= 0.74s peak_rss=   490MB
pandas-cols     total=160174646.36 time= 0.78s peak_rss=   130MB
pandas-pyarrow  total=160174646.36 time= 1.04s peak_rss=   522MB
polars          total=160174646.36 time= 0.47s peak_rss=   429MB
duckdb          total=160174646.36 time= 0.44s peak_rss=   203MB

All seven got 160,174,646.36. The versions are in the callout above:

python -c "import pandas, polars, duckdb, pyarrow; print('pandas', pandas.__version__, '| polars', polars.__version__, '| duckdb', duckdb.__version__, '| pyarrow', pyarrow.__version__)"

pandas 3.1.0 | polars 2.1.0 | duckdb 2.0.0 | pyarrow 26.0.0

The pattern is the one you would guess, with two details that matter. The standard library reader is the slowest and by a long way the smallest: 13 MB for a file that makes 490 MB of DataFrame. Because it is a stream, its memory does not grow with the file, so it is the only one on this list that would read a 50 GB file on a laptop, slowly. And selecting one column in pandas saved a great deal of memory (490 MB to 130 MB) and little or no time, because the parser still scans every byte: 0.74 s against 0.78 s here, and 0.54 s against 0.36 s in a separate run for the column selection article, which is a measure of how noisy a sub-second timing is.

My rule of thumb follows from the table:

• The csv module when you must stream, when you need exact control of quoting and line endings, when the code runs where you cannot install packages, or when the job is "read, validate, write" and the data never needs to be a table.

• pandas when you will analyze, reshape or join the data and it fits in memory.

• polars or DuckDB when it does not fit, when speed matters, or when the question is naturally SQL. The DuckDB guide covers the second.

None of these is the "right" one. The file, the memory and the job pick it.

Five habits that prevent most CSV bugs

These are the ones that cost me the most before I made them automatic. Each one is a one-line change, and each fails quietly when you leave it out.

**1. Open with newline="".** The csv module does its own line ending handling, and Python's text mode does its own, and they must not both act. On reading, without it \r\n inside a quoted field silently becomes \n. On writing, on Windows, every record gets \r\r\n and many readers show a blank row after each line. On Linux you will never see the second one, which is why it ships. The reproduction is in the Windows blank line article, and the reading half in the csv module article.

2. State the encoding. open() without encoding uses the machine's locale, so the same script reads a file correctly on your laptop and wrongly on a server. Write encoding="utf-8" for files you produce. For files you receive from people, encoding="utf-8-sig" is the safer default: it strips a byte order mark if one is present and changes nothing if it is not. A BOM left on turns the first column name into 'id' and every row["id"] into a KeyError, as the DictReader article shows. If decoding fails, the answer is to find the real encoding, not to try codecs until one stops raising; the detection article has the method.

3. Treat identifiers as text. ZIP codes, account numbers, SKUs, phone numbers and anything else with leading zeros or more digits than a double can hold. The csv module returns strings and leaves this to you. pandas infers numbers and drops the zeros: on the orders file, 190,451 of 2,000,000 ZIP codes lost one or two leading zeros, 9.5% of the rows, with no message (why). Declare dtype for those columns, or dtype=str for the whole file and convert the numeric ones.

4. Never silence a warning you have not understood. The pandas DtypeWarning means one column holds two Python types. In my test it was 524,288 integers and 175,712 strings, and the same code 5521 was found 49 times as an integer and 16,670 times as text (the article). warnings.filterwarnings("ignore") is the one choice that removes the only sign of the defect.

5. Count before and after. The number of records you read should equal the number the file has, which you can get from a second tool. The number you write should equal the number you meant to write. A pipeline that checks counts at both ends finds most of what the other four habits would have prevented.

Reading with the standard library

The reader returns a list of strings per record. It handles quoted commas, doubled quotes and line breaks inside fields:

import csv

with open("people.csv", newline="", encoding="utf-8-sig") as f:
    reader = csv.reader(f)
    header = next(reader)
    for row in reader:
        ...

The detail beyond the basics is the dialect. delimiter, quotechar, escapechar, doublequote, skipinitialspace and quoting describe how the file is written, and they must match the producer. A semicolon file needs delimiter=";", a file that quotes with apostrophes needs quotechar="'", and a file written by hand with a space after each comma needs skipinitialspace=True. The surprise is strict: by default the reader accepts a,"b"c,d and returns bc, a merged field, with no error; strict=True raises ',' expected after '"'. For data that goes into a system of record, I run strict and let the error stop the job. The csv module article shows each of these on a sample.

DictReader reads the header and gives each record as a dictionary, so row["amount"] still works when columns are reordered. Its traps are in the header: a BOM, duplicate names (the second column silently wins), spaces, and the way it fills short rows with None and long rows with a list under the key None. It also costs: about 1.3 times the memory of lists for the same rows, and about twice the time (3.10 s against 1.52 s above). See the DictReader article.

Records of different widths are the most common structural problem. Comparing each record's length with the header's and keeping the line number tells you where the damage is, and the common causes, an unquoted delimiter, a trailing delimiter, a truncated row and a record broken across lines, each need a different repair (the article). A related failure is the error field larger than field limit (131072), which is either a very large cell or a quote that was never closed, and raising the limit hides the second (the article).

Writing with the standard library

Writing is the same module in reverse, and the defaults are sensible:

import csv

with open("out.csv", "w", newline="", encoding="utf-8") as f:
    writer = csv.writer(f)
    writer.writerow(["id", "name", "note"])
    writer.writerows(rows)

Four facts about the output are worth knowing before you ship a file. Records end in \r\n by default, on every platform; pass lineterminator="\n" for LF. Fields are quoted only when they need to be: when they contain the delimiter, a quote or a line break. None is written as an empty field, and every other value goes through str(): a float prints as 0.30000000000000004, a datetime as 2025-03-14 10:15:00 with a space, a boolean as True, and bytes as the text b'bytes'. The converter belongs in your code, not the writer. And DictWriter refuses a dictionary with a key that is not in fieldnames, which is a guard worth keeping.

For files that Excel on Windows will open, UTF-8 needs a byte order mark, which encoding="utf-8-sig" writes; that part is widely reported behavior and I did not test Excel itself. The details are in the writing article.

pandas for analysis

For analysis, pd.read_csv is the shortest path to a table, and the options that matter are the ones that name what is unusual about the file. A report with a title line, a comment, semicolons and European numbers loads with skiprows, comment, sep, decimal and thousands. The set of strings treated as missing is a specific list: NULL, NA, N/A, n/a, nan, None and a few more are missing, while Null, na, none and a dash are not, so print isna() rather than assume. keep_default_na=False is the right setting for a code column in which NA is a real value. Rows with too many fields raise a ParserError, on_bad_lines="skip" drops them, and a callable with engine="python" can collect them. The whole set is in the read_csv article.

Dates are text until you ask. parse_dates with date_format is the pattern, and the traps are all about ambiguity: a column with several formats stays text under parse_dates with no warning, and to_datetime(format="mixed") guessed 03/04/2025 month first and 13/04/2025 day first inside the same column. Declare one format per column and let the first failure tell you something about the file (the article).

For memory, usecols and dtype are the levers. Two columns of the 2,000,000 row file cost 36.0 MB as a frame, and 10.0 MB with category and float32. That float32 is a trap for money: its total was 160,174,656.00 against the true 160,174,646.36. chunksize with a per chunk aggregate gave the same answers at 128 MB peak when the file does not fit.

Finding out what you were handed

When a file arrives from outside, look at its bytes before choosing an encoding. Three facts decide most cases: does it start with a byte order mark, is it valid UTF-8, and what does a detector guess if it is not. A short script that prints all three for several files:

import codecs, sys
from charset_normalizer import from_bytes

for path in sys.argv[1:]:
    raw = open(path, "rb").read()
    bom = "utf-8-sig BOM" if raw.startswith(codecs.BOM_UTF8) else "utf-16 BOM" if raw[:2] in (codecs.BOM_UTF16_LE, codecs.BOM_UTF16_BE) else "no BOM"
    try:
        raw.decode("utf-8")
        valid = "valid UTF-8"
    except UnicodeDecodeError as e:
        valid = f"not UTF-8 (byte 0x{raw[e.start]:02x} at {e.start})"
    best = from_bytes(raw).best()
    guess = f"{best.encoding}" if best else "none"
    print(f"{path.rsplit('/', 1)[-1]:18s} {bom:14s} {valid:34s} guess: {guess}")

python /batches/csv-0001-0200/lab/py/sniff_file.py utf8.csv utf8-bom.csv utf16.csv cp1252.csv latin1.csv shift_jis.csv

utf8.csv           no BOM         valid UTF-8                        guess: utf_8
utf8-bom.csv       utf-8-sig BOM  valid UTF-8                        guess: utf_8
utf16.csv          utf-16 BOM     not UTF-8 (byte 0xff at 0)         guess: utf_16
cp1252.csv         no BOM         not UTF-8 (byte 0xe9 at 23)        guess: cp1250
latin1.csv         no BOM         not UTF-8 (byte 0xe9 at 18)        guess: cp1250
shift_jis.csv      no BOM         not UTF-8 (byte 0x93 at 15)        guess: cp932

utf8.csv has no BOM, is valid UTF-8 and the detector agrees. utf8-bom.csv starts with the three byte signature and is otherwise the same, so utf-8-sig is the codec that reads it cleanly. utf16.csv has a UTF-16 mark, and 0xff as its first byte, so it is not UTF-8. The three files that are not Unicode at all are the instructive ones. The cp1252 and latin1 samples both failed UTF-8 at an 0xe9 byte, which is é in those code pages, and the detector guessed cp1250 for both, a Central European code page that agrees with them on this byte and disagrees on others. The Shift JIS sample was guessed as cp932, the Microsoft superset of it, which is a safe answer. The lesson is that a detector is a ranking of candidates by how plausible the text looks, and a short sample from a Western European code page can have several equally plausible answers. Treat its output as a hypothesis, read a few lines decoded with it, and check the characters that matter against what you know about the source. The detection article works through that process, and the Latin-1 and Windows article covers the conversion.

Guessing the dialect

If the delimiter is not known in advance, csv.Sniffer will guess from a sample, and it is worth using with the candidates restricted:

import csv

base = "/batches/csv-0001-0200/lab/data/"
for name in ["d-comma.csv", "d-semi.csv", "d-tab.tsv", "d-pipe.txt", "d-single.csv"]:
    with open(base + name, newline="", encoding="utf-8") as f:
        sample = f.read(4096)
    try:
        d = csv.Sniffer().sniff(sample, delimiters=",;\t|")
        hh = csv.Sniffer().has_header(sample)
        print(f"{name:13s} delimiter={d.delimiter!r:6} quotechar={d.quotechar!r} has_header={hh}")
    except csv.Error as e:
        print(f"{name:13s} Sniffer failed: {e}")

python /batches/csv-0001-0200/lab/py/sniffer.py

d-comma.csv   delimiter=','    quotechar='"' has_header=True
d-semi.csv    delimiter=';'    quotechar='"' has_header=True
d-tab.tsv     delimiter='\t'   quotechar='"' has_header=True
d-pipe.txt    delimiter='|'    quotechar='"' has_header=True
d-single.csv  Sniffer failed: Could not determine delimiter

The comma, semicolon, tab and pipe files were each identified, with has_header true for all four. The single column file, which has no delimiter to find, raised Sniffer failed: Could not determine delimiter, and that is the right outcome, since there was nothing to find. Pass delimiters=",;\t|" as I did, so that a file with a stray colon or space is not read as colon separated; the sniffer is a heuristic over the sample you give it, and a sample that starts with a title line or ends mid-record can mislead it. I use it to choose among known candidates, never to accept whatever it returns, and I print the choice in the log so that a surprise is visible. The sniffer in DuckDB works differently and is covered in its own article, and the choice of delimiter from the producer's side is in comma, semicolon, tab and pipe.

Read, filter, write in one pass

The case the standard library handles best is a transform that never holds more than a row. This script reads the orders file, keeps the refunded orders and writes them to a new file with the same header:

import csv, resource, time

src = "/batches/csv-0001-0200/work/orders-2m.csv"
t = time.perf_counter()
kept = 0
with open(src, newline="", encoding="utf-8") as fin, open("/tmp/refunded.csv", "w", newline="", encoding="utf-8") as fout:
    r, w = csv.reader(fin), csv.writer(fout)
    header = next(r)
    w.writerow(header)
    status = header.index("status")
    for row in r:
        if row[status] == "refunded":
            w.writerow(row)
            kept += 1
dt = time.perf_counter() - t
print(f"kept {kept} refunded rows in {dt:.2f}s, peak {resource.getrusage(resource.RUSAGE_SELF).ru_maxrss/1024:.0f} MB")

python /batches/csv-0001-0200/lab/py/filter_stream.py && duckdb -list -noheader -c "SELECT 'duckdb count: ' || count(*) FROM 'orders-2m.csv' WHERE status = 'refunded'" && wc -l /tmp/refunded.csv

kept 400135 refunded rows in 1.97s, peak 13 MB
duckdb count: 400135
400136 /tmp/refunded.csv

It kept 400,135 rows in 1.97 s at a peak of 13 MB, for a 130 MB input. DuckDB's count of the same filter agreed, 400,135, and the output file has 400,136 lines: the rows plus the header. Finding the column's position from the header, header.index("status"), instead of hard coding 5 means the script survives a reordered file and fails loudly with ValueError if the column is renamed, which I prefer to a wrong answer. The reader and the writer share the loop and nothing accumulates. When the task is exactly this shape, filter or reshape and pass along, the csv module is hard to beat for memory, and for 2,000,000 rows two seconds is hardly worth optimizing.

polars and DuckDB from Python

Both engines are one import away and both are worth knowing even if pandas is your daily tool. They share pandas' hazard with identifiers, with one difference in where the default lands:

import duckdb
import pandas as pd
import polars as pl

path = "/batches/csv-0001-0200/work/orders-2m.csv"
df = pl.read_csv(path)
print("polars default zip dtype  :", df.schema["zip"])
df = pl.read_csv(path, schema_overrides={"zip": pl.String})
print("polars override zip dtype :", df.schema["zip"], "| starts with 0:", int(df["zip"].str.starts_with("0").sum()))
rel = duckdb.sql(f"SELECT country, round(sum(amount), 2) AS revenue FROM '{path}' GROUP BY country ORDER BY revenue DESC LIMIT 3")
out = rel.df()
print("duckdb -> pandas:", type(out).__name__, out.shape, "| top:", out.iloc[0].tolist())
print("duckdb zip dtype :", duckdb.sql(f"DESCRIBE SELECT zip FROM '{path}'").fetchall()[0][1])

python /batches/csv-0001-0200/lab/py/polars_zip.py

polars default zip dtype  : Int64
polars override zip dtype : String | starts with 0: 190451
duckdb -> pandas: DataFrame (3, 2) | top: ['FR', np.float64(22969870.61)]
duckdb zip dtype : VARCHAR

polars inferred the ZIP column as Int64, which drops the leading zeros the same way pandas does, and schema_overrides={"zip": pl.String} restored it, with 190,451 values that start with 0 again. DuckDB made the column VARCHAR on its own, as the type article found. Its .df() method hands a query result to pandas as a DataFrame: the grouped revenue query came back as a 3 by 2 frame with France on top at 22,969,870.61, which is how I mix the two in practice. Let DuckDB do the reading, filtering and grouping where the file is large, and hand a result small enough for pandas to the library that is better for the next step. The Python API is described in the DuckDB Python documentation.

Testing a CSV round trip

Any code that writes a CSV and any code that reads one should be tested against values designed to break them. A short list covers most of the failures in this guide: a comma, a double quote, a line feed, a Windows line break, a leading zero, non-ASCII text, an empty string and leading and trailing spaces. The test writes them with the writer, reads them back with the reader, and compares:

import csv, io

NASTY = [
    ["id", "text"],
    ["1", "plain"],
    ["2", "comma, inside"],
    ["3", 'quote " inside'],
    ["4", "line one\nline two"],
    ["5", "windows\r\nbreak"],
    ["6", "02134"],
    ["7", "Zoë 日本語"],
    ["8", ""],
    ["9", "  padded  "],
]

def roundtrip(rows, **dialect):
    buf = io.StringIO(newline="")
    csv.writer(buf, **dialect).writerows(rows)
    buf.seek(0)
    return list(csv.reader(buf, **dialect))

assert roundtrip(NASTY) == NASTY
print("default dialect: round trip ok,", len(NASTY), "rows")
assert roundtrip(NASTY, delimiter=";", quoting=csv.QUOTE_ALL) == NASTY
print("semicolon, quote all: round trip ok")

python /batches/csv-0001-0200/lab/py/roundtrip_test.py

default dialect: round trip ok, 10 rows
semicolon, quote all: round trip ok

Both the default dialect and a semicolon dialect with QUOTE_ALL round-tripped all ten rows unchanged, including "windows\r\nbreak" and 02134. That is the property you want from the standard library, and it holds only because the buffer was created with newline=""; change that and the CRLF case is the first to fail. Put a test like this next to any function that serializes data, and run the same rows through the other side of your pipeline, the database load or the partner's importer, since the bug is often in the hand-off and not in Python. The format itself, and the exact rules the writer follows, are in RFC 4180, which is short enough to read once.

A loader that rejects instead of dropping

The failure I fear most is the silent one: a row skipped, a value coerced, a total that is quietly wrong. A loader that does its own conversion and keeps a record of everything it refuses is not much code, and it is the pattern I use for anything that matters:

"""Stream a CSV into typed dicts, reject what does not fit, and say how many."""
import csv
from datetime import datetime
from decimal import Decimal, InvalidOperation

SCHEMA = {
    "order_id": int,
    "customer_id": int,
    "zip": str,
    "amount": Decimal,
    "created_at": lambda s: datetime.strptime(s, "%Y-%m-%d %H:%M:%S"),
    "status": str,
    "country": str,
    "note": str,
}


def load(path, schema=SCHEMA, rejects=None):
    """Yield one typed dict per good record; append (line, reason, raw) to rejects for the rest."""
    with open(path, newline="", encoding="utf-8-sig") as f:
        reader = csv.reader(f, strict=True)
        header = next(reader)
        missing = set(schema) - set(header)
        if missing:
            raise ValueError(f"header lacks {sorted(missing)}")
        prev = reader.line_num
        for row in reader:
            line = prev + 1
            prev = reader.line_num
            if len(row) != len(header):
                if rejects is not None:
                    rejects.append((line, f"{len(row)} fields, expected {len(header)}", row))
                continue
            rec = dict(zip(header, row))
            try:
                yield {k: conv(rec[k]) for k, conv in schema.items()}
            except (ValueError, InvalidOperation) as e:
                if rejects is not None:
                    rejects.append((line, f"{type(e).__name__}: {e}", row))

It opens with utf-8-sig and newline="", runs the reader in strict mode, checks that the header contains the columns the schema needs, and for each record either yields a dictionary of converted values or appends (line, reason, raw) to a list the caller owns. Money is Decimal, the timestamp is parsed with a format, the ZIP code stays a string. On the 2,000,000 row file and on a 99 row file with one damaged record:

cd /batches/csv-0001-0200/lab/py && python use_loader.py orders-2m.csv

orders-2m.csv: 2000000 good, 0 rejected, total 160174646.36, 11.9s

cd /batches/csv-0001-0200/lab/py && python use_loader.py bad-orders.csv

bad-orders.csv: 98 good, 1 rejected, total 7702.84, 0.0s
  line 50: 9 fields, expected 8

The big file produced 2,000,000 good records, none rejected, and a total of 160,174,646.36 that matches every other tool. It took 11.9 s, which is the honest cost of converting eight fields per row in pure Python with Decimal and strptime: about eight times the plain csv.reader sum. The small file loaded 98 records, rejected one, and said why: line 50: 9 fields, expected 8. If 12 seconds is too slow, convert fewer columns, parse timestamps lazily, or hand the whole job to DuckDB, which does the same typing in a fraction of the time.

Two choices in the loader are deliberate. It rejects instead of repairing, because a repair is a guess about what the writer meant (when it is safe and when it is not). And the rejects list is the caller's, so the job can write it to a file, count it and alert on it. A pipeline whose reject count is usually zero and sometimes 4,000 has told you something.

Writing a file safely

A script that crashes halfway through writing leaves a truncated file at the final path, and the next job reads it as if it were complete. The fix is to write to a temporary file in the same directory and rename it when the write has finished, since a rename on the same filesystem replaces the old file in one step:

import csv, os, tempfile

def write_csv_atomic(path, header, rows):
    d = os.path.dirname(os.path.abspath(path))
    fd, tmp = tempfile.mkstemp(dir=d, prefix=".tmp-", suffix=".csv")
    try:
        with os.fdopen(fd, "w", newline="", encoding="utf-8") as f:
            w = csv.writer(f)
            w.writerow(header)
            w.writerows(rows)
        os.replace(tmp, path)
    except BaseException:
        os.unlink(tmp)
        raise

write_csv_atomic("atomic.csv", ["id", "name"], [[1, "Ana"], [2, "Bo, Jr."]])
print(open("atomic.csv", newline="").read().replace("\r\n", "<CRLF>\n"), end="")

def boom():
    yield [3, "Cy"]
    raise RuntimeError("source failed halfway")

try:
    write_csv_atomic("atomic.csv", ["id", "name"], boom())
except RuntimeError as e:
    print("error:", e)
print("file after failed write:", open("atomic.csv", newline="").read().count("\r\n"), "lines; leftovers:", [n for n in os.listdir(".") if n.startswith(".tmp-")])

python /batches/csv-0001-0200/lab/py/atomic.py

id,name<CRLF>
1,Ana<CRLF>
2,"Bo, Jr."<CRLF>
error: source failed halfway
file after failed write: 3 lines; leftovers: []

The first write produced id,name, 1,Ana and 2,"Bo, Jr." with CRLF endings and the comma correctly quoted. The second write failed halfway when its source raised; the old file was untouched, still three lines, and no .tmp- file was left behind. I use this shape for every file another job will pick up.

On speed: writing the 2,000,000 rows took 8.64 s with a csv.writer loop over the DataFrame's tuples and 3.67 s with DataFrame.to_csv:

python /batches/csv-0001-0200/lab/py/bench_write.py

csv.writer loop: 8.64s (131.9 MB) | DataFrame.to_csv: 3.67s (129.9 MB)

The writer loop's file was 131.9 MB and the pandas file 129.9 MB; the difference is the 2,000,000 carriage returns of \r\n against \n. That is a reason to choose a line ending on purpose.

Large files

Past a gigabyte the questions change. If the answer is an aggregate, stream it with the standard library or read it in chunks and reduce each one. If it is a join or a group by with many keys, a columnar engine will do the work faster than you can write the loop, and DuckDB reads the file in place, in parallel, without loading it. If you will query the file more than a few times, convert it once to Parquet; that file was 33.9 MB against 129.9 MB for the CSV and a grouped query took 0.04 s against 0.20 s in my test, as the Parquet article shows. And if the file arrives broken, the cheapest fix is in the producer.

Symptom, cause, fix

How to tell it worked

Compare record counts at every step with a number from a second tool. Check the dtypes or the types in your loader against what each column means. Read the first bytes of a file you wrote with xxd and confirm the line endings and the presence or absence of a BOM. Re-read the file you wrote with a different reader, and compare a checksum of one column. And keep the rejects: if a job drops rows, the reject file is what makes that a decision instead of an accident.

When Python is the wrong place for this job

If all you need is to filter, group and join, a SQL query on the file is shorter and faster than the loop, and the same files can be queried with DuckDB or the shell tools. If the file is a daily feed of many gigabytes, a pipeline in Python that parses every field is the expensive part and should be replaced by one that converts to a columnar format at the edge. If the data is meant for people, the spreadsheet is the product, and the work is in the encoding, the quoting and the line endings more than in the code. And if the same trouble keeps recurring from one source, no amount of defensive Python is as good as asking the source to produce a valid file, which is a single request that saves a script, and every reader after you.