How to Write CSV Values With Commas, Quotes and Newlines in Python

The bug

Here is a hand-written CSV export:

def export_csv(rows: list[list[str]]) -> str:
    out = ""
    for row in rows:
        out += ",".join(row) + "\n"
    return out

It works for clean data. Then a customer name contains a comma:

import csv, io
text = export_csv([["1", "Smith, Bob"]])
print(list(csv.reader(io.StringIO(text))))
# [['1', 'Smith', ' Bob']]   - 3 cells instead of 2

Any program that reads this file - Excel, pandas, a database import - sees an extra column. A double quote or a multi-line address breaks the row in other ways.

How CSV quoting works

The common CSV format (described in RFC 4180) has three rules:

1. A cell that contains a comma, a double quote or a line break is wrapped in double quotes.
2. A double quote inside a quoted cell is written twice: " becomes "".
3. A line break inside a quoted cell is part of the value, not the end of the row.

So these values:

Value	Written as
Smith, Bob	"Smith, Bob"
O"Reilly	"O""Reilly"
12 Main St + line break + Apt 4	"12 Main St (line break) Apt 4"

You could write these rules yourself, but it is easy to miss a case. The standard library already does it.

The fix: csv.writer

import csv
import io

def export_csv(rows: list[list[str]]) -> str:
    buf = io.StringIO()
    csv.writer(buf).writerows(rows)
    return buf.getvalue()

Check it with the same round-trip test:

rows = [
    ["id", "name", "address"],
    ["1", "Smith, Bob", "12 Main St"],
    ["2", 'O"Reilly', "12 Main St\nApt 4"],
]
text = export_csv(rows)
assert list(csv.reader(io.StringIO(text))) == rows

The writer quotes only the cells that need it, doubles inner quotes, and keeps line breaks inside quoted cells. The reader turns the text back into the original values.

Writing to a file

When you write to a file, open it with newline="":

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

csv.writer ends each row with \r\n by default. Without newline="", Python may change the line endings again on some systems, and you get empty lines between rows when the file is opened in Excel.

Writing dictionaries with DictWriter

If your rows are dicts (for example from a database cursor), csv.DictWriter maps keys to columns:

fields = ["id", "name", "email"]
writer = csv.DictWriter(buf, fieldnames=fields, extrasaction="ignore")
writer.writeheader()
writer.writerows(customers)   # list of dicts

extrasaction="ignore" drops keys that are not in fields instead of raising an error.

Returning CSV from a Flask endpoint

from flask import Response

@app.get("/export")
def export():
    text = export_csv(load_rows())
    return Response(
        text,
        mimetype="text/csv",
        headers={"Content-Disposition": "attachment; filename=export.csv"},
    )

The Content-Disposition header tells the browser to download the file.

Other options you may need

• quoting=csv.QUOTE_ALL wraps every cell in quotes. Some import tools expect this.
• delimiter=";" writes semicolon-separated files, common in regions where the comma is the decimal separator.
• encoding="utf-8-sig" adds a byte order mark so Excel detects UTF-8 and shows accented characters correctly.
• Spreadsheet formulas: a cell that starts with =, +, - or @ can run as a formula when the file is opened in a spreadsheet. If users control the data, prefix those cells with a single quote (') before writing.

The general lesson

CSV looks simple, but its edge cases are in the data, not in your test file with clean names. When a format has a parser and a writer in the standard library, use them, and test with values that contain the separator, the quote character and a line break.