esc
  • WorkflowsPages
  • Case studiesPages
  • Machine Learning FoundationsLearn
  • AI EngineeringLearn
  • AI Guardrails & Red TeamingLearn
  • UI/UXLearn
  • Design SystemsLearn
  • Production System DesignLearn
  • Book a callContact
  • Download ResumeContact
Let’s talk

I’ll build the agent for it.

Machine Learning Foundations / Beginner / 01.47

CSV files, up close

A CSV file is only text. Learn the rules, the quirks and how to read and write one safely with Python.

14 min readFree

Why look closer at CSV

In the last lesson you met the main data formats. CSV is the one you will open most often as a student. It is a plain text file that holds a table. Most database systems can export one, and most spreadsheet programs can import one.

Small details trip people up: a comma inside a name, a strange first character, a number that is really text. This lesson shows the rules, where real files differ, and how to read and write them with Python.

Key words

CSV
Comma-separated values: a plain text file where each line is one row of a table and commas split the row into cells.
Record
One row of the table. In a CSV file, one record sits on one line (with one exception you will meet soon).
Field
One cell inside a record.
Header
An optional first line that holds the names of the columns.
Delimiter
The character that splits one field from the next. In CSV it is a comma.
Encoding
The rule that turns letters into bytes when text is saved to a file, and back again when it is read.
BOM
Byte order mark: a few special bytes some programs put at the very start of a text file.
Dialect
A set of small choices, such as which delimiter and which quote character a CSV file uses.

What the format says

The official description is a short document called RFC 4180. An RFC (Request for Comments) is a public note from the people who work on internet standards. This one is only informational. It says: 'This memo provides information for the Internet community. It does not specify an Internet standard of any kind.' It also admits that 'there is no formal specification in existence, which allows for a wide variety of interpretations of CSV files.' So it writes down 'the format that seems to be followed by most implementations'.

Python's documentation agrees: the lack of a standard 'means that subtle differences often exist in the data produced and consumed by different applications.' A CSV file is a habit more than a law.

Here are the main rules from RFC 4180, in plain words.

  • Records and fields

    'Each record is located on a separate line, delimited by a line break (CRLF).' Inside a record, fields are 'separated by commas'. Each line 'should contain the same number of fields throughout the file.'

  • An optional header

    'There maybe an optional header line appearing as the first line of the file', with the names of the fields. The file itself does not say whether a header is there, so you have to know.

  • Spaces count

    'Spaces are considered part of a field and should not be ignored.' 'Mina' and ' Mina' are two different values. The last field on a line must not be followed by a comma.

The RFC says the text/csv type 'uses CRLF to denote line breaks' (carriage return, then line feed, written \r\n in Python), but warns that 'some implementations may use other values.' Python's csv module copes with both, if you open the file the right way (more below).

Quotes, commas and line breaks

What if a value contains a comma? Then the comma would look like a split. The RFC's answer: 'Fields containing line breaks (CRLF), double quotes, and commas should be enclosed in double-quotes.' The quotes tell the reader: everything up to the closing quote is one field.

What if the value itself contains a double quote? The RFC says: 'a double-quote appearing inside a field must be escaped by preceding it with another double quote.' So a quote inside a field is written twice. Here is a small file that has both problems, and a line break inside a quoted field.

Text
name,note
"Mina","She said ""hi"", then left"
"Joe","Line one
Line two"

Read it as text: the first record's note is a quoted field. It holds a comma and two doubled quotes. The second record's note holds a real line break. That is the exception to 'one record, one line': a line break inside quotes belongs to the field, not to the table. Here is how Python's csv module parses it. The sample text lives in an io.StringIO object, which lets us treat a string as if it were a file. The \r\n and \n inside the string are the line breaks.

Python
import csv
import io

text = 'name,note\r\n"Mina","She said ""hi"", then left"\r\n"Joe","Line one\nLine two"\r\n'
for row in csv.reader(io.StringIO(text, newline="")):
    print(row)

Output

