CSV splitting in Python

Why it matters

A lot of real data arrives as plain text, one record per line. A spreadsheet can save itself as a CSV file. A bank can send a list of payments in CSV. A shop can export its orders as CSV. CSV stands for comma-separated values. Each line is a record, and the commas separate the fields inside the record. A field is one piece of information, such as a name or an age.

Splitting a line into fields is the first step of almost every data job. The obvious tool is str.split(","), and for simple data it works well. The trouble starts with real data. A name can contain a comma. A field can be empty. A program that uses the obvious tool then reads the wrong number of fields, and every later step works on shifted values. The age ends up in the name column, and nothing crashes, so the mistake can stay hidden for a long time.

This lesson shows how a plain split works, where it fails, and how to write a small parser that handles quoted fields. The parser uses only strings, a list and a loop. In real work you would use the standard library's csv module, which handles the same rules, and the end of the lesson shows it. The next lesson in this module covers searching for a pattern inside text, which is a different job from splitting a line.

The idea: a comma ends a field

Take one line of data:

Ada,36,London

There are two commas, so there are three fields. The first field is Ada, the second is 36 and the third is London. Reading the line from left to right, you can cut it at each comma. The cut pieces are the fields.

In Python, split does this cutting for you:

line = "Ada,36,London"
fields = line.split(",")
print(fields)
print("count:", len(fields))

The output is:

['Ada', '36', 'London']
count: 3

split returns a list of strings. The argument "," is the text to cut at. Unlike some languages, Python does not read it as a pattern, so a comma here means only a comma. Searching for a pattern with more than one possible form is a different job, and the regex basics lesson later in this module covers it.

Each field is a string. The number 36 is still text. If you need the number, you convert it later with int(). Keep the fields as text until you need the number.

Empty fields

An empty field is a field with nothing in it. In a line like Ada,,London, the two commas have nothing between them, so the middle field is empty. An empty field is still a field, and a correct split must keep it. The middle value is an empty string, which is not the same as None. None means "no value at all". An empty string means "a value that has no characters".

Here is the same idea at the end of a line, and at the start:

print("Ada,,London".split(","))
print("Ada,36,".split(","))
print(len("Ada,36,".split(",")))
print(",Ada".split(","))

The output is:

['Ada', '', 'London']
['Ada', '36', '']
3
['', 'Ada']

The middle empty field is kept, so Ada,,London gives three fields. The ending case also keeps its last field. Ada,36, has three fields, and the last one is empty. Python's split keeps every empty field, at the start, in the middle and at the end. This is a difference from some other languages, where the plain split removes empty fields at the end. In Python the field count always matches the number of commas plus one, so the trailing -1 argument that other languages need is not needed here.

Two more edge cases are worth knowing, because they surprise almost every beginner.

print("".split(","), len("".split(",")))
print(",,".split(","), len(",,".split(",")))

The output is:

[''] 1
['', '', ''] 3

An empty line gives one field, which is the empty string, because there is no comma to cut at. A line with two commas gives three empty fields, which matches the three columns the line describes. The rule to remember is simple: n commas give n + 1 fields, whatever the content.

Where the plain split breaks: quoted fields

Now take a line from a real file, where a name contains a comma:

"Smith, John",42,Leeds

The data has three fields: Smith, John, 42 and Leeds. The name is wrapped in double quotes, and the comma inside the quotes is part of the name. The quotes tell the reader that the comma does not end the field.

The plain split does not know about quotes. It cuts at every comma:

line = '"Smith, John",42,Leeds'
fields = line.split(",")
print(fields)
print("count:", len(fields))

The output is:

['"Smith', ' John"', '42', 'Leeds']
count: 4

The program now sees four fields instead of three. The first field is "Smith with a stray quote at the front, and the second is John" with a stray quote at the end. Every later column is shifted by one. If the program reads the age from index 1, the second column, it gets the text John" and tries to turn it into a number. Python raises ValueError: invalid literal for int() with base 10: ' John"' at that point, far away from the real cause. The error message names the bad text, but not the line of the file that caused it.

