Reading and Writing CSV Files in Python

A CSV file (Comma Separated Values file) is a delimited text file that uses a comma , to separate values. It is used to store tabular data, such as a spreadsheet or database.

Python’s Built-in csv library makes it easy to read, write, and process data from and to CSV files.

Open a CSV File

When you want to work with a CSV file, the first thing to do is to open it. You can open a file using open() built-in function specifying its name (same as a text file).

f = open('myfile.csv')

When you specify the filename only, it is assumed that the file is located in the same folder as Python. If it is somewhere else, you can specify the exact path where the file is located.

f = open(r'C:\Python33\Scripts\myfile.csv')

Remember! While specifying the exact path, characters prefaced by \ (like \n \r \t etc.) are interpreted as special characters. You can escape them using:

  • raw strings like r'C:\new\text.txt'
  • double backslashes like 'C:\\new\\text.txt'

Specify File Mode

Here are five different modes you can use to open the file:

Python File Modes
CharacterModeDescription
‘r’Read (default)Open a file for read only
‘w’WriteOpen a file for write only (overwrite)
‘a’AppendOpen a file for write only (append)
‘r+’Read+Writeopen a file for both reading and writing
‘x’CreateCreate a new file

Here are some examples:

# Open a file for reading
f = open('myfile.csv')
# Open a file for writing
f = open('myfile.csv', 'w')
# Open a file for reading and writing
f = open('myfile.csv', 'r+')

Because read mode ‘r’ and text mode ‘t’ are default modes, you do not need to specify them.

Close a CSV File

It’s a good practice to close the file once you are finished with it. You don’t want an open file running around taking up resources!

Use the close() function to close an open file.

f = open('myfile.csv')
f.close()
# check closed status
print(f.closed)
# Prints True

There are two approaches to ensure that a file is closed properly, even in cases of error.

The first approach is to use the with keyword, which Python recommends, as it automatically takes care of closing the file once it leaves the with block (even in cases of error).

with open('myfile.csv') as f:
    print(f.read())

The second approach is to use the try-finally block:

f = open('myfile.csv')
try:
    # File operations goes here
finally:
    f.close()

Read a CSV File

Suppose you have the following CSV file.

myfile.csv
name,age,job,city
Bob,25,Manager,Seattle
Sam,30,Developer,New York

You can read its contents by importing the csv module and using its reader() method. The reader() method splits each row on a specified delimiter and returns the list of strings.

import csv
with open('myfile.csv') as f:
    reader = csv.reader(f)
    for row in reader:
        print(row)
# Prints:
# ['name', 'age', 'job', 'city']
# ['Bob', '25', 'Manager', 'Seattle']
# ['Sam', '30', 'Developer', 'New York']

Write to a CSV File

To write an existing file, you must first open the file in one of the writing modes (‘w’‘a’ or ‘r+’) first. Then, use a writer object and its writerow() method to pass the data as a list of strings.

import csv
with open('myfile.csv', 'w') as f:
    writer = csv.writer(f)
    writer.writerow(['Bob', '25', 'Manager', 'Seattle'])
    writer.writerow(['Sam', '30', 'Developer', 'New York'])
myfile.csv
Bob,25,Manager,Seattle
Sam,30,Developer,New York

Read a CSV File Into a Dictionary

You can read CSV file directly into a dictionary using DictReader() method. The first row of the CSV file is assumed to contain the column names, which are used as keys for the dictionary.

import csv
with open('myfile.csv') as f:
    reader = csv.DictReader(f)
    for row in reader:
        print(row)
# Prints:
# {'job': 'Manager', 'city': 'Seattle', 'age': '25', 'name': 'Bob'}
# {'job': 'Developer', 'city': 'New York', 'age': '30', 'name': 'Sam'}

If the CSV file doesn’t have column names like the file below, you should specify your own keys by setting the optional parameter fieldnames.

