CSV and spreadsheets

Why this matters

Many systems export tables as CSV files. A bank gives you a list of transactions. A shop exports its orders. A government site publishes a list of schools. A database tool saves query results. Each of these is a plain text file where each line is one row of a table.

CSV is easy to make and easy to read, which is why it is common. It is also easy to break. A comma inside a name, a leading zero in a postal code or a spreadsheet that guesses the wrong type can change the data without any error message. This lesson covers how a CSV file is built, how to read and write one correctly in code, and how to open one in a spreadsheet so that the numbers stay the numbers you saw in the file.

The previous lesson covered JSON, a format for nested data. CSV holds one flat table. It has no nesting, no types and no names beyond the first line.

The idea: rows, commas and a header

CSV stands for comma-separated values. A CSV file is a text file. Each line is one row. Within a row, commas separate the values, which are called fields. The first line is often a header, which names each column.

name,city,score
Asha,Pune,91
Ravi,Delhi,78
Meera,Chennai,85

This file has four lines, so it has four rows: one header and three data rows. Each row has three fields. In the second row, the fields are Asha, Pune and 91.

Every value in a CSV file is text. The file does not say that 91 is a number. The program that reads the file decides what each field means. This is the most important fact in this lesson, and most of the problems below come from forgetting it.

Here is the file read in Python. The csv module is part of Python's standard library, so it needs no install. The io.StringIO wrapper lets this example read from a string as if it were a file. In your own program you would open a real file, as shown later.

import csv
import io
 
text = """name,city,score
Asha,Pune,91
Ravi,Delhi,78
Meera,Chennai,85
"""
 
rows = list(csv.reader(io.StringIO(text)))
print(rows)
print(rows[0])
print(rows[1][1])

Output:

[['name', 'city', 'score'], ['Asha', 'Pune', '91'], ['Ravi', 'Delhi', '78'], ['Meera', 'Chennai', '85']]
['name', 'city', 'score']
Pune

csv.reader returns each row as a list. The header is rows[0], and rows[1][1] is the second field of the first data row. Look at the scores in the output: '91' is in quotes, so it is a string. The reader does not turn it into a number.

Why you cannot just split on commas

Splitting a line on commas is the first idea most people have, and it works for the file above. It fails as soon as a field contains a comma. Here is a row from an order export. The customer's name is Sharma, Meera.

import csv
 
line = '1042,"Sharma, Meera",2,"Said ""thanks""",349.50'
print(line.split(","))
print(next(csv.reader([line])))

Output:

['1042', '"Sharma', ' Meera"', '2', '"Said ""thanks"""', '349.50']
['1042', 'Sharma, Meera', '2', 'Said "thanks"', '349.50']

The naive split produces six pieces, but the row has five fields. The name was cut in two, and the quotes were left in place. The csv reader produces five fields, and it removes the quotes that wrap a field. Always use the reader. Do not write your own split.

The quoting rules

CSV uses double quotes to protect a value that would otherwise be read as a separator. The rules are short.

1. If a field contains a comma, a double quote or a line break, wrap the whole field in double quotes.
2. Inside a quoted field, write each double quote as two double quotes.
3. A field that has none of those characters does not need quotes, and quotes are allowed there too.

In the example above, the field "Said ""thanks""" is the text Said "thanks". The outer pair of quotes marks where the field starts and ends. Each pair of inner quotes stands for one real quote.

A field may also contain a line break. The reader keeps reading the quoted field until it finds the closing quote, even if that takes several lines of the file.

import csv
import io
 
text = 'id,note\n1,"first line\nsecond line"\n2,plain\n'
rows = list(csv.reader(io.StringIO(text)))
print(rows)
print("row count", len(rows))
print("note of row 1:", repr(rows[1][1]))

Output:

[['id', 'note'], ['1', 'first line\nsecond line'], ['2', 'plain']]
row count 3
note of row 1: 'first line\nsecond line'

The text file has four lines, but the reader finds three rows. A row can take up two lines of the file. This is why counting lines with a tool like wc -l does not give you the number of rows when the data holds line breaks.

Writing CSV

When you write a CSV file, use csv.writer. It adds the quotes for you. Write each row as a list of values, and the writer works out where quotes are needed.