['name', 'note']
['Mina', 'She said "hi", then left']
['Joe', 'Line one\nLine two']

Python gave back clean values: the doubled quotes became single, the comma stayed, and Joe's note keeps its line break, shown as \n. The quote marks around the fields are gone; they were only packaging.

Dialects: semicolons and tabs

Some files do not use commas at all. Wikipedia notes that Microsoft Excel supports 'a dialect of CSV', and that, as of 2019, its separator 'is not enforced to be the comma'. A semicolon is one choice you may meet. Another is the tab character. A file split by tabs is often called TSV, for tab-separated values. The RFC itself notes that a type for 'text/tab-separated-values' was already registered before CSV got one.

The csv module calls these differences dialects. Its documentation says 'The excel class defines the usual properties of an Excel-generated CSV file' and 'The excel_tab class defines the usual properties of an Excel-generated TAB-delimited file.' The reader's delimiter defaults to a comma, so for a semicolon file you pass delimiter=';'. For a tab file you can name the dialect. See what happens when we forget:

Python
import csv
import io

semi = "name;price\nTea;3\nCoffee;4\n"
tabs = "name\tprice\nTea\t3\nCoffee\t4\n"

print(list(csv.reader(io.StringIO(semi), delimiter=";")))
print(list(csv.reader(io.StringIO(tabs), dialect="excel-tab")))
print(list(csv.reader(io.StringIO(semi))))

Output

[['name', 'price'], ['Tea', '3'], ['Coffee', '4']]
[['name', 'price'], ['Tea', '3'], ['Coffee', '4']]
[['name;price'], ['Tea;3'], ['Coffee;4']]

The first two lines are correct tables. The third shows the mistake: with the default comma, the whole line stays as one field, 'name;price'. If your data shows up as one fat column, wrong delimiter is the first thing to check.

Encodings and the BOM

A file is bytes. An encoding is the rule for turning letters into bytes and back. Wikipedia says CSV 'is not limited to a particular character encoding' but 'should be and is commonly used with UTF-8, particularly because it does not provide a way to indicate the character encoding.' In other words, the file cannot tell you which encoding it uses. You have to find out. The most common choice is UTF-8, and Python's open() decodes with UTF-8 by default.

Now the odd part. Some programs put a BOM at the start of a file. Wikipedia describes it as 'a particular usage of the special Unicode character code, U+FEFF'. In UTF-8 it is the three bytes EF BB BF. Its only use there is to signal that the text is UTF-8, and the Unicode Standard 'does not require or recommend its use'.

Python knows this trick. Its codecs documentation says Microsoft invented a variant of UTF-8, 'that Python calls "utf-8-sig"', for Notepad. On decoding, utf-8-sig 'will skip those three bytes if they appear as the first three bytes in the file'. Let's write a file with a BOM (in a temporary folder) and read it two ways.

Python
import csv
import os
import tempfile

folder = tempfile.mkdtemp()
path = os.path.join(folder, "names.csv")

with open(path, "w", newline="", encoding="utf-8-sig") as f:
    csv.writer(f).writerows([["name", "city"], ["Zoë", "Köln"]])

with open(path, "rb") as f:
    print(f.read(3))

with open(path, newline="", encoding="utf-8") as f:
    print(next(csv.reader(f)))

with open(path, newline="", encoding="utf-8-sig") as f:
    print(next(csv.reader(f)))

Output

b'\xef\xbb\xbf'
['\ufeffname', 'city']
['name', 'city']

The first line shows the three BOM bytes. Read as plain utf-8, the BOM sticks to the first column name: '\ufeffname'. It is invisible when printed, yet row['name'] fails with a KeyError. Read as utf-8-sig, the name is clean.

Quick check

A file looks fine, but row['name'] raises a KeyError. The printed header shows '\ufeffname'. What is the likely fix?

Tip

Python's docs say that in UTF-8 'the use of the BOM is discouraged and should generally be avoided.' When you write files, plain utf-8 is the usual choice.

Everything is text

