How to Parse CSV Files in Python and Survive Quotes, Encodings, and Bad Rows

CSV looks like the simplest file format in existence: rows of text, values separated by commas. Most tutorials treat it that way and show you line.split(",") as if that were the whole story. It is not. The moment a real value contains a comma, a quote mark, a stray extra column, or a byte that a different program wrote in a different encoding, that one-liner silently produces the wrong data instead of an error you would notice. By the end of this tutorial you will have built a small, well-tested CSV parser in Python that survives all of those cases, and you will understand exactly why each one breaks a naive approach in the first place.

This is a beginner-friendly, hands-on tutorial. Every command shown here was actually run to produce the output printed underneath it, so you can follow along on your own machine and see the same results.

What You Will Build

You will parse a small CSV file of e-commerce orders that has five real-world problems hiding in it: a shipping address with a comma inside it, a missing quantity, a row with an extra column, a price that is not actually a number, and (in a second file) an invisible byte-order mark that Excel adds when it exports “CSV UTF-8.” Along the way you will:

• See exactly how and why splitting a line by comma breaks.
• Use Python’s built-in csv module to handle quoting correctly.
• Learn what csv.DictReader actually does when a row has too many or too few fields.
• Reproduce and fix a real KeyError caused by a UTF-8 byte-order mark.
• Build a validator that converts and checks each row, collecting a clear reason for every row it cannot use instead of crashing.
• Write automated tests for all of it with pytest.
• Reproduce a genuine Windows-specific bug in how the csv module writes line endings, and see the one-argument fix.

Prerequisites

• Python 3.10 or later installed on your machine (this tutorial was written and tested against Python 3.13). Everything here uses only the standard library, so you do not need to install anything extra to follow the main tutorial.
• pytest installed for the testing section (pip install pytest).
• Basic familiarity with Python: functions, dictionaries, and reading a traceback.
• A terminal and a text editor. Examples below use Windows paths and a Windows-specific bug is covered directly, but everything else works identically on macOS and Linux.

Step 1: See Why Splitting on Commas by Hand Breaks

Start with a small CSV file of orders. Save this as orders.csv:

order_id,customer_name,shipping_address,item,quantity,unit_price
1001,Maria Garcia,"123 Main St, Apt 4B",Widget,3,9.99
1002,Sean O'Brien,"456 Oak Avenue",Gadget,1,24.50
1003,Zoe Muller,"789 Pine Road",Gizmo,,14.75
1004,Chidi Okafor,"321 Elm Street",Widget,2,9.99,warehouse-B
1005,Anna Kowalski,"555 Birch Lane",Doohickey,5,oops

Notice row 1001: the shipping address is "123 Main St, Apt 4B". It is wrapped in double quotes specifically because it contains a comma that is part of the data, not a column separator. That quoting is part of the CSV format, defined in RFC 4180, and it is exactly what a naive split(",") does not understand. Try it:

with open("orders.csv", encoding="utf-8") as f:
    lines = f.read().splitlines()

header = lines[0].split(",")
print("Header:", header)
print()

for line in lines[1:]:
    fields = line.split(",")
    print(f"{len(fields)} fields: {fields}")

Running this prints:

Header: ['order_id', 'customer_name', 'shipping_address', 'item', 'quantity', 'unit_price']

7 fields: ['1001', 'Maria Garcia', '"123 Main St', ' Apt 4B"', 'Widget', '3', '9.99']
6 fields: ['1002', "Sean O'Brien", '"456 Oak Avenue"', 'Gadget', '1', '24.50']
6 fields: ['1003', 'Zoe Muller', '"789 Pine Road"', 'Gizmo', '', '14.75']
7 fields: ['1004', 'Chidi Okafor', '"321 Elm Street"', 'Widget', '2', '9.99', 'warehouse-B']
6 fields: ['1005', 'Anna Kowalski', '"555 Birch Lane"', 'Doohickey', '5', 'oops']

The header has 6 columns, but row 1001 just produced 7 fields. The address got cut in half at the comma, and every field after it shifted one position to the right: the item column now holds "Apt 4B", the quantity column holds "Widget", and there is a leftover 9.99 with nowhere to go. If you had blindly assigned these fields to columns by position, you would have silently stored garbage data with no error at all. That is the core danger of parsing CSV by hand: it does not fail loudly, it fails quietly.