import csv
import io
 
out = io.StringIO()
writer = csv.writer(out)
writer.writerow(["id", "name", "note"])
writer.writerow([1, "Sharma, Meera", 'Said "thanks"'])
writer.writerow([2, "Ravi", "plain"])
writer.writerow([3, "Asha", "two\nlines"])
print(out.getvalue(), end="")

Output:

id,name,note
1,"Sharma, Meera","Said ""thanks"""
2,Ravi,plain
3,Asha,"two
lines"

Notice that the number 1 was written without quotes, because it has no special character. The writer converts each value to text as it writes it. The end="" argument in print stops Python from adding a second blank line, because the writer already ends each row with a line break.

Working with a real file

The next program writes a file to disk, prints the text, and then reads it back into a list of dicts. A dict in this context is a row whose keys come from the header. The file goes in the same folder as the script. __file__ is the path of the script itself.

import csv
import os
 
folder = os.path.dirname(os.path.abspath(__file__))
path = os.path.join(folder, "people.csv")
 
with open(path, "w", newline="", encoding="utf-8") as f:
    w = csv.writer(f)
    w.writerow(["name", "note"])
    w.writerow(["Meera", "likes tea, not coffee"])
    w.writerow(["Ravi", 'has a "nickname"'])
 
with open(path, newline="", encoding="utf-8") as f:
    print(f.read(), end="")
 
with open(path, newline="", encoding="utf-8") as f:
    for row in csv.DictReader(f):
        print(row["name"], "|", row["note"])
 
os.remove(path)

Output:

name,note
Meera,"likes tea, not coffee"
Ravi,"has a ""nickname"""
Meera | likes tea, not coffee
Ravi | has a "nickname"

Two details matter here. First, newline="" is passed to open both times. The csv module handles line breaks itself, and this argument tells Python not to change them. Without it, quoted line breaks and some line endings can be read wrongly, as the mistakes section shows. Second, csv.DictReader uses the first line as the keys, so row["note"] reads the column called note. You do not need to count column positions.

The os.remove(path) line deletes the file when the program ends. The program creates the file only so that it can read it back, so it cleans up after itself.

A file with semicolons

In some countries, spreadsheets save CSV files with semicolons between fields, because the comma is used as a decimal mark there. The number 1,5 means one and a half in those countries. The reader needs to know which separator the file uses.

import csv
import io
 
semi = "name;amount\nMeera;1,5\nRavi;2,25\n"
rows = list(csv.reader(io.StringIO(semi), delimiter=";"))
print(rows)
print("wrong delimiter:", next(csv.reader(io.StringIO(semi))))

Output:

[['name', 'amount'], ['Meera', '1,5'], ['Ravi', '2,25']]
wrong delimiter: ['name;amount']

With the right delimiter, each row has two fields. With the default comma, the whole line is one field. The reader does not guess the separator. If your file has a surprising number of columns, look at the first line in a text editor and check which character separates the values.

Rows that do not match the header

A row can have more or fewer fields than the header. csv.DictReader does not stop. It fills the gaps in a way that is easy to miss.

import csv
import io
 
bad = "name,age,city\nMeera,30\nRavi,25,Pune,extra\n"
for row in csv.DictReader(io.StringIO(bad)):
    print(row)

Output:

{'name': 'Meera', 'age': '30', 'city': None}
{'name': 'Ravi', 'age': '25', 'city': 'Pune', None: ['extra']}

The first row is missing its city, so the value is None. The second row has an extra value, which DictReader stores under the key None as a list. Nothing failed, so the problem will show up later as a wrong total or a crash in the code that uses the data. Check the length of each row in your code when the data comes from outside.

Leading zeros and spreadsheets

A postal code such as 01234 is a string of digits. Its leading zero matters. Removing the zero gives a different code, and a letter carrier would send the mail to the wrong place. The problem is that a spreadsheet tries to be helpful.

Here is a file with a postal code that starts with zero.

import csv
import io
 
zip_text = "name,zip\nMeera,411001\nRavi,01234\n"
for row in csv.DictReader(io.StringIO(zip_text)):
    print("as text:", row["name"], repr(row["zip"]))
