Handling CSV Files in Python
As a buddy of mine always says "the nice thing about standards is that there's so many to choose from". Take CSV files for example. CSV, of course, stands for "Comma Separated Values", more often than not though, it seems that CSV files use tabs to separate values rather than commas. And let's not even mention field quoting. If you deal with CSV files and you use Python the csv module can make your life a bit easier.
Dealing with CSV files in Python probably couldn't be much easier. For example purposes, let's use the following CSV file that contains 3 columns "A", "B", and "C D":
$ cat test.csv A,B,"C D" 1,2,"3 4" 5,6,7
The following python program reads it and displays its contents:
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 run it produces:
$ python csv1.py A : 1 B : 2 C D : 3 4 A : 5 B : 6 C D : 7
In addition, the csv module provides writer objects for writing CSV files. The following Python program converts our test CSV file to a CSV file that uses tabs as a value separator and that has all values quoted. The delimiter character and the quote character, as well as how/when to quote, are specifed 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='\t', quotechar='"', quoting=csv.QUOTE_ALL) for row in reader: writer.writerow(row) ifile.close() ofile.close()
Running it produces:
$ python csv2.py $ cat ttest.csv "A" "B" "C D" "1" "2" "3 4" "5" "6" "7"
My first task when starting to use the csv module was to write a function to try to determine what format the CSV file was in before opening it so that I could deal with commas and tabs and different quoting conventions:
import os import sys import csv def opencsv(filename): tfile = open(filename, "r") line = tfile.readline() tfile.close() if line == '"': quote_char = '"' quote_opt = csv.QUOTE_ALL elif line == "'": quote_char = "'" quote_opt = csv.QUOTE_ALL else: quote_char = '"' quote_opt = csv.QUOTE_MINIMAL if line.find('\t') != -1: delim_char = '\t' else: delim_char = ',' tfile = open(filename, "rb") reader = csv.reader(tfile, delimiter=delim_char, quotechar=quote_char, quoting=quote_opt) return (tfile, reader)
Being new to the csv module and making the common mistake of not reading the whole "man" page, I of course failed to notice that the csv module already contains something to do this called the Sniffer class. I'll leave using it as an exercise for the reader (and in this case the writer also).
Mitch Frazier is an Associate Editor for Linux Journal.
Special Reports: DevOps
Have projects in development that need help? Have a great development operation in place that can ALWAYS be better? Regardless of where you are in your DevOps process, Linux Journal can help!
With deep focus on Collaborative Development, Continuous Testing and Release & Deployment, we offer here the DEFINITIVE DevOps for Dummies, a mobile Application Development Primer, advice & help from the experts, plus a host of other books, videos, podcasts and more. All free with a quick, one-time registration. Start browsing now...
- SUSE – “Will not diverge from its Open Source roots!”
- Dealing with Boundary Issues
- Vagrant Simplified
- Libreboot on an X60, Part I: the Setup
- October 2015 Issue of Linux Journal: Raspberry Pi
- System Status as SMS Text Messages
- Bluetooth Hacks
- Disney's Linux Light Bulbs (Not a "Luxo Jr." Reboot)
- February 2015 Video Preview
- New Products