Step 2: Let Python’s csv Module Handle Quoting

Python’s standard library ships a csv module built specifically to handle this. Swap the manual split for csv.reader:

import csv

with open("orders.csv", encoding="utf-8", newline="") as f:
    reader = csv.reader(f)
    header = next(reader)
    print("Header:", header)
    print()
    for row in reader:
        print(f"{len(row)} fields: {row}")

(Ignore the newline="" argument for a moment; Step 7 explains exactly why it is there and what breaks if you leave it out.)

Output:

Header: ['order_id', 'customer_name', 'shipping_address', 'item', 'quantity', 'unit_price']

6 fields: ['1001', 'Maria Garcia', '123 Main St, Apt 4B', 'Widget', '3', '9.99']
6 fields: ['1002', "Sean O'Brien", '456 Oak Avenue', 'Gadget', '1', '24.50']
6 fields: ['1003', 'Zoe Muller', '789 Pine Road', 'Gizmo', '', '14.75']
7 fields: ['1004', 'Chidi Okafor', '321 Elm Street', 'Widget', '2', '9.99', 'warehouse-B']
6 fields: ['1005', 'Anna Kowalski', '555 Birch Lane', 'Doohickey', '5', 'oops']

Row 1001 is fixed: the address comes back as one clean field, 123 Main St, Apt 4B, with the surrounding quotes stripped automatically. The csv module reads the file character by character and tracks whether it is currently inside a quoted field, so a comma inside quotes is just data, not a separator.

Notice what quoting alone did not fix: row 1004 still has 7 fields (a genuinely extra column), row 1003 still has an empty quantity, and row 1005 still has "oops" where a price should be. Those are not quoting problems. They are data-quality problems, and you will handle them explicitly in Step 5.

Step 3: Read Rows by Column Name With DictReader

Working with positional lists (row[0], row[1]) is error-prone once you have more than a few columns. csv.DictReader uses the header row to give you a dictionary per row instead:

import csv

with open("orders.csv", encoding="utf-8", newline="") as f:
    reader = csv.DictReader(f)
    for row in reader:
        print(dict(row))

{'order_id': '1001', 'customer_name': 'Maria Garcia', 'shipping_address': '123 Main St, Apt 4B', 'item': 'Widget', 'quantity': '3', 'unit_price': '9.99'}
{'order_id': '1002', 'customer_name': "Sean O'Brien", 'shipping_address': '456 Oak Avenue', 'item': 'Gadget', 'quantity': '1', 'unit_price': '24.50'}
{'order_id': '1003', 'customer_name': 'Zoe Muller', 'shipping_address': '789 Pine Road', 'item': 'Gizmo', 'quantity': '', 'unit_price': '14.75'}
{'order_id': '1004', 'customer_name': 'Chidi Okafor', 'shipping_address': '321 Elm Street', 'item': 'Widget', 'quantity': '2', 'unit_price': '9.99', None: ['warehouse-B']}
{'order_id': '1005', 'customer_name': 'Anna Kowalski', 'shipping_address': '555 Birch Lane', 'item': 'Doohickey', 'quantity': '5', 'unit_price': 'oops'}

Look closely at row 1004. It has one extra value, warehouse-B, with nowhere to go because the header only defines six columns. DictReader does not drop it and does not crash. It stores it in a list under the key None. This is documented, deliberate behavior: according to the official csv module documentation, “If a row has more fields than fieldnames, the remaining data is put in a list and stored with the fieldname specified by restkey (which defaults to None). If a non-blank row has fewer fields than fieldnames, the missing values are filled-in with the value of restval (which defaults to None).”

You can see the other half of that rule directly:

import csv, io

data = "a,b,c\n1,2\n1,2,3,4,5\n"
reader = csv.DictReader(io.StringIO(data))
for row in reader:
    print(dict(row))

{'a': '1', 'b': '2', 'c': None}
{'a': '1', 'b': '2', 'c': '3', None: ['4', '5']}

