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.

CSV file 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 and Close 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).

Example: Open a CSV 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.

You can also specify the exact path that the file is located at.

Example: Specifying absolute path

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

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!

The close() function is used to close a file.

Example: Close a file

f.close()

Another way (Recommended) to open a file is to use the with keyword.

It automatically takes care of closing the file once it leaves the with block (even in cases of error).

Example: Open a CSV file using ‘with’ statement

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

Read a CSV File

Assume 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.

Example: Read entire CSV file

Code:

import csv

with open('myfile.csv') as f:
    reader = csv.reader(f)
    for row in reader:
        print(row)

Output:

['name', 'age', 'job', 'city']
['Bob', '25', 'Manager', 'Seattle']
['Sam', '30', 'Developer', 'New York']

Write a CSV File

To write to an existing file, you need to open the file in one of the writing modes viz. 'w', 'a' or 'r+'

Then, use writerow() method and pass the data in the form of list of strings.

Example:

Code:

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'])

New File Contents:

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.

Example:

File Contents:

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

Code:

import csv

with open('myfile.csv') as f:
    reader = csv.DictReader(f)
    for row in reader:
        print(row)

Output:

{'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, you should specify your own keys by setting the optional parameter fieldnames.

Example:

File Contents:

Bob,25,Manager,Seattle
Sam,30,Developer,New York

Code:

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)

Output:

{'job': 'Manager', 'city': 'Seattle', 'age': '25', 'name': 'Bob'}
{'job': 'Developer', 'city': 'New York', 'age': '30', 'name': 'Sam'}

Write a CSV File From a Dictionary

You can write a CSV file from a dictionary, using DictWriter() method.

Here the fieldnames parameter is required when writing a dictionary.

If you want to add column names in the CSV file, use writeheader() method.

Example:

Code:

import csv

with open('myfile.csv', mode='w') as f:
    keys = ['name', 'age', 'job', 'city']
    writer = csv.DictWriter(f, fieldnames=keys)

    writer.writeheader()
    writer.writerow({'job': 'Manager', 'city': 'Seattle', 'age': '25', 'name': 'Bob'})
    writer.writerow({'job': 'Developer', 'city': 'New York', 'age': '30', 'name': 'Sam'})

New File Contents:

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.

Example: Write a file with different delimiter ‘|’

Code:

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'])

New File Contents:

Bob|25|Manager|Seattle
Sam|30|Developer|New York

Specify the same delimiter while reading the file.

Example: Read a file with different delimiter ‘|’

File Contents:

Bob|25|Manager|Seattle
Sam|30|Developer|New York

Code:

import csv

with open('myfile.csv') as f:
    reader = csv.reader(f, delimiter='|')
    for row in reader:
        print(row)

Output:

['Bob', '25', 'Manager', 'Seattle']
['Sam', '30', 'Developer', 'New York']

Handle Comma Within a Data

To handle comma within a data, wrap it in quotes.

Python treats the specified delimiter in a quoted string as an ordinary character.

You can specify the character to be used for quoting using the parameter quotechar.

Example: Write a CSV file with data containing delimiter (comma)

Code:

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'])

New File Contents:

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

Specify the same character used for quoting while reading the file.

Example: Read a CSV file with data containing delimiter (comma)

File Contents:

name,age,address
Bob,25,"113 Cherry St, Seattle, WA 98104, USA"
Sam,30,"150 Greene St, New York, NY 10012, USA"

Code:

import csv

with open('myfile.csv') as f:
    reader = csv.reader(f, quotechar='"')
    for row in reader:
        print(row)

Output:

['name', 'age', 'address']
['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 during file manipulation by using the try-except block.

Example: Exception handling during CSV file manipulation

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))