for row in csv.DictReader(io.StringIO(zip_text)):
    print("as number:", row["name"], int(row["zip"]))

Output:

as text: Meera '411001'
as text: Ravi '01234'
as number: Meera 411001
as number: Ravi 1234

Reading the text gives '01234', which is correct. Converting it to a number with int gives 1234, and the zero is gone. A spreadsheet does the same conversion when it opens the file. With its default settings, Excel and Google Sheets look at each cell, decide that it looks like a number, and store it as a number. The file on disk still says 01234. The zero is lost when the spreadsheet shows the cell, and it is lost for good once you save the spreadsheet.

Quotes do not help. If the file holds "01234" in quotes, the spreadsheet still reads the value as a number when it opens the file. The quotes are removed by the CSV rules, and the value that remains is 01234, which the spreadsheet then converts.

Three ways to keep the zero:

• Open the file with the import dialog, and set the column that holds the code to Text. The dialog is part of the spreadsheet's CSV import, and it is the reliable way to keep the value.
• Use a text editor or a program to read the file first, then copy the codes into the spreadsheet as text.
• If the zeros are already gone, the file you have is damaged. Ask for a fresh export, or check the original source. You cannot recover the zero from the number alone.

The same conversion affects long numbers such as card numbers or bank account numbers. A spreadsheet keeps about 15 significant digits, so a 19-digit number can come out with its last digits rounded or replaced by zeros. Treat any identifier as text from the start.

Opening CSV safely

Opening a CSV file is safe in the sense that it does not run anything. The risk is in what a spreadsheet does with the values. The steps below cover the common mistakes.

1. Look at the file in a text editor before you open it in a spreadsheet. Check the first line for the header, check the separator, and check whether codes have leading zeros.
2. Use the import dialog, not a double click, when the file has a special separator, a long identifier or a column that must stay text.
3. Save the spreadsheet under a new name. Do not save over the original export, because the spreadsheet may have changed the values as soon as you opened it.
4. Be careful with values that start with =, +, - or @. A spreadsheet can read such a cell as a formula, and a formula can do things such as a HYPERLINK that sends the reader to a web address they did not expect. This is called CSV injection. Security guidance for CSV export recommends putting a single quote in front of any value that starts with one of these characters, so that the cell is stored as text.

Here is a small helper that adds that quote. It belongs in the code that writes the file, and it should be used only for text columns, because a negative number such as -5 is a value you may need as a number.

RISKY_START = ("=", "+", "-", "@")
 
def make_safe(value):
    text = str(value)
    if text.startswith(RISKY_START):
        return "'" + text
    return text
 
for value in ['=HYPERLINK("http://example.com")', "+1 555 0100", "-5", "@SUM(A1)", "normal"]:
    print(make_safe(value))

Output:

'=HYPERLINK("http://example.com")
'+1 555 0100
'-5
'@SUM(A1)
normal

Some spreadsheets show the leading quote, and some hide it. A program that reads the file back will see it as part of the value. Decide which behaviour you want before you use the helper.

Common mistakes

Splitting on commas. Covered above. line.split(",") breaks any field that contains a comma. Use csv.reader.

Opening a file without newline="". The csv module needs to see the line breaks itself. If a row holds a carriage return character, which is a line break from some older systems, the reader stops with this message:

csv.Error: new-line character seen in unquoted field - do you need to open the file with newline=''?

The code that produced that output reads text directly:

import csv
import io
 
print(list(csv.reader(io.StringIO("a,b\rc,d\n"))))

The message is clear about the cure. Open the file with newline="" in the open call, as the earlier examples do.

An unclosed quote. If a field starts with a quote and the closing quote is missing, the reader keeps reading. It treats everything after the quote as part of one field, including the rows below it. This input has the mistake in the second row.

import csv
import io
 
data = 'name,note\nMeera,"forgot the end quote\nRavi,ok\nAsha,ok\n'
rows = list(csv.reader(io.StringIO(data)))
print("row count:", len(rows))
for row in rows:
    print(row)

Output:

row count: 2
['name', 'note']
['Meera', 'forgot the end quote\nRavi,ok\nAsha,ok\n']