Here is the most important idea for machine learning. A CSV file stores only text. It has no way to say 'this column is a whole number' or 'this one is a date'. The csv module respects that. Its docs say 'Each row read from the csv file is returned as a list of strings. No automatic data type conversion is performed unless the QUOTE_NONNUMERIC format option is specified.' That option turns unquoted fields into floats, which is too blunt for most real files, so we convert by hand.

Models need numbers, so convert each value on purpose: int() for whole numbers, float() for decimals. A bad value then fails loudly, which beats a silent wrong answer.

Missing values

Real tables have gaps: a customer who left the age blank, a sensor that did not report. A CSV file shows a gap as an empty field, such as the two commas in Bo,,Oslo. Wikipedia notes that CSV 'makes no distinction between null and empty values', so the file cannot say whether a gap means 'unknown', 'none' or 'not applicable'. That is a question for whoever made the data.

The csv module's DictReader also covers a row that is too short. Its docs say: 'If a non-blank row has fewer fields than fieldnames, the missing values are filled-in with the value of restval (which defaults to None).' Look at the difference between an empty field and a missing one:

Python
import csv
import io

text = "name,age,city\nAda,36,Leeds\nBo,,Oslo\nCy,29\n"
for row in csv.DictReader(io.StringIO(text)):
    print(row)

Output

{'name': 'Ada', 'age': '36', 'city': 'Leeds'}
{'name': 'Bo', 'age': '', 'city': 'Oslo'}
{'name': 'Cy', 'age': '29', 'city': None}

Bo's age is '', an empty string, because the field was there but empty. Cy's city is None, because the row ended early and there was no field at all. Both mean 'no value' to you, but your code must check for both.

Reading and writing with the csv module

The module is part of Python's standard library, so there is nothing to install. The docs describe it as classes 'to read and write tabular data in CSV format'. There are four you will use. csv.reader gives each row as a list. csv.DictReader gives each row as a dictionary keyed by the header. csv.writer writes lists. csv.DictWriter writes dictionaries.

An earlier lesson showed a first DictReader read. Two details matter now. First, per the docs, if you leave out fieldnames, 'the values in the first row of file f will be used as the fieldnames and will be omitted from the results.' If your file has no header, pass fieldnames yourself, and then the first row 'will be included in the results.'

Second, the way you open the file. The docs say a CSV file object 'should be opened with newline="".' A footnote adds: 'If newline="" is not specified, newlines embedded inside quoted fields will not be interpreted correctly, and on platforms that use \r\n line endings on write an extra \r will be added.' So always pass newline="" when you read or write a CSV file.

Writing a CSV file

The writer adds quotes for you. By default (QUOTE_MINIMAL) it quotes only fields that hold a special character such as the delimiter, a quote, or a line break. Here a name has a comma, a note has quotes and another has a line break. The ids start with zeros, so we keep them as text. The last line prints the file as one string with repr(), so every character shows.

Python
import csv
import os
import tempfile

rows = [
    {"id": "007", "name": "Mina, Jr.", "note": 'said "hi"'},
    {"id": "008", "name": "Joe", "note": "two\nlines"},
]

path = os.path.join(tempfile.mkdtemp(), "out.csv")
with open(path, "w", newline="", encoding="utf-8") as f:
    writer = csv.DictWriter(f, fieldnames=["id", "name", "note"])
    writer.writeheader()
    writer.writerows(rows)

with open(path, newline="", encoding="utf-8") as f:
    print(repr(f.read()))

Output

'id,name,note\r\n007,"Mina, Jr.","said ""hi"""\r\n008,Joe,"two\nlines"\r\n'

Each line ends with \r\n, the CRLF from the RFC. The name 'Mina, Jr.' is wrapped in quotes, the quote in the note is doubled, and the note with a line break is quoted too. The ids kept their zeros because we wrote them as text.

Common traps