The same problem appears with a quote inside a field. In CSV, a double quote inside a quoted field is written as two double quotes side by side. The text He said "hi" is written as "He said ""hi""". The plain split cuts at the commas and leaves the quotes in place, so the result is still wrong.

Idea in plain words: walk the line with two flags

A parser is a program that reads text and understands its structure. For CSV, the parser walks the line one character at a time and keeps track of two things.

• A flag called in_quotes says whether the walk is currently inside a quoted part. It starts as False.
• A list called current collects the characters of the field that is being read now. It is the Python version of a builder: you append characters to it, and join them into a string when a field ends.

The rules are short.

• Outside quotes, a double quote turns in_quotes on and adds nothing to the field.
• Outside quotes, a comma ends the field. Save the field, then clear current.
• Outside quotes, any other character is added to current.
• Inside quotes, two double quotes in a row stand for one double quote in the value. Add one " to current and skip the second quote.
• Inside quotes, a single double quote closes the quoted part. Turn in_quotes off and add nothing.
• Inside quotes, any other character, including a comma, is added to current.
• At the end of the line, save current as the last field. The last field has no comma after it, so the loop never saves it on its own.

Read the rules once more and check them against the line "Smith, John",42,Leeds. The first character is a quote, so in_quotes turns on. The letters Smith are added. The comma is inside quotes, so it is added too. The space and John are added. The quote closes the quoted part. The comma after the quote is outside quotes, so it ends the field, and Smith, John is saved. Then 42 is read and saved at the next comma, and Leeds is saved at the end of the line.

The full parser

Here is the parser in full. It is the function that produced the outputs in the examples below.

def split_csv_line(line):
    fields = []
    current = []
    in_quotes = False
    i = 0
    while i < len(line):
        c = line[i]
        if in_quotes:
            if c == '"':
                if i + 1 < len(line) and line[i + 1] == '"':
                    current.append('"')
                    i += 1
                else:
                    in_quotes = False
            else:
                current.append(c)
        else:
            if c == '"':
                in_quotes = True
            elif c == ",":
                fields.append("".join(current))
                current.clear()
            else:
                current.append(c)
        i += 1
    fields.append("".join(current))
    return fields
 
print(split_csv_line('"Smith, John",42,Leeds'))

The output is:

['Smith, John', '42', 'Leeds']

The function returns a list. A list grows as fields are added, so it works when the number of fields is not known in advance. The current list is a list of single characters. "".join(current) makes one string from them, and current.clear() empties the list so that the next field starts from nothing. Without it, the characters of one field would run into the next.

The loop uses i as an index into the line, and i += 1 at the end moves to the next character. The condition if i + 1 < len(line) and line[i + 1] == '"' looks one character ahead. It is needed for the doubled quote. When the parser sees a quote inside quotes, it checks whether the next character is also a quote. If so, it adds one quote and moves past both. The i += 1 inside that branch moves onto the second quote, and the i += 1 at the bottom of the loop moves past it.

The "".join(current) call is also what makes the string. Python strings cannot be changed in place, so the parser collects characters in a list and builds each string once, which is the pattern from the second lesson.

Worked example 1: a simple line

Start with the easiest case, a line with no quotes at all:

print(split_csv_line("Ada,36,London"))

The output is:

['Ada', '36', 'London']

The walk never sees a quote, so in_quotes stays False the whole time. Each comma saves the current field. The last field is saved after the loop ends. The result matches the plain split, which is what you want for simple data. The parser is not much harder to use for ordinary lines.

Worked example 2: quotes inside a quoted field

Now the harder case, with a doubled quote. In a Python string literal, a double quote inside single quotes needs no escape, so the text is written as shown:

print(split_csv_line('"He said ""hi""",7,x'))

The output is:

['He said "hi"', '7', 'x']

Trace the first field. The first character is a quote, so in_quotes turns on. The letters He said are added. Then the walk meets ", and the next character is also ". That is a doubled quote, so one " is added and the walk skips the second one. The text hi is added. Then three quotes come after hi. The first two form a doubled quote, so one " is added. The third quote is followed by a comma, not a quote, so it closes the quoted part. The comma then ends the field. The saved field is He said "hi", which is the value the file means.