Only two rows were found, and Ravi and Asha are inside Meera's note. Nothing failed. The reader has a strict option that stops at this kind of problem:

import csv
import io
 
data = 'name,note\nMeera,"forgot the end quote\nRavi,ok\nAsha,ok\n'
try:
    list(csv.reader(io.StringIO(data), strict=True))
except csv.Error as err:
    print("csv.Error:", err)

Output:

csv.Error: unexpected end of data

Use strict=True when a broken file must not pass quietly. The message does not say which line, so compare the row count with the number of rows you expected.

Assuming every value is a number. The reader returns text. "91" + 1 is an error in Python, and int(row["score"]) + 1 is the correct way to add one. If you forget the conversion, you may get a wrong total in a spreadsheet formula or a crash in your code, depending on where you do the arithmetic.

Forgetting the header. csv.reader returns the header as the first row. If you loop over every row and add up the first column, the header text is included. Skip it with next(reader) or use csv.DictReader, which uses the header for you.

Wrong delimiter, wrong decimal mark. A semicolon file read as comma-separated gives one field per line. A file with 1,5 read as a comma file gives two fields. Check the separator first.

Practice

Exercise 1. Write CSV text for three students with the columns name and note. One note is likes maths, and python. Another note is said "hello". The third note is plain. Use the quoting rules to make the text valid.

Show the answer

name,note
Asha,"likes maths, and python"
Ravi,"said ""hello"""
Meera,plain

The first note has a comma, so it is in quotes. The second has a double quote, so it is in quotes and each inner quote is doubled. The third needs no quotes. A check with csv.reader returns three data rows with the notes unchanged.

Exercise 2. The line 1,"Lee, Ann",90 is one row. How many fields does it have, and what is the second field?

Show the answer

It has three fields: 1, Lee, Ann and 90. The second field is Lee, Ann, with the comma kept, because the quotes tell the reader that the comma is part of the value. A naive split on commas would give four pieces.

Exercise 3. Write a Python program that reads the CSV text below with a header and prints the average score, rounded to one decimal place.

text = "name,score\nAsha,91\nRavi,78\nMeera,85\n"

Show the answer

import csv
import io
 
text = "name,score\nAsha,91\nRavi,78\nMeera,85\n"
scores = [int(row["score"]) for row in csv.DictReader(io.StringIO(text))]
print(round(sum(scores) / len(scores), 1))

Output:

84.7

The scores are read as text, so each one is converted with int before the arithmetic. The sum is 254, and 254 divided by 3 is 84.67, which rounds to 84.7.

Exercise 4. A file has the row Ravi,01234. The spreadsheet shows 1234 in that cell. Explain why, and name two things you could do to keep the zero.

Show the answer

The spreadsheet read 01234 as text that looks like a number, converted it to the number 1234, and dropped the leading zero when it displayed the value. Two ways to keep it are to import the file with the postal code column set to Text, and to read the file in a text editor or a program and copy the code across as text. Adding quotes around 01234 in the file does not stop the conversion.

Exercise 5. Which of these cell values could be read as a formula by a spreadsheet: =1+1, Ravi, -, @team? Write a short Python check that prints the answer.

Show the answer

=1+1, - and @team can be read as formulas, because they start with =, - or @. Ravi cannot. The check is:

RISKY_START = ("=", "+", "-", "@")
 
for value in ["=1+1", "Ravi", "-", "@team"]:
    print(repr(value), value.startswith(RISKY_START))

Output:

'=1+1' True
'Ravi' False
'-' True
'@team' True

Summary

• A CSV file is text, with one row per line, commas between fields, and an optional header line. Every value is text until your code converts it.
• Read and write CSV with the csv module, never by splitting on commas. Use csv.DictReader when the first line holds the column names.
• Wrap a field in double quotes if it contains a comma, a quote or a line break, and double each quote inside it.
• Spreadsheets convert values that look like numbers, which drops leading zeros and rounds long numbers. Use the import dialog and set those columns to Text.
• Open files with newline="" in Python, and check the separator and the number of fields per row.
• Keep the original export. Treat values that start with =, +, - or @ as a risk when they go into a spreadsheet.