myfile.csv
Bob,25,Manager,Seattle
Sam,30,Developer,New York
import csv
with open('myfile.csv') as f:
    keys = ['name', 'age', 'job', 'city']
    reader = csv.DictReader(f, fieldnames=keys)
    for row in reader:
        print(row)
# Prints:
# {'job': 'Manager', 'city': 'Seattle', 'age': '25', 'name': 'Bob'}
# {'job': 'Developer', 'city': 'New York', 'age': '30', 'name': 'Sam'}

Write a CSV File From a Dictionary

Since you can read a csv file in a dictionary, it’s only fair that you should be able to write it from a dictionary as well:

You can write a CSV file from a dictionary using the DictWriter() method. Here it is necessary to specify the fieldname parameter. This makes sense, because without a list of fieldnames, the DictWriter may not know which keys to use to retrieve values from the dictionary.

import csv
with open('myfile.csv', mode='w') as f:
    keys = ['name', 'age', 'job', 'city']
    writer = csv.DictWriter(f, fieldnames=keys)
    writer.writeheader()    # add column names in the CSV file
    writer.writerow({'job': 'Manager', 'city': 'Seattle', 'age': '25', 'name': 'Bob'})
    writer.writerow({'job': 'Developer', 'city': 'New York', 'age': '30', 'name': 'Sam'})
myfile.csv
name,age,job,city
Bob,25,Manager,Seattle
Sam,30,Developer,New York

Use a different delimiter

The comma , is not the only delimiter used in the CSV files. You can use any delimiter of your choice such as the pipe |, tab \t, colon : or semi-colon ; etc.

To specify a different delimiter, use the parameter delimiter.

# Write a file with different delimiter '|'
import csv
with open('myfile.csv', mode='w') as f:
    writer = csv.writer(f, delimiter='|')
    writer.writerow(['Bob', '25', 'Manager', 'Seattle'])
    writer.writerow(['Sam', '30', 'Developer', 'New York'])

The code above generates the following output file:

myfile.csv
Bob|25|Manager|Seattle
Sam|30|Developer|New York

Let’s read this file, specifying the same delimiter.

# Read a file with different delimiter '|'
import csv
with open('myfile.csv') as f:
    reader = csv.reader(f, delimiter='|')
    for row in reader:
        print(row)
# Prints:
# ['Bob', '25', 'Manager', 'Seattle']
# ['Sam', '30', 'Developer', 'New York']

Handle Comma Within a Data

Sometimes your CSV file contains fields such as an address that contains a comma. This can become a problem when working with a CSV file.

There are two different ways to handle this situation:

Use a different delimiter as described earlier. In this way the comma can be safely used in the data itself.

Or wrap data into quotes. Python considers a comma in a quoted string as an ordinary character. While writing a file, you can specify the character to be used for quoting using the parameter quotechar.

import csv
with open('myfile.csv', mode='w') as f:
    writer = csv.writer(f, quotechar='"')
    writer.writerow(['Bob', '25', '113 Cherry St, Seattle, WA 98104, USA'])
    writer.writerow(['Sam', '30', '150 Greene St, New York, NY 10012, USA'])

The code above generates the following output file:

myfile.csv
Bob,25,"113 Cherry St, Seattle, WA 98104, USA"
Sam,30,"150 Greene St, New York, NY 10012, USA"

Let’s read this file, specifying the same character used for quoting.

import csv
with open('myfile.csv') as f:
    reader = csv.reader(f, quotechar='"')
    for row in reader:
        print(row)
# Prints:
# ['Bob', '25', '113 Cherry St, Seattle, WA 98104, USA']
# ['Sam', '30', '150 Greene St, New York, NY 10012, USA']

Catching and Reporting Errors

You can handle exceptions that occur during file manipulation by using the try-except block.

import csv, sys
filename = 'myfile.csv'
with open(filename, newline='') as f:
    reader = csv.reader(f)
    try:
        for row in reader:
            print(row)
    except csv.Error as e:
        sys.exit('file {}, line {}: {}'.format(filename, reader.line_num, e))