Worked example 3: a file with a header and a check on the field count

Real files have a header line, which names the columns. A good habit is to check that every row has the same number of fields as the header. A row with the wrong count is a sign of bad data, and it should be reported rather than silently used.

The next program reads four rows and checks each one. It uses split_csv_line from the parser above:

def show(fields):
    quoted = ", ".join('"' + f + '"' for f in fields)
    return f"[{quoted}] count={len(fields)}"
 
text = 'name,age,city\nAda,36,London\n"Smith, John",42,Leeds\nLin,,Paris'
rows = text.split("\n")
expected = len(split_csv_line(rows[0]))
for row in rows:
    fields = split_csv_line(row)
    status = "ok" if len(fields) == expected else "WRONG COUNT"
    print(status, show(fields))

The output is:

ok ["name", "age", "city"] count=3
ok ["Ada", "36", "London"] count=3
ok ["Smith, John", "42", "Leeds"] count=3
ok ["Lin", "", "Paris"] count=3

The header has three names, so expected is 3. Each data row is checked against it. The row Lin,,Paris has an empty age, and the parser keeps that empty field, so the count is still 3 and the row is accepted. A row with a missing column would show WRONG COUNT. The check is cheap, and it catches shifted columns early.

The program splits the whole text on "\n", which is the newline character. A newline is a character that ends a line. This works here because no field in the example contains a line break. Real CSV files can have a line break inside a quoted field. This lesson handles one line at a time, so a field with a line break needs a parser that keeps reading past the end of the line. The csv module below does that.

The show function is only for printing. It wraps each field in double quotes, so an empty field shows as "" and a field with spaces is easy to see. Printing with quotes is a good debugging habit when a value looks right but is not.

Compare the same rows with the plain split:

for row in rows:
    print("naive split count", len(row.split(",")), "for:", row)

The output is:

naive split count 3 for: name,age,city
naive split count 3 for: Ada,36,London
naive split count 4 for: "Smith, John",42,Leeds
naive split count 3 for: Lin,,Paris

Only the quoted row is wrong, and it is wrong in a silent way. The program would keep going with four fields for a row that has three. The parser reads the same row as three fields, so the count check passes it.

The csv module

The standard library has a module that does everything the parser above does, and more. For real files, use it:

import csv
 
print(next(csv.reader(['"Smith, John",42,Leeds'])))
print(next(csv.reader(['"He said ""hi""",7,x'])))
print(list(csv.reader(['Ada,36,'])))

The output is:

['Smith, John', '42', 'Leeds']
['He said "hi"', '7', 'x']
[['Ada', '36', '']]

csv.reader takes an iterable of lines, such as an open file, and returns one list of fields for each record. It handles quoted commas, doubled quotes and empty fields the same way the parser does. The line Ada,36, gives three fields, with the last one empty.

The csv module also reads past a line break inside a quoted field, which the parser in this lesson does not do. For example:

import csv
import io
 
text = 'name,age\n"Line one\nline two",3\nLin,,Paris\n'
rows = list(csv.reader(io.StringIO(text)))
print(rows)
print(len(rows))

The output is:

[['name', 'age'], ['Line one\nline two', '3'], ['Lin', '', 'Paris']]
3

The field with the line break is one field, and the file has three records. The io.StringIO object works like an open file, so you can test the reader without creating a file. When you read a real file, open it with newline="", as in open("data.csv", newline="", encoding="utf-8"), so the module sees the line breaks inside quoted fields exactly as they are.

Common mistakes

Trusting the plain split on data that may contain quotes. The most common error is using split(",") on a file that a person or another program wrote. It works on the first thousand rows and fails on the row where a name has a comma. The fix is to use the csv module for any file that may contain quotes. A quick test is to search the file for a double quote character. If it has one, the plain split is not safe.

Expecting trailing empty fields to disappear. Python keeps them, as the examples above show. A program written for a language that drops them may check for the wrong count. Test the row Ada,36, and check that you get three fields.

