If you want to import or export spreadsheets and databases for use in the Python interpreter, you must rely on the CSV module, or Comma Separated Values format.
What is a CSV File?
CSV files are used to store a large number of variables – or data. They are incredibly simplified spreadsheets – think Excel – only the content is stored in plaintext.
And the CSV module is a built-in function that allows Python to parse these types of files.
It’s worth noting that when you work with a CSV file, you are dabbling in JSON development.
The text inside a CSV file is laid out in rows, and each of those has columns, all separated by commas. Every line in the file is a row in the spreadsheet, while the commas are used to define and separate cells.
Working with the CSV Module
To pull information from CSV files you use loop and split methods to get the data from individual columns.
The CSV module explicitly exists to handle this task, making it much easier to deal with CSV formatted files. This becomes especially important when you are working with data that’s been exported from actual spreadsheets and databases to text files. This information can be tough to read on its own.
Unfortunately, there is no standard so the CSV module uses “dialects” to support parsing using different parameters. Along with a generic reader and writer, the module includes a dialect for working with Microsoft Excel and related files.
The CSV module includes all the necessary functions built in. They are:
In this guide we are only going to focus on the reader and writer functions which allow you to edit, modify, and manipulate the data stored in a CSV file.
Reading CSV Files
To pull data from a CSV file, you must use the reader function to generate a reader object.
The reader function is designed to take each line of the file and make a list of all columns. Then, you just choose the column you want the variable data for.
It sounds a lot more complicated than it is. To prove it, let’s take a look at an example.
import CSV With open(‘some.csv’, ‘rb’) as f: reader = csv.reader(f) for row in reader: print row
Notice how the first command is used to import the CSV module?
Let’s look at another example.
import csv import sys f = open(sys.argv, ‘rb’) reader = csv.reader(f) for row in reader print row f.close()
In the first two lines, we are importing the CSV and sys modules. Then, we open the CSV file we want to pull information from.
Next, we create the reader object, iterate the rows of the file, and then print them. Finally, we close out the operation.
CSV Sample File
We’re going to take a look at an example CSV file. Pay attention to how the information is stored and presented.
Title,Release Date,Director And Now For Something Completely Different,1971,Ian MacNaughton Monty Python And The Holy Grail,1975,Terry Gilliam and Terry Jones Monty Python's Life Of Brian,1979,Terry Jones Monty Python Live At The Hollywood Bowl,1982,Terry Hughes Monty Python's The Meaning Of Life,1983,Terry Jones
Reading CSV Files Example
We’re going to start with a basic CSV file that has 3 columns, containing the variables “A”, “B”, “C”, and “D”.
$ cat test.csv A,B,”C D” 1,2,”3 4” 5,6,7
Then, we’ll use the following Python program to read and display the contents of the above CSV file.
import csv ifile = open(‘test.csv’, “rb”) reader = csv.reader(ifile) rownum = 0 for row in reader: # Save header row. if rownum ==0: header = row else: colnum = 0 for col in row: print ‘%-8s: %s’ % (header[colnum], col) colnum + = 1 rownum + = 1 ifile.close()
When we execute this program in Python, the output will look like this:
$ python csv1.py A : 1 B : 2 C D : 3 4 A : 5 B : 6 C D : 7
Writing to CSV Files
When you have a set of data that you would like to store inside a CSV file, it’s time to do the opposite and use the write function. Believe it or not, this is just as easy to accomplish as reading them.
The writer() function will create an object suitable for writing. To iterate the data over the rows, you will need to use the writerow() function.
Here’s an example.
The following Python program converts a file called “test.csv” to a CSV file that uses tabs as a value separator with all values quoted. The delimiter character and the quote character, as well as how/when to quote, are specified when the writer is created. These same options are available when creating reader objects.
import csv ifile = open('test.csv', "rb") reader = csv.reader(ifile) ofile = open('ttest.csv', "wb") writer = csv.writer(ofile, delimiter='', quotechar='"', quoting=csv.QUOTE_ALL) for row in reader: writer.writerow(row) ifile.close() ofile.close()
When you execute this program, the output will be:
$ python csv2.py $ cat ttest.csv "A" "B" "C D" "1" "2" "3 4" "5" "6" "7"
Quoting CSV Files
With the CSV module, you can also perform a variety of quoting functions.
- csv.QUOTE_ALL – Quote everything, regardless of type.
- csv.QUOTE_MINIMAL – Quote fields with special characters
- csv.QUOTE_NONNUMERIC – Quote all fields that are not integers or floats
- csv.QUOTE_NONE – Do not quote anything on output
More Python Reading and Resources
http://docs.python.org/2/library/csv.html http://www.doughellmann.com/PyMOTW/csv/ http://effbot.org/librarybook/csv.htm http://www.linuxjournal.com/content/handling-csv-files-python http://programming-crash-course.codepoint.net/there_are_columns
Recommended Python Training
Course: Python 3 For Beginners
Over 15 hours of video content with guided instruction for beginners. Learn how to create real world applications and master the basics.