A short row (missing column c) gets a plain None value. A long row gets its extras collected into a list under the key None. Neither one raises an exception on its own, which is exactly why you cannot skip validating your rows: a silently malformed row will flow straight through your program unless you check for it yourself.

Step 4: Handle the Byte-Order Mark (BOM) Gotcha

Here is a bug that only shows up with files exported from certain Windows programs, most commonly when Excel saves a spreadsheet as “CSV UTF-8.” Create a second file, orders_bom.csv, written with a UTF-8 byte-order mark (BOM) at the very start, the way Excel does it:

with open("orders_bom.csv", "w", encoding="utf-8-sig", newline="") as f:
    f.write("order_id,customer_name,item\n1006,Priya Shah,Widget\n")

Now read it back the “normal” way, with plain utf-8:

import csv

with open("orders_bom.csv", encoding="utf-8", newline="") as f:
    reader = csv.DictReader(f)
    row = next(reader)
    print("Fieldnames:", reader.fieldnames)
    print("Row:", row)
    print(row["order_id"])

Fieldnames: ['order_id', 'customer_name', 'item']
Row: {'order_id': '1006', 'customer_name': 'Priya Shah', 'item': 'Widget'}
Traceback (most recent call last):
  ...
KeyError: 'order_id'

The column really is called order_id in the file. But three invisible bytes at the very start of the file (0xEF 0xBB 0xBF) got decoded as a real character, the Unicode byte-order mark , and glued onto the front of the first header name. Your code asks for row["order_id"], Python looks for that exact key, does not find it (the real key is "order_id"), and raises KeyError. Per the Python codecs documentation, utf-8-sig is “a variant of UTF-8… for its Notepad program: Before any of the Unicode characters is written to the file, a UTF-8 encoded BOM… is written,” and Microsoft tooling uses it to help other programs auto-detect that a file is UTF-8.

The fix is a one-word change: open the file with encoding="utf-8-sig" instead of "utf-8". That codec strips a leading BOM if one is present:

with open("orders_bom.csv", encoding="utf-8-sig", newline="") as f:
    reader = csv.DictReader(f)
    row = next(reader)
    print("Fieldnames:", reader.fieldnames)
    print(row["order_id"])

Fieldnames: ['order_id', 'customer_name', 'item']
1006

Crucially, utf-8-sig is also safe on files that have no BOM at all; it only strips the mark when one is actually present at the start of the file, and otherwise decodes normally. That makes it a safe default any time you are reading a CSV file from an unknown source, rather than something you only reach for after you have already been bitten once.

Step 5: Validate and Convert Types, Reporting Bad Rows Instead of Crashing

You now know three separate failure modes: quoting, ragged rows, and encoding. A real parser needs to convert string values to the right types (an order quantity should be an integer, a price should be a float) and needs a clear way to say “this row is bad, and here is why” instead of either crashing the whole file or silently keeping garbage. Save this as order_parser.py:

from dataclasses import dataclass


@dataclass
class Order:
    order_id: str
    customer_name: str
    shipping_address: str
    item: str
    quantity: int
    unit_price: float


class RowError(Exception):
    """Raised for one bad row; carries the row number and the reason."""

    def __init__(self, row_number, reason):
        self.row_number = row_number
        self.reason = reason
        super().__init__(f"row {row_number}: {reason}")


def parse_row(row_number, row):
    """Validate and convert one DictReader row into an Order.

    Raises RowError with a specific reason if the row can't be used.
    """
    if None in row:
        raise RowError(row_number, f"unexpected extra column(s): {row[None]}")

    for field in ("order_id", "customer_name", "shipping_address", "item", "quantity", "unit_price"):
        if row.get(field) is None:
            raise RowError(row_number, f"missing column '{field}'")

    if row["quantity"] == "":
        raise RowError(row_number, "quantity is empty")
    try:
        quantity = int(row["quantity"])
    except ValueError:
        raise RowError(row_number, f"quantity {row['quantity']!r} is not a whole number")

    try:
        unit_price = float(row["unit_price"])
    except ValueError:
        raise RowError(row_number, f"unit_price {row['unit_price']!r} is not a number")

    return Order(
        order_id=row["order_id"],
        customer_name=row["customer_name"],
        shipping_address=row["shipping_address"],
        item=row["item"],
        quantity=quantity,
        unit_price=unit_price,
    )