Forgetting the last field. The loop saves a field when it sees a comma. The last field has no comma after it, so it is saved only by the line after the loop, fields.append("".join(current)). If that line is missing, every line loses its last column. Test the parser on a line that ends in a value, not only on a line that ends in a comma.

Forgetting to clear the list. The line current.clear() empties the list after a field is saved. If it is removed, the characters of the next field are added after the characters of the old one. The second field would then be Ada36 instead of 36, and each later field would repeat all the text before it. The list is reused for every field, so it must start empty each time.

Expecting spaces to be removed. The parser keeps spaces exactly as they are in the line. Earlier, the quoted name showed up as Smith, John with the space kept, and a line like Ada, 36 gives the field 36 with a space in front. Some files add a space after each comma. If your data does, strip each field with strip() after the parser has split it. Do not strip inside the parser, because a quoted field can start or end with spaces that are part of the value.

Expecting one line to contain a whole record. The parser in this lesson reads one line. A quoted field with a line break in it is split across two lines of the file. Reading the file line by line, the parser sees an unclosed quote at the end of the first line. The fix is the csv module, which reads past the line break while the quote is open.

Reading past the end of the line. The doubled-quote check looks one character ahead. Without the check i + 1 < len(line), a line that ends in a quote would raise IndexError: string index out of range. The condition is short, but it is needed. Every time you look at i + 1, check that it is still inside the line first.

Practice

1. For the line a,b,,c,, how many fields does split(",") return? List the fields.

Show the answer

split(",") returns 5 fields: a, b, the empty string, c and a final empty string. There are four commas, so there are five fields. The last empty string is the field after the last comma.

2. Write a function count_fields(line) that returns how many fields the parser finds in a line. Use split_csv_line from this lesson. Test it on "Lee, Ann",,"x".

Show the answer

def count_fields(line):
    return len(split_csv_line(line))
 
print(count_fields('"Lee, Ann",,"x"'))

The output is 3. The fields are Lee, Ann, the empty string and x. The comma inside the first quotes does not split the field, and the empty field between the two commas is kept.

3. Predict the fields that split_csv_line returns for the line "a""b",,c. Write each field in double quotes so spaces and empty values are visible.

Show the answer

The first field is a"b, because the doubled quote inside the quotes becomes one quote. The second field is the empty string, written "". The third field is c. So the list is "a"b", "" and "c", which is three fields in total. Running the function prints ['a"b', '', 'c'].

4. Explain in your own words why "".split(",") gives one field and ",,".split(",") gives three. Then say which result is more useful for a CSV line that should have three columns and holds no data.

Show the answer

The empty string has no commas, so the split finds nothing to cut and returns the whole string, which is one empty piece. The line ,, has two commas, so it is cut into three pieces, all empty. The rule is that n commas give n + 1 fields. For a line that should have three columns, the result ['', '', ''] is the useful one, because the count matches the header. The one-field answer for the empty line would make a row of three columns look like a row with one column.

5. Change split_csv_line so that it raises a ValueError when a line ends while a quoted part is still open. For example, "open,1,2 should cause an error. Show the change and the message it prints.

Show the answer

Add this check after the loop and before the last fields.append line:

if in_quotes:
    raise ValueError("unclosed quote in: " + line)

Calling the function on "open,1,2 raises a ValueError with the message unclosed quote in: "open,1,2. The check is needed because a missing closing quote means the file is damaged, and the parser would otherwise return fields that are wrong without any warning. Note that the csv module does not raise here. It returns the text as it is, so check the result of csv.reader when your data must be exact.

Summary

• A CSV line is a record, and its commas separate the fields. The plain split(",") cuts at every comma, including commas inside quotes.
• Python's plain split keeps empty fields at the start, in the middle and at the end. n commas always give n + 1 fields.
• A parser walks the line one character at a time, with an in_quotes flag and a list of characters for the current field.
• A doubled quote inside a quoted field stands for one quote in the value. A single quote closes the quoted part.
• The last field is saved after the loop, so the parser must add it at the end.
• For real files, use the csv module. It reads quoted commas, doubled quotes and line breaks inside quoted fields.
• Check each row's field count against the header, and report rows with the wrong count instead of using them silently.