A few problems come up again and again. All of them follow from one fact: the file is only text.

  1. 1

    1. Numbers with commas

    A price such as 1,299 must be quoted in a CSV file, or the comma would split it. After reading, the value is the text '1,299'. float('1,299') fails. Strip the comma first, then convert.

  2. 2

    2. Leading zeros

    A postcode such as 02134 is a label, not a quantity. Turn it into a number and the zero is gone. Keep ids, postcodes and phone numbers as text.

  3. 3

    3. Dates

    A date is just text too. The text 03/04/2026 does not say which part is the day and which is the month. Prefer the year-month-day form, such as 2026-03-01, and convert it with date.fromisoformat().

  4. 4

    4. Huge files

    A file may be bigger than your memory. Do not load it all. Loop over the reader: each trip round the loop gives you one row, and you keep only what you need, such as a running total, as we do in the walk-through.

Here are the first two traps together, in one small run.

Python
import csv
import io

text = 'item,price,zip\nLamp,"1,299",02134\n'
row = next(csv.DictReader(io.StringIO(text)))
print(row)
print(row["zip"], int(row["zip"]))
try:
    float(row["price"])
except ValueError as err:
    print("error:", err)
print(float(row["price"].replace(",", "")))

Output

{'item': 'Lamp', 'price': '1,299', 'zip': '02134'}
02134 2134
error: could not convert string to float: '1,299'
1299.0

The raw row shows the price as '1,299' and the postcode as '02134'. The int() call turned the postcode into 2134, so the zero is lost. Converting '1,299' straight to a float fails with an error, but after replace(',', '') it becomes 1299.0.

Walk-through: clean and convert

Let's put it all together on the house-price data from earlier lessons. The file below is made up for this lesson. It has quoted prices with commas, a blank bedroom count, a bad size ('n/a'), a missing price and dates. The goal is a list of clean rows, ready for a model.

  1. 1

    1. Read the rows

    Use csv.DictReader so each row is a dictionary and you can pick values by column name.

  2. 2

    2. Convert one row inside try

    Turn size into a float, bedrooms into an int (or None if blank), price into a float after removing the comma, and the sold date into a date.

  3. 3

    3. Catch bad rows

    If any conversion raises ValueError, skip the row and count it. Never hide this count.

  4. 4

    4. Report what happened

    Print the clean rows and how many were kept and skipped. Then you can decide whether to fix the file or the code.

Python
import csv
import io
from datetime import date

raw = """size_m2,bedrooms,price,sold
50,1,"150,000",2026-03-01
80,,"240,000",2026-04-15
n/a,3,"360,000",2026-05-30
120,3,,2026-06-02
"""

clean = []
skipped = 0
for row in csv.DictReader(io.StringIO(raw)):
    try:
        clean.append({
            "size_m2": float(row["size_m2"]),
            "bedrooms": int(row["bedrooms"]) if row["bedrooms"] else None,
            "price": float(row["price"].replace(",", "")),
            "sold": date.fromisoformat(row["sold"]),
        })
    except ValueError:
        skipped += 1

for r in clean:
    print(r)
print("kept:", len(clean), "skipped:", skipped)

Output

{'size_m2': 50.0, 'bedrooms': 1, 'price': 150000.0, 'sold': datetime.date(2026, 3, 1)}
{'size_m2': 80.0, 'bedrooms': None, 'price': 240000.0, 'sold': datetime.date(2026, 4, 15)}
kept: 2 skipped: 2

Two rows survived. Row three was skipped because float('n/a') fails. Row four was skipped because its price was empty, and float('') fails too. The second kept row has bedrooms None: a blank we chose to keep as 'unknown'. Always look at the skipped count. Here half the rows vanished, and in real data that is a problem to understand, not a detail to ignore.

Where to go next

CSV is plain text: one record per line, commas between fields, quotes for anything awkward. It has no types and no way to name its encoding. That explains most CSV bugs: a wrong delimiter, a hidden BOM, numbers that are really text, and gaps that are empty strings.

Next: Excel files.

Sources

How is this lesson?

Last updated on 16 November 2026

Learn with me

Every lesson is free. I add new ones as I learn. Have a question, or want help with your own work? Book a short call.