Easy Exporting CSV Data with Python

This article is a follow-up to Reading CSV Data with Python.

In that article, I described what CSV files are and how to read CSV Data. The flip side to reading data is writing data out to a file. I will summarize again what a CSV file is then go into how to write data in this format.

CSV files are extremely common. I run into them all the time at work when transferring data. At home, a CSV file is almost always an option when exporting a file from the bank or out of Google Sheets.

What is CSV?

CSV=Comma Separated Values

Black and white macro of giraffe head Photo by Andrew Furlan / Unsplash

We see them all the time but there seems to be some confusion about what a CSV file really is. When I talk to people about CSV files, I realize that most of us equate them with Microsoft Excel documents. This is probably because Excel is often the default program for opening CSV files.

Here is the thing. A CSV file is simply a text file. That’s it, nothing more. The file extension is usually .csv but it doesn’t have to be. Any file extension is simply a hint to what is contained in the file and doesn’t actually determine or control the content type. The Wikipedia Article about CSV files states that:

CSV” is not a single, well-defined format. As a result, in practice the term “CSV” might refer to any file that: is plain text using a character set such as ASCII, various Unicode character sets consists of records (typically one record per line), with the records divided into fields separated by delimiters * where every record has the same sequence of fields

Here is an example. I have a file called users.csv sitting on my desktop. In a spreadsheet program (Excel, Google Sheets, LibreOffice, Numbers, etc) this file would look like this.

csv_file_1.1

Those columns and rows are nice and fancy. We think there is something magical going one but it isn’t. The contents of this file are really just:

user_id,first_name,last_name,email
234,Kevin,Crommer,example@example.com
235,Carl,Marx,cmarx@example.com

You can open a CSV file with a plaintext editor to see the true contents. It is very similar to viewing the source of a web page and looking at the underlying HTML.

A spreadsheet programs reads the CSV file and displays to the user (you and me) a pretty version that makes it easy to see the columns and rows.

As you can see in my example, A CSV file typically has one row per record. In this case there are 4 columns. The headers of the columns are user_id, first_name, last_name and email. Records will have one or more columns that are usually, not always, separated by a comma.

Enough Review Already, Get to the Writing Data Good Stuff

Ok, ok. Let’s learn about how to write CSV data to a file or file-like object.

There are a few essentials points that you will need to remember when writing out CSV data.

Do not try to write CSV without a library.

You have better things to do with your time than to figure out how to handle every possible CSV variation. Believe me, I speak from experience. I will admit to wasting days of time before I discovered the built-in (yes built-in) csv module. Your efforts are better spent getting stuff done.

The csv module is Awesome

The CSV Module is built into every modern version of Python.1

This module handles all of the intricacies of reading and writing CSV data. It is crazy fast and just works.

To quote the Python Docs:

The csv module implements classes to read and write tabular data in CSV format. It allows programmers to say, “write this data in the format preferred by Excel,” or “read data from this file which was generated by Excel,” without knowing the precise details of the CSV format used by Excel. Programmers can also describe the CSV formats understood by other applications or define their own special-purpose CSV formats.

Enough Enough already… Get to An Example

Okay, okay. Let’s start with an example.

Unrelated Awesome Motorcycle Photo by Josh Marshall

Let’s say you have a list of people in your python script. Each person has a name, email, phone_number, age, home_planet, and language.

people = [
    {
        'name': 'Admiral Ackbar',
        'email': 'ackbar@calamari.com',
        'phone_number': '233.234.2340.8ad51d',
        'age': 'unknown',
        'home_planet': 'Calamari',
        'language': 'Standard'
    },
    {
        'name': 'Sheev Palpatine',
        'email': 'spalpatine@naboo.com',
        'phone_number': '822.288.2340.4a451d',
        'age': 'unknown',
        'home_planet': 'naboo',
        'language': 'Standard'
    },
    {
        'name': 'Leia Organa',
        'email': 'lorgana@naboo.com',
        'phone_number': 'none',
        'age': '54',
        'home_planet': 'Alderaan',
        'language': 'Standard'
    },
    {
        'name': 'Chewbacca',
        'email': 'chewie@kashyyyk.com',
        'phone_number': 'none',
        'age': '150',
        'home_planet': 'Kashyyyk',
        'language': 'Wookie'
    }
]

Now we have some sample data, let’s create a CSV with this it.

CSV Writing - Step 1

The first step is to open a file to write to. I recommend using the pattern with open(filename, 'w+') as _fhandler:. Without with you have to call _fhandler.close() yourself or risk losing or corrupting your data. Python is forgiving but I just don’t like taking the risk.

import csv
with open('./outputfile.csv', 'w+') as _fhandler:

Once the file is open for writing, you then create the csv writer. At this point you have two choices. Which you choose will be determined by the structure of the data for each row.

The Dictionary Writer

The CSV module provides a writer for dictionary data, which is what we have in this example. In this case, the csv_writer is created as in the example above.

csv_writer = csv.DictWriter(_fhandler, fieldnames=FIELDNAMES)

FIELDNAMES must refer to a list of fieldnames that you want to write to the file. If your dictionaries have more fields than what you want to include in the csv file, you need to tell the writer to ignore them. This is how you would do this.

csv_writer = csv.DictWriter(_fhandler, fieldnames=FIELDNAMES, extrasaction='ignore')

You will want the CSV writer to write the columns headers at the top of the file. Here is how to do that.

csv_writer.writeheader()

With the header written, you are ready to write the CSV data to the file. This is as simple as

csv_writer.writerows(people)

That will write all of the rows to the file at once.

You can also write the data one row at a time. In this case it would be:

for person in people:
    csv_writer.writerow(person)

Our complete example then ends up like the code snippet below.

import csv
people = [
    {
        'name': 'Admiral Ackbar',
        'email': 'ackbar@calamari.com',
        'phone_number': '233.234.2340.8ad51d',
        'age': 'unknown',
        'home_planet': 'Calamari',
        'language': 'Standard'
    },
    {
        'name': 'Sheev Palpatine',
        'email': 'spalpatine@naboo.com',
        'phone_number': '822.288.2340.4a451d',
        'age': 'unknown',
        'home_planet': 'naboo',
        'language': 'Standard'
    },
    {
        'name': 'Leia Organa',
        'email': 'lorgana@naboo.com',
        'phone_number': 'none',
        'age': '54',
        'home_planet': 'Alderaan',
        'language': 'Standard'
    },
    {
        'name': 'Chewbacca',
        'email': 'chewie@kashyyyk.com',
        'phone_number': 'none',
        'age': '150',
        'home_planet': 'Kashyyyk',
        'language': 'Wookie'
    }
]

FIELDNAMES = ('name', 'email', 'age','home_planet','language')
with open('./outputfile.csv', 'w+') as _fhandler:
    csv_writer = csv.DictWriter(_fhandler, fieldnames=FIELDNAMES, extrasaction='ignore')
    csv_writer.writeheader()

    # All rows at once
    csv_writer.writerows(people)

    # one row at a time
    #for person in people:
    #  csv_writer.writerow(person)

The Simple List Writer

Your data may not be in dictionary form. It could be simple lists of raw data. I prefer to work with dictionaries myself but that may not always be available.

If the data were in list form, then the complete example would look like this.

import csv
people = [
    ['Admiral Ackbar', 'ackbar@calamari.com', '233.234.2340.8ad51d', 'unknown', 'Calamari', 'Standard'], 
    ['Sheev Palpatine', 'spalpatine@naboo.com', '822.288.2340.4a451d', 'unknown', 'naboo', 'Standard'], 
    ['Leia Organa', 'lorgana@naboo.com', 'none', '54', 'Alderaan', 'Standard'],
    ['Chewbacca', 'chewie@kashyyyk.com', 'none', '150', 'Kashyyyk', 'Wookie']
]

FIELDNAMES = ('name', 'email', 'age','home_planet','language')
with open('./outputfile.csv', 'w+') as _fhandler:
    csv_writer = csv.writer(_fhandler)
    csv_writer.writerow(FIELDNAMES)

    # All rows at once
    csv_writer.writerows(people)

    # one row at a time
    #for person in people:
    #  csv_writer.writerow(person)

This appears shorter than the dictionary version only because of how it is formatted. There are several things I don’t like about working with simple list data.

First, there is not structure to the data. What I mean is there is not way to be sure that the first value of each row is the person’s name. The list could be mixed up for some reason.

Second, I don’t have a simple way to write only those fields that I want. I left the phone_number in my dictionary example but I have no choice but to include it here unless I want to do some manipulation of each row to remove these columns before writing it to the file.

A final note about csv_writer.writerows(rows_of_data)

Here is one final note about the csv_writer.writerows(rows_of_data) version of writing data. rows_of_data can be a list of rows or an iterator. ^[I’ll write more about iterators (and generators) in another article. Sneak Peak: they are awesome.]

An iterator is essentially a memory-efficient way of looping through some set of data without loading the whole data set into memory. I have used them to manipulate gigantice sets of data (think multiple gigabytes) without crashing my computer. They are also useful for dealing with streaming information where you don’t know how much data there will be.

That’s it, folks

There you go. Now you know how to write CSV data with Python. No go and try your hand at it.


  1. Python 2: https://docs.python.org/2/library/csv.html, Python 3: https://docs.python.org/3/library/csv.html