CSV (comma-separated values) is the format every e-commerce system eventually has to read or write: product exports from a supplier, daily order dumps for accounting, review imports from a third-party platform. The csv module in the standard library handles the format's edge cases (embedded commas, quoted fields, multi-line cells) that a naive line.split(",") quietly gets wrong. This lesson covers the reader and writer pairs, the dict-based variants, dialects, quoting rules, format sniffing, and the one keyword argument (newline='') that everyone forgets.

CSV looks like a one-liner problem. Open the file, read each line, split on ,, done. That works on the friendliest possible file and breaks on every real one. Three things go wrong fast.

First, fields can contain commas. A product titled Anker, Wireless Mouse is one field, not two. To handle this, CSV writers wrap fields that contain commas in double quotes: "Anker, Wireless Mouse",29.99,Electronics. A naive split on , returns four tokens; the correct parse returns three.

Second, fields can contain the quote character itself. CSV escapes an embedded " by doubling it: a review body of She said "great mouse" is written as "She said ""great mouse""". Splitting on , produces garbage. Even splitting smarter without honoring the escape rule mishandles the inner quotes.

Third, fields can contain newlines. A product description that spans two lines is one logical record but two physical lines in the file, with the embedded newline wrapped inside quotes. Reading line-by-line with for line in file: cuts that record in half.

Here is what goes wrong with a hand-rolled splitter:

Four fields instead of three, the title is split in half, and the quoted review still has its escape doubling visible. The csv module gets all of this right because it implements the actual CSV grammar (RFC 4180 plus the common variants).

Three fields, the title intact, the inner quotes un-escaped. That's the whole reason the module exists.

One more thing worth saying up front: the csv module is not pandas. pandas.read_csv is a heavier dependency that returns a DataFrame with type inference, missing-value handling, and column slicing. For the streaming, row-by-row reading and writing this lesson covers, the standard library is plenty.

csv.reader is the simplest interface. You give it any iterable of lines (a file, a list of strings, a StringIO), and it yields each record as a list of string fields.

Imagine a small product catalog file at products.csv:

Reading it row by row:

Three things to notice. The open() call uses newline="", which we'll cover in detail below. next(reader) pulls the first row, which is the header. The rest of the rows come out of the for loop as plain lists of strings. Every field is a string, even price and stock; CSV doesn't carry types, so converting to float or int is the caller's job.

Type conversion looks like this:

float(price) and int(stock) happen per row. If the field is missing or malformed, the conversion raises ValueError, which you'd usually catch and log as a bad-row problem rather than crashing the import.

csv.writer is the mirror image. You hand it a file open for writing, then call writerow(row) or writerows(rows) with lists of values. The writer handles the quoting and escaping for you.

Notice what the writer did with 'USB-C "Pro" Hub': it wrapped the whole field in double quotes (because the value itself contained a ") and doubled the inner quotes to escape them. That's the CSV escape rule, applied automatically. Numeric values were converted to their string forms using str(); 29.99 came out as 29.99, and 24.5 came out as 24.5 (Python collapses the trailing zero, which is something to know if you care about pretty currency output).

If you want consistent decimal formatting, do it before writing:

f"{total:.2f}" forces two decimal places, so 24.5 is written as 24.50. The writer treats the resulting string as just another field. This is the pattern for any time you care about output formatting: format it in Python first, hand the writer a string.

writerow writes one row; writerows takes an iterable of rows. Both append to wherever the file is currently positioned. If you want to append to an existing CSV without rewriting the header, open in "a" mode and just call writerow for each new record.

This is the bug that catches everyone. When you open a CSV file with the default newline (which is None, meaning "use universal newlines mode"), Python's text layer translates \n to \r\n on Windows when writing, and translates \r\n back to \n on Windows when reading. The csv module already handles row endings on its own, and the two layers fight each other.

The visible symptom on Windows is blank lines between every record in a written CSV. When csv.writer writes row\r\n and the text layer turns the \n into \r\n, the result on disk is row\r\r\n. Open the file in Excel and every other row looks empty.

The fix is newline="", which tells the text layer "don't translate newlines, just hand them through." The csv module then writes exactly the line endings the dialect specifies, and reads them back the same way.

What's wrong with this code?

The double \r\r\n is the bug. On Linux and macOS the same code produces clean a,b\nc,d\n, which hides the problem until someone runs the script on Windows or opens the file in an Excel that strictly interprets the doubled carriage return as a blank row.

Fix:

The Python docs are explicit about this: always open CSV files with newline="". The rule applies on every operating system, even though the bug only shows up on Windows. Treat it as part of the boilerplate, like writing with open(...) instead of leaving files unclosed.

Index-based row access (row[0], row[1]) is fragile. If a supplier rearranges the columns in next month's export, every offset shifts by one and the import silently corrupts your database. csv.DictReader solves this by yielding each row as a dict keyed by the column names from the header row.

DictReader reads the first row of the file and treats it as the field names. Every subsequent row becomes a dict mapping each name to its value. The order of columns no longer matters to the consuming code; what matters is the column names.

You can override the field names if the file has no header, or if you want to rename the keys:

If the file has its own header but you pass fieldnames, the header row is treated as a regular data row (because DictReader no longer needs to consume the first line for field names). That's almost never what you want, so either let DictReader read the header automatically or use fieldnames only on truly headerless files.

Two edge cases come up with real-world data. If a row has fewer fields than the header, the missing keys map to None. If a row has more fields, the extra values collect into a list under the key set by the restkey parameter (default: None, which means the extras are dropped silently). For strict imports, set both:

The second row is short, so price falls back to "MISSING". The third row is long, so the two extra fields land in the extras list. In production code, the presence of extras or the restval sentinel is usually a signal to log the row as malformed rather than silently absorb it.

The mirror is csv.DictWriter, which takes a list of dicts and writes them to a CSV using a declared set of field names. You have to call writeheader() once (unless you don't want a header row), then writerow(dict) or writerows([dict, ...]).

Three details worth pointing out. fieldnames controls the column order in the output, not the order of keys in each dict. The dict can have keys in any order, and DictWriter looks each one up by name. The "Solid keyboard, loud." field got wrapped in quotes because it contained a comma. The 'She said "okay"' field got wrapped in quotes and the inner quotes were doubled, which is the standard CSV escape for an embedded quote.

By default, if a row dict is missing a key that's in fieldnames, DictWriter raises ValueError. If a row dict has an extra key not in fieldnames, it also raises. Both behaviors can be relaxed with the extrasaction parameter:

extrasaction="ignore" drops keys that aren't in fieldnames. The other value, "raise" (the default), is stricter. For missing keys, the parameter to use is restval: it's the value the writer substitutes when a dict is missing one of the declared fields.

restval="N/A" filled in the missing fields. The default restval is "", which writes empty strings, often a saner choice when downstream consumers will load the file back into a database.

A "dialect" is the bundle of formatting choices that make a CSV file what it is: the delimiter, the quote character, the line terminator, how quoting is applied, and a few smaller knobs. The csv module ships three named dialects, and excel is the default.

excel matches what Microsoft Excel writes when you "Save As CSV": commas, double quotes, Windows line endings, and only the fields that need quoting are quoted. excel-tab is the same thing but with tabs, which some legacy systems use because tabs are less likely to appear inside fields. unix_dialect was added in Python 3.2 and uses Unix line endings plus QUOTE_ALL, which wraps every field in quotes regardless of content.

You pick a dialect by name when constructing the reader or writer:

The fields are separated by tab characters. The line terminator is still \r\n because excel-tab inherits that from the excel family.

You can also register a custom dialect for a supplier or partner who uses a non-standard format:

register_dialect lets the rest of the code refer to that bundle of options by a single name. The third row's Power Strip | Surge value contained the delimiter, so the writer wrapped it in quotes automatically. The same dialect name now works for reading, too: csv.reader(f, dialect="supplier_pipe") reads the file back correctly.

Instead of registering, you can pass the same options as keyword arguments directly to reader or writer. That's fine for one-off scripts. Registering is cleaner when the same format shows up in multiple places.

The quoting parameter controls when the writer wraps a field in quotes. Four constants live in the csv module, each describing a different strategy.

QUOTE_MINIMAL is what excel uses and what you want most of the time. It produces the smallest, cleanest output. QUOTE_ALL adds quotes everywhere, which makes parsing more forgiving for downstream tools that aren't strict CSV parsers (every field has a clear start and end). Here's the same data under each:

QUOTE_NONE is the strictest. The writer refuses to quote anything, so any field that contains the delimiter forces a runtime error unless an escapechar is provided to escape the delimiter character literally.

The delimiter inside the field is prefixed with a backslash. The reader needs the same escapechar to undo it. This is rare in practice; most pipelines use quoting and skip the escape-character setup.

QUOTE_NONNUMERIC does something special on read: any field that isn't quoted gets parsed as a float. This is the closest the csv module gets to type inference. It works because the writer wraps every non-numeric field in quotes, so the reader knows "unquoted means number."

The header row is all strings (it was quoted in the source). The data rows have a quoted product name (still a string) and unquoted numbers (parsed as float). Notice that 42 came back as 42.0: QUOTE_NONNUMERIC parses every unquoted field as a float, even when the source value looked like an integer. If you want true integers, do the conversion yourself instead.

Not every file in the wild uses commas. Tabs and pipes are common. Semicolons show up in European exports because some locales use a comma as the decimal separator (19,99 for the price) and need a different field delimiter to avoid collision. The delimiter and quotechar keyword arguments let you adapt.

Each field is now separated by ;. The prices use a comma as the decimal mark and aren't quoted because the comma isn't the delimiter in this file. Reading the file back uses the same options:

The reader uses the same delimiter=";" and produces clean fields. The price comes back as "19,99" (a string with a comma); converting it to a float requires float(price.replace(",", ".")) if you need numeric values.

A different quotechar is rarer, but works the same way. Passing quotechar="'" to the reader and writer flips the convention so that single quotes wrap each field and inner double quotes pass through verbatim. This shows up in legacy systems whose data contains lots of double quotes; for most modern pipelines, the default " is what you want.

When you don't know whether a file is comma-separated, tab-separated, or pipe-separated, csv.Sniffer can guess from a sample. It looks at the first chunk of the file, counts delimiter candidates, and returns a dialect you can pass to csv.reader.

Sniffer.sniff returned a Dialect object describing the format. Plugging it into a reader handles the rest:

The sniffer can also guess whether the file has a header row, with has_header:

The heuristic is reasonable but not perfect: it compares the first row to subsequent rows and looks for a clear difference (different types, longer text, all-string fields above numeric ones below). For pristine data the answer is usually right. For ambiguous data it can be wrong, and you should treat it as a hint rather than a guarantee.

Two things to notice. The sample is the first 2048 bytes; that's usually enough for the sniffer to make a confident guess and small enough to read quickly. f.seek(0) rewinds the file so the reader starts from the top after the sniff. If the file isn't seekable (a network stream, for example), buffer the sample manually with io.StringIO instead.

A realistic e-commerce task: read a product catalog, read a reviews file that references the products by product_id, join them, and write a per-product summary CSV with average rating and review count.

Set up the input files:

Now the read-join-write pipeline:

A few patterns worth noticing. The reviews are loaded into an in-memory dict keyed by product_id, which gives O(1) lookup per product. Both files are read row-by-row with DictReader, so there's no full-file load even if the inputs are large. The output DictWriter declares its fieldnames once and the writer enforces that every row has those keys. The avg_rating is formatted to two decimal places inside the dict before writing, so the writer just sees strings.

The same pattern (read with DictReader, transform in Python, write with DictWriter) handles most CSV processing jobs an e-commerce app runs into: importing supplier catalogs, exporting orders to accounting, transforming feeds for partner integrations. Once the dialect, encoding, and quoting are settled at the edges, the middle is plain Python.

CSV files don't carry encoding information, so the caller has to pick one when opening the file. UTF-8 is the right default for new files; for Excel on Windows that needs to render non-ASCII characters correctly, use encoding="utf-8-sig", which prepends a byte-order mark. The csv module just relies on whatever text stream the open file provides.