def parse_orders(path):
    """Parse a CSV file of orders.

    Returns (orders, errors): a list of Order objects that parsed cleanly,
    and a list of RowError objects for rows that did not.
    """
    import csv

    orders = []
    errors = []
    with open(path, encoding="utf-8-sig", newline="") as f:
        reader = csv.DictReader(f)
        for row_number, row in enumerate(reader, start=2):  # header is row 1
            try:
                orders.append(parse_row(row_number, row))
            except RowError as e:
                errors.append(e)
    return orders, errors


if __name__ == "__main__":
    orders, errors = parse_orders("orders.csv")

    print(f"Parsed {len(orders)} valid order(s):")
    for order in orders:
        print(f"  {order}")

    print(f"\nSkipped {len(errors)} bad row(s):")
    for error in errors:
        print(f"  {error}")

A few design choices worth calling out:

• parse_orders opens the file with encoding="utf-8-sig" by default, so it is safe against the BOM gotcha from Step 4 with no extra work from the caller.
• Checking if None in row catches the “too many columns” case from Step 3 before anything else runs.
• Each failure raises RowError with a specific, human-readable reason and the exact row number, rather than one generic “invalid data” message.
• enumerate(reader, start=2) makes the row numbers in error messages match what you would see if you opened the file in a text editor (the header is line 1, so the first data row is line 2).

Run it against orders.csv:

Parsed 2 valid order(s):
  Order(order_id='1001', customer_name='Maria Garcia', shipping_address='123 Main St, Apt 4B', item='Widget', quantity=3, unit_price=9.99)
  Order(order_id='1002', customer_name="Sean O'Brien", shipping_address='456 Oak Avenue', item='Gadget', quantity=1, unit_price=24.5)

Skipped 3 bad row(s):
  row 4: quantity is empty
  row 5: unexpected extra column(s): ['warehouse-B']
  row 6: unit_price 'oops' is not a number

Every one of the five original problems is now accounted for: two rows parsed cleanly (with the embedded comma correctly preserved in the address), and the three genuinely bad rows were skipped with a specific, correct reason instead of crashing the whole import or silently storing bad numbers.

Step 6: Write Automated Tests With pytest

Manually running the script and reading the output is fine while you are developing, but you want tests that lock this behavior in place so a future change cannot quietly break it. Save this as test_order_parser.py in the same folder:

import pytest
from order_parser import parse_orders


@pytest.fixture
def csv_file(tmp_path):
    """Write a small CSV to a temp file and return its path."""
    def _write(text):
        path = tmp_path / "test_orders.csv"
        path.write_text(text, encoding="utf-8")
        return str(path)
    return _write


def test_parses_a_clean_row(csv_file):
    path = csv_file(
        "order_id,customer_name,shipping_address,item,quantity,unit_price\n"
        "1,Ada Lovelace,10 Downing St,Widget,2,5.00\n"
    )
    orders, errors = parse_orders(path)
    assert errors == []
    assert len(orders) == 1
    assert orders[0].quantity == 2
    assert orders[0].unit_price == 5.00


def test_handles_embedded_comma_in_quoted_field(csv_file):
    path = csv_file(
        "order_id,customer_name,shipping_address,item,quantity,unit_price\n"
        '1,Ada Lovelace,"10 Downing St, Flat 2",Widget,2,5.00\n'
    )
    orders, errors = parse_orders(path)
    assert errors == []
    assert orders[0].shipping_address == "10 Downing St, Flat 2"


def test_skips_row_with_empty_quantity(csv_file):
    path = csv_file(
        "order_id,customer_name,shipping_address,item,quantity,unit_price\n"
        "1,Ada Lovelace,10 Downing St,Widget,,5.00\n"
    )
    orders, errors = parse_orders(path)
    assert orders == []
    assert len(errors) == 1
    assert "quantity is empty" in str(errors[0])


