Reading CSV & Excel Files
Published by Brayan K
Spreadsheets run the business world — sales exports, customer lists, financial reports. Python lets you read, transform, and write that tabular data automatically, turning hours of manual spreadsheet work into a script that runs in seconds.
Part of the free Python course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.
A CSV line is more than split(",")
A CSV file is text: one row per line, with the values separated by commas. A value that holds a comma is put in double quotes, which is why String Methods warned that a plain split(",") cuts such a line in the wrong place. The csv module reads the quotes:
import csv
line = 'Ada,"London, UK",36'
print(line.split(","))
for row in csv.reader([line]):
print(row)
# ✅ Expected output:
# ['Ada', '"London', ' UK"', '36']
# ['Ada', 'London, UK', '36']csv.reader() takes anything that hands out lines, here a list holding one string, and gives each row as a list of strings. A double quote inside a quoted value is written twice:
import csv
lines = ['title,quote', '"Dune","Fear is the ""mind-killer"""']
for row in csv.reader(lines):
print(row)
# ✅ Expected output:
# ['title', 'quote']
# ['Dune', 'Fear is the "mind-killer"']Give it a file's name instead of its lines and it reads the name itself, a letter at a time: csv.reader("scores.csv") gives ten rows, from ['s'] to ['v']. Reading a file comes further down.
Every value is text
csv.DictReader() takes the first line as the column names and gives each row as a dictionary. Every value in it is a string, the numbers too:
import csv
lines = ["name,salary", "Ada,95000", "Grace,98000"]
rows = list(csv.DictReader(lines))
print(rows[0])
total = sum(row["salary"] for row in rows)
# ✅ Expected output:
# {'name': 'Ada', 'salary': '95000'}
# ✅ Expected error:
# TypeError: unsupported operand type(s) for +: 'int' and 'str'Convert each value with int() or float() where you use it. Text also compares a character at a time, so as text the biggest page count is "96":
import csv
lines = ["title,pages", "Dune,412", "Bee,96", "Emma,474"]
rows = list(csv.DictReader(lines))
longest = max(rows, key=lambda r: r["pages"])
print(longest["title"], longest["pages"])
longest = max(rows, key=lambda r: int(r["pages"]))
print(longest["title"], longest["pages"])
print(sum(int(r["pages"]) for r in rows))
# ✅ Expected output:
# Bee 96
# Emma 474
# 982The header row
csv.reader() hands back the header as an ordinary row, so int() on its second value stops with ValueError: invalid literal for int() with base 10: 'salary'. Take the header off with slicing first:
import csv
lines = ["name,salary", "Ada,95000", "Grace,98000"]
rows = list(csv.reader(lines))
header = rows[0]
total = 0
for row in rows[1:]:
total += int(row[1])
print(header, total)
# ✅ Expected output:
# ['name', 'salary'] 193000DictReader's keys are the header exactly as written, capitals included: under a header of Name,Salary, row["name"] stops with KeyError: 'name'. And a file with no header row loses its first row to the keys, unless you name the columns yourself with fieldnames=:
import csv
lines = ["Ada,95000", "Grace,98000"]
print(list(csv.DictReader(lines)))
for row in csv.DictReader(lines, fieldnames=["name", "salary"]):
print(row)
# ✅ Expected output:
# [{'Ada': 'Grace', '95000': '98000'}]
# {'name': 'Ada', 'salary': '95000'}
# {'name': 'Grace', 'salary': '98000'}Semicolons, empty cells and short rows
Spreadsheets set up for countries where the comma is the decimal point often save with semicolons instead. Read with the default comma, each row stays in one piece, or splits at the decimal comma:
import csv
lines = ["name;price", "Widget;9,99", "Gadget;19,50"]
for row in csv.reader(lines):
print(row)
for row in csv.DictReader(lines, delimiter=";"):
print(row["name"], float(row["price"].replace(",", ".")))
# ✅ Expected output:
# ['name;price']
# ['Widget;9', '99']
# ['Gadget;19', '50']
# Widget 9.99
# Gadget 19.5delimiter=";" splits at semicolons, and replace(",", ".") turns the decimal comma into a point, because float("9,99") stops with ValueError: could not convert string to float: '9,99'. Real exports also have gaps:
import csv
lines = ["product,qty", "Widget,3", "Gadget,", "Gizmo"]
for row in csv.DictReader(lines):
print(row)
qty = int(row["qty"]) if row["qty"] else 0
print(row["product"], qty)
# ✅ Expected output:
# {'product': 'Widget', 'qty': '3'}
# Widget 3
# {'product': 'Gadget', 'qty': ''}
# Gadget 0
# {'product': 'Gizmo', 'qty': None}
# Gizmo 0An empty cell is an empty string, and int("") stops with ValueError: invalid literal for int() with base 10: '', so the example checks first. A row shorter than the header gets None for the missing values.
Writing CSV files
Writing needs an open file. with open("people.csv", "w", newline="") as f: opens people.csv for writing and closes it when the indented block ends; File Handling covers open() in full, and the next section explains newline="". csv.writer() adds whatever quotes a value needs:
import csv
rows = [["name", "city"], ["Ada", "London, UK"], ["Grace", 'She said "hi"']]
with open("people.csv", "w", newline="") as f:
csv.writer(f).writerows(rows)
with open("people.csv", newline="") as f:
for line in f.read().splitlines():
print(line)
# ✅ Expected output:
# name,city
# Ada,"London, UK"
# Grace,"She said ""hi"""writerow() takes a list. Given a string, it writes each letter as a value: writer.writerow("Ada") writes A,d,a. For dictionaries, csv.DictWriter() takes the column names, and writeheader() writes them as the first line:
import csv
scores = [{"name": "Ada", "score": 95}, {"name": "Grace", "score": 98}]
with open("scores.csv", "w", newline="") as f:
writer = csv.DictWriter(f, fieldnames=["name", "score"])
writer.writeheader()
writer.writerows(scores)
with open("scores.csv", newline="") as f:
for row in csv.DictReader(f):
print(row)
# ✅ Expected output:
# {'name': 'Ada', 'score': '95'}
# {'name': 'Grace', 'score': '98'}The scores come back as strings, as always. A dictionary with a key that isn't one of the column names stops the writer:
import csv
with open("scores.csv", "w", newline="") as f:
writer = csv.DictWriter(f, fieldnames=["name", "score"])
writer.writeheader()
writer.writerow({"name": "Ada", "score": 95, "age": 36})
# ✅ Expected error:
# ValueError: dict contains fields not in fieldnames: 'age'To build CSV text without a file, for example to send it somewhere, write into io.StringIO(), a text file that lives in memory, and take the text out with getvalue(). Checkpoint: A Real-World Script builds its report this way:
import csv
import io
buffer = io.StringIO()
writer = csv.DictWriter(buffer, fieldnames=["name", "stars"], lineterminator="\n")
writer.writeheader()
writer.writerows([{"name": "web-scraper", "stars": 130}, {"name": "notes", "stars": 5}])
print(buffer.getvalue().strip())
# ✅ Expected output:
# name,stars
# web-scraper,130
# notes,5lineterminator="\n" ends each row with a plain line break. Without it, the writer ends rows with "\r\n", the line end the CSV format uses: repr() of the text shows 'name,stars\r\nnotes,5\r\n'.
Why every CSV file is opened with newline=""
The csv module writes its own line ends. Without newline="", Python also turns each "\n" it writes into the computer's own line end, which on Windows is "\r\n", so every row ends "\r\r\n" and reads back with a blank row after it. This example makes Python translate the way Windows does, to show the result here:
import csv
rows = [["name", "score"], ["Ada", "95"]]
# newline="\r\n" makes Python turn each "\n" into "\r\n" as it writes,
# which is what Windows does when newline="" is left out.
with open("scores.csv", "w", newline="\r\n") as f:
csv.writer(f).writerows(rows)
with open("scores.csv", newline="") as f:
for row in csv.reader(f):
print(row)
# ✅ Expected output:
# ['name', 'score']
# []
# ['Ada', '95']
# []Linux and Mac don't translate, so the blank rows only appear on Windows. When reading, newline="" matters everywhere: it keeps a line break inside a quoted value as it was written. So open every CSV file with newline="", for reading and for writing.
Excel files with openpyxl
An .xlsx file isn't text: it's a zip file of XML documents, so the csv module can't read it. The openpyxl package can; install it with pip install openpyxl. It isn't available in this site's editor, so the next two examples only run in Python on a computer, where the build checks their output. Values keep their types, so the units are numbers you can multiply:
from openpyxl import Workbook, load_workbook
wb = Workbook()
sheet = wb.active
sheet.title = "January"
sheet.append(["Region", "Units", "Revenue"])
sheet.append(["North", 120, 48000])
sheet.append(["South", 95, 38000])
wb.save("sales.xlsx")
wb = load_workbook("sales.xlsx")
sheet = wb["January"]
print(sheet["A1"].value)
print(sheet.cell(row=2, column=3).value)
for region, units, revenue in sheet.iter_rows(min_row=2, values_only=True):
print(region, units, revenue, units * 2)
# ✅ Expected output:
# Region
# 48000
# North 120 48000 240
# South 95 38000 190sheet["A1"] is a cell; .value is what's in it. iter_rows(min_row=2, values_only=True) skips the header and gives each row's values as a tuple. openpyxl reads what the file stores, not what Excel shows: a formula comes back as its text, and with data_only=True as the result Excel saved when it last opened the file, None for a file Excel never opened:
from openpyxl import Workbook, load_workbook
wb = Workbook()
sheet = wb.active
sheet.append(["Units", "Price", "Total"])
sheet.append([3, 2.5, "=A2*B2"])
wb.save("order.xlsx")
print(load_workbook("order.xlsx").active["C2"].value)
print(load_workbook("order.xlsx", data_only=True).active["C2"].value)
# ✅ Expected output:
# =A2*B2
# Nonepandas reads a whole CSV or Excel file into a table in one call, read_csv() or read_excel(), and turns the number columns into numbers for you; Reading & Writing Data (CSV, Excel, JSON) in the pandas course shows it.
Practice: pages in a reading list
Fill the three blanks with the values in the hint lines, in order, so that the program totals the pages and finds the longest book, whose title has a comma in it.
import csv
lines = ["title,pages", "Dune,412", '"Emma, a novel",474', "Bee,96"]
rows = list(csv.___(lines))
total = sum(___(r["pages"]) for r in rows)
print(len(rows), "books,", total, "pages")
longest = max(rows, key=lambda r: int(r["pages"]))
print("Longest:", longest["___"])
# 👉 replace ___ with DictReader
# 👉 replace ___ with int
# 👉 replace ___ with title
# ✅ Expected output:
# 3 books, 982 pages
# Longest: Emma, a novelOn your own: a revenue summary
A shop exports its revenue as these lines, separated by semicolons, with decimal commas and one empty cell:
region;month;revenue
North;Jan;1200,50
South;Jan;980,00
North;Feb;
South;Feb;1010,25Total the revenue per region, counting an empty cell as 0, and write summary.csv with the header region,revenue, one row per region in alphabetical order and the amounts to two decimal places. Then print the file. Compare with this solution:
import csv
lines = [
"region;month;revenue",
"North;Jan;1200,50",
"South;Jan;980,00",
"North;Feb;",
"South;Feb;1010,25",
]
totals = {}
for row in csv.DictReader(lines, delimiter=";"):
text = row["revenue"].replace(",", ".")
amount = float(text) if text else 0.0
totals[row["region"]] = totals.get(row["region"], 0.0) + amount
with open("summary.csv", "w", newline="") as f:
writer = csv.writer(f)
writer.writerow(["region", "revenue"])
for region, amount in sorted(totals.items()):
writer.writerow([region, f"{amount:.2f}"])
with open("summary.csv", newline="") as f:
for line in f.read().splitlines():
print(line)
# ✅ Expected output:
# region,revenue
# North,1200.50
# South,1990.25totals.get(region, 0.0) starts each region at zero the first time it appears, and sorted(totals.items()) puts the regions in alphabetical order.
Practice quiz
What does 'Ada,"London, UK",36'.split(",") give?
- ['Ada', 'London, UK', '36']
- ['Ada', '"London, UK"', '36']
- ['Ada', '"London', ' UK"', '36']
- An error
Answer: ['Ada', '"London', ' UK"', '36']. split() knows nothing about quotes, so it cuts at the comma inside them. csv.reader() gives ['Ada', 'London, UK', '36'].
rows = list(csv.DictReader(["name,salary", "Ada,95000", "Grace,98000"])). What does sum(row["salary"] for row in rows) do?
- It stops with TypeError: unsupported operand type(s) for +: 'int' and 'str'
- It gives 193000
- It gives '9500098000'
- It gives 0
Answer: It stops with TypeError: unsupported operand type(s) for +: 'int' and 'str'. Every value from csv is a string, and sum() starts from the number 0. Convert with int() first.
Under the header title,pages, the rows are Dune,412, Bee,96 and Emma,474. What is max(rows, key=lambda r: r["pages"])["title"]?
- Emma
- Bee
- Dune
- An error
Answer: Bee. The pages are text, compared letter by letter, and "9" comes after "4". key=lambda r: int(r["pages"]) gives Emma.
What does list(csv.DictReader(["Ada,95000", "Grace,98000"])) give?
- [{'name': 'Ada', 'salary': '95000'}, {'name': 'Grace', 'salary': '98000'}]
- []
- An error
- [{'Ada': 'Grace', '95000': '98000'}]
Answer: [{'Ada': 'Grace', '95000': '98000'}]. DictReader takes the first line as the column names, so a file without a header loses its first row. Pass fieldnames= to name the columns.
What is list(csv.reader(["name;price", "Widget;9,99"]))[1]?
- ['Widget', '9,99']
- ['Widget;9', '99']
- ['Widget', '9.99']
- ['Widget;9,99']
Answer: ['Widget;9', '99']. The reader splits at commas unless you pass delimiter=";", so it cuts at the decimal comma.
What does a csv writer write for writer.writerow("Ada")?
- Ada
- "Ada"
- A,d,a
- It stops with a TypeError
Answer: A,d,a. writerow() goes through what it's given, and a string gives its letters. Pass a list: writerow(["Ada"]).
A DictWriter with fieldnames=["name", "score"] is given {"name": "Ada", "score": 95, "age": 36}. What happens?
- It stops with ValueError: dict contains fields not in fieldnames: 'age'
- It writes Ada,95
- It writes Ada,95,36
- It adds an age column
Answer: It stops with ValueError: dict contains fields not in fieldnames: 'age'. DictWriter refuses keys that aren't column names. A missing key writes an empty cell instead.
Under the header product,qty, a row holds only Gizmo. What does DictReader give for it?
- {'product': 'Gizmo', 'qty': ''}
- {'product': 'Gizmo'}
- It stops with an error
- {'product': 'Gizmo', 'qty': None}
Answer: {'product': 'Gizmo', 'qty': None}. A missing value at the end of a short row is None. An empty cell, as in Gadget, with a comma, is ''.
Continue this course
- Previous: HTTP Requests & APIs
- Next: Web Scraping with BeautifulSoup — Extract data from web pages with requests and BeautifulSoup
- Quick reference: Python cheat sheet
Frequently asked questions
How do I read a CSV file that's too big for a list?
Loop over the reader instead of calling list() on it. for row in csv.DictReader(f) reads one row at a time, so the whole file is never in memory at once. Working with Files, Streams & Large Datasets covers big files.
Can openpyxl read old .xls files?
No. openpyxl reads only Excel's newer formats: .xlsx, .xlsm, .xltx and .xltm. Given an .xls file, it stops with an InvalidFileException that suggests the two ways round it: read the file with the xlrd package, or save it again as .xlsx.
Why does my first column's name have a strange character in front?
Excel's "CSV UTF-8" format starts the file with an invisible character, the byte order mark. Opened with encoding="utf-8", it becomes part of the first column's name, '\ufeffname', so row["name"] stops with a KeyError. Open the file with encoding="utf-8-sig" and Python drops it.