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.
- Two Pi R
- Readers' Choice Awards 2013
- AIDE—Developing for Android on Android
- A Handy U-Boot Trick
- The Geek's Guide to the Coolest 2013 Holiday Gifts
- Best. Cake. Ever.
- Sublime Text: One Editor to Rule Them All?
- Raspberry Pi: the Perfect Home Server
- Tech Tip: Really Simple HTTP Server with Python
- RSS Feeds
- Uber jealous
14 min 36 sec ago
- Reality is disapointing
10 hours 47 min ago
- Máy sấy quần áo
13 hours 33 min ago
- Services on GlusterFS
13 hours 42 min ago
- Reply to comment | Linux Journal
15 hours 23 min ago
- Definitely cool stuff here
16 hours 24 min ago
- thanks for the information
17 hours 35 min ago
- nice information thanks
18 hours 14 min ago
21 hours 9 min ago
- The lost opportunity of security
1 day 9 hours ago