def test_skips_row_with_non_numeric_price(csv_file):
    path = csv_file(
        "order_id,customer_name,shipping_address,item,quantity,unit_price\n"
        "1,Ada Lovelace,10 Downing St,Widget,2,free\n"
    )
    orders, errors = parse_orders(path)
    assert orders == []
    assert "unit_price" in str(errors[0])


def test_skips_row_with_extra_column(csv_file):
    path = csv_file(
        "order_id,customer_name,shipping_address,item,quantity,unit_price\n"
        "1,Ada Lovelace,10 Downing St,Widget,2,5.00,extra\n"
    )
    orders, errors = parse_orders(path)
    assert orders == []
    assert "extra column" in str(errors[0])


def test_handles_utf8_bom(tmp_path):
    path = tmp_path / "bom_orders.csv"
    path.write_text(
        "order_id,customer_name,shipping_address,item,quantity,unit_price\n"
        "1,Ada Lovelace,10 Downing St,Widget,2,5.00\n",
        encoding="utf-8-sig",
    )
    orders, errors = parse_orders(str(path))
    assert errors == []
    assert orders[0].order_id == "1"

Each test uses pytest’s built-in tmp_path fixture, which hands you a fresh, automatically-cleaned-up temporary directory for every test, so tests never collide with each other or leave files behind. Run the suite:

python -m pytest test_order_parser.py -v

collecting ... collected 6 items

test_order_parser.py::test_parses_a_clean_row PASSED                     [ 16%]
test_order_parser.py::test_handles_embedded_comma_in_quoted_field PASSED [ 33%]
test_order_parser.py::test_skips_row_with_empty_quantity PASSED          [ 50%]
test_order_parser.py::test_skips_row_with_non_numeric_price PASSED       [ 66%]
test_order_parser.py::test_skips_row_with_extra_column PASSED            [ 83%]
test_order_parser.py::test_handles_utf8_bom PASSED                       [100%]

6 passed in 0.10s

Notice that the BOM test writes its temporary file with encoding="utf-8-sig" directly through pathlib‘s write_text, which is a convenient way to reproduce a BOM-prefixed file in a test without hand-writing the raw bytes.

Step 7: Avoid the newline=” Gotcha When Writing CSV (a Real Windows Bug)

You have been opening files with newline="" throughout this tutorial without a full explanation. Here is what actually goes wrong if you leave it out, reproduced on a real Windows machine. Compare writing the same two rows with and without it:

import csv

rows = [["id", "name"], ["1", "Ada"], ["2", "Grace"]]

# Wrong: no newline='' on write
with open("bad_newline.csv", "w", encoding="utf-8") as f:
    w = csv.writer(f)
    w.writerows(rows)

print(open("bad_newline.csv", "rb").read())

# Right: newline='' on write
with open("good_newline.csv", "w", encoding="utf-8", newline="") as f:
    w = csv.writer(f)
    w.writerows(rows)

print(open("good_newline.csv", "rb").read())

b'id,name\r\r\n1,Ada\r\r\n2,Grace\r\r\n'
b'id,name\r\n1,Ada\r\n2,Grace\r\n'

Look at the raw bytes: the “wrong” file has \r\r\n after every row instead of a plain \r\n. Here is why. On Windows, Python’s text mode automatically translates every \n you write into the platform’s native line ending, \r\n. But the csv module also writes its own \r\n at the end of every row, on every platform, regardless of your operating system, because that is what the CSV format itself specifies. Combine the two and you get a doubled carriage return. The official documentation spells this out directly: “If newline='' is not specified, newlines embedded inside quoted fields will not be interpreted correctly, and on platforms that use \r\n line endings on write an extra \r will be added. It should always be safe to specify newline='', since the csv module does its own (universal) newline handling.”

Reading the broken file back shows the damage clearly:

with open("bad_newline.csv", encoding="utf-8") as f:
    for row in csv.reader(f):
        print(row)

['id', 'name']
[]
['1', 'Ada']
[]
['2', 'Grace']
[]

Every real row is now followed by a spurious empty row, because Python’s own text-mode newline translation is fighting with the csv module’s newline handling on the way back in too. (Interestingly, csv.DictReader quietly skips fully blank rows for you, so if you had used DictReader here you would not have noticed the corruption at all; it would only surface once that file was opened by a different program, such as a spreadsheet, that does not do the same forgiving skip.) The fix costs nothing: always pass newline="" to open() whenever you are reading or writing a CSV file, on every platform, every time.

Common Mistakes and Gotchas

• Splitting or joining CSV text by hand. As Step 1 showed, this breaks the instant a field contains the delimiter character inside quotes. Always use the csv module (or a well-tested library) instead of string methods.
• Forgetting newline="". As Step 7 showed, omitting it on Windows corrupts the line endings you write and can introduce phantom blank rows when reading them back. The official documentation says it is always safe to include, so there is no reason to leave it out.
• Assuming a fixed number of columns. Step 3 showed that DictReader tolerates ragged rows by design (via restkey and restval), which means your code, not the library, is responsible for deciding what to do about them.
• Trusting encoding="utf-8" blindly. If you cannot control how a file was produced, prefer encoding="utf-8-sig" for reading. It is a strict superset of plain UTF-8 decoding: it removes a leading BOM if present and behaves identically to "utf-8" if one is not.
• Catching exceptions too broadly. The parser in Step 5 only catches the specific ValueError that int() and float() raise for bad input. A bare except: would also swallow real bugs in your own code and make them look like bad data.

Bonus: Detecting an Unknown Delimiter With csv.Sniffer

Everything above assumed you already knew the file used commas. Sometimes you receive a file and do not know whether it uses commas, semicolons, or tabs (common in regions where a comma is the decimal separator). The standard library’s csv.Sniffer class can often work this out for you from a sample of the file:

import csv

with open("semicolon.csv", encoding="utf-8", newline="") as f:
    sample = f.read(1024)
    f.seek(0)
    dialect = csv.Sniffer().sniff(sample)
    print("Detected delimiter:", repr(dialect.delimiter))
    reader = csv.DictReader(f, dialect=dialect)
    for row in reader:
        print(dict(row))

Against a file that actually uses semicolons (order_id;customer_name;item), this correctly prints:

Detected delimiter: ';'
{'order_id': '1', 'customer_name': 'Ada Lovelace', 'item': 'Widget'}
{'order_id': '2', 'customer_name': 'Grace Hopper', 'item': 'Gadget'}

Sniffer works by statistical guessing, not certainty, so treat its output as a helpful default rather than a guarantee, especially on very small or unusual samples.

How to Verify Everything Works

Put all of the pieces together and confirm the whole pipeline end to end:

1. Run python order_parser.py and confirm you see 2 parsed orders and 3 skipped rows with the exact reasons shown in Step 5.
2. Run python -m pytest test_order_parser.py -v and confirm all 6 tests pass.
3. Deliberately edit orders.csv to introduce a new bad row (for example, delete the entire unit_price value and its leading comma from the end of one row, so that row has one fewer field than the header) and re-run the parser. You should see it show up in the “skipped” list with the reason missing column 'unit_price', not crash the whole script.
4. Re-run the BOM example from Step 4 and confirm that reading with utf-8-sig succeeds while reading with plain utf-8 raises KeyError, so you have seen both the failure and the fix with your own eyes.

If all four checks pass, you have a parser that handles the four most common ways real-world CSV files go wrong, backed by tests that will catch a regression the moment one is introduced.

Next Steps

• If you need to aggregate, filter, or reshape large volumes of tabular data rather than just validate individual rows, look at how to build an idempotent, incremental ETL pipeline in Python with pandas, which picks up roughly where this tutorial leaves off.
• Explore the csv module’s dialect system if you regularly work with files that use non-standard quoting or escaping rules from a specific external system.
• If your CSV files are large enough that loading them entirely into memory is a concern, keep using the row-by-row iteration style shown here (for row in reader:) rather than converting the reader to a list, since the reader only holds one row in memory at a time.
• For CSV files with millions of rows where performance matters more than dependency-free simplicity, benchmark the standard library approach shown here against pandas.read_csv, which uses a compiled C parser and can be substantially faster at scale.