Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

I work with CSV files and sometimes need to quickly check the contents of a row or column from the command line. In many cases cut, head, tail, and friends will do the job; however, cut cannot easily deal with situations such as

"this, is the first entry", this is the second, 34.5

Here, the first comma is part of the first field, but cut -d, -f1 disagrees. Before I write a solution myself, I was wondering if anyone knew of a good tool that already exists for this job. It would have to, at the very least, be able to handle the example above and return a column from a CSV formatted file. Other desirable features include the ability to select columns based on the column names given in the first row, support for other quoting styles and support for tab-separated files.

If you don't know of such a tool but have suggestions regarding implementing such a program in Bash, Perl, or Python, or other common scripting languages, I wouldn't mind such suggestions.

share|improve this question
add comment

8 Answers

up vote 15 down vote accepted

You can use Python's csv module.

A simple example:

import csv
reader = csv.reader(open("test.csv", "rb"))
for row in reader:
    for col in row:
        print col
share|improve this answer
    
My final solution was in python since my Perl was too rusty. Thanks. –  Steven D Feb 24 '11 at 3:02
add comment

I used csvtool once and it saved me a lot of time and trouble. Callable from the shell.

http://caml.inria.fr/cgi-bin/hump.en.cgi?contrib=447

share|improve this answer
add comment

I've found csvfix, a command line tool does the job well. You will need to make it yourself however:

http://code.google.com/p/csvfix/

It does all the things you'd expect, order/select columns, split/merge and many you wouldn't like generating SQL inserts from CSV data and diffing CSV data.

share|improve this answer
add comment

There is also a Curry library for reading/writing files in CSV format: CSV.

share|improve this answer
    
Would you mind posting some sample code, like the Perl, Python and R answers? (Especially since Curry is not a common unix scripting language.) –  Gilles Apr 20 '11 at 21:31
    
@Gilles: Yes, you are right, I should post some sample code to make the answer better. I'm going to do this in a while. –  imz -- Ivan Zakharyaschev Apr 20 '11 at 21:36
add comment

R is not my favorite programming language, but it is good for things like this. If your csv file is

***********
foo.csv
***********
 col1, col2, col3
"this, is the first entry", this is the second, 34.5
'some more', "messed up", stuff

Inside the R interpreter type

> x=read.csv("foo.csv", header=FALSE)

> x
                     col1                col2   col3
1 this, is the first entry  this is the second   34.5
2              'some more'           messed up  stuff
> x[1]  # first col
                      col1
1 this, is the first entry
2              'some more'
> x[1,] # first row
                      col1                col2  col3
1 this, is the first entry  this is the second  34.5

With regard to your other requests, for "the ability to select columns based on the column names given in the first row" see

> x["col1"]
                      col1
1 this, is the first entry
2              'some more'

For "support for other quoting styles" see the quote argument to read.csv (and related functions). For "support for tab-separated files" see the sep argument to read.csv (set sep to '\t').

For more information see the online help.

> help(read.csv)
share|improve this answer
    
I'm very familiar with R, but my goal was to have something I could use easily from Bash. –  Steven D Apr 6 '11 at 15:03
    
@Steven: R can easily be run from the command line, in the same way as Python or Perl, if that is your only concern. See Rscript (part of the base R distribution) or the addon package littler. You can do #!/usr/bin/env Rscript or similar. –  Faheem Mitha Apr 7 '11 at 18:21
    
Ah yes. I'm pretty proficient in R but hadn't used it much to create this type of utility. I have something working in Python but I may try to create something in R as well. –  Steven D Apr 10 '11 at 1:05
add comment

Or, you could try some awk magic. Howewer, I'm not a good awk user and cannot confirm this would work properly, and how to do it.

share|improve this answer
7  
Here is one awk CSV Parser I used a while back.. It seems quite well thought out... lorance.freeshell.org/csv –  Peter.O Feb 15 '11 at 14:11
add comment

Sounds like a job for Perl with Text::CSV.

perl -MText::CSV -pe '
    BEGIN {$csv = Text::CSV->new();}
    $csv->parse($_) or die;
    @fields = $csv->fields();
    print @fields[1,3];
'

See the documentation for how to handle column names. The separator and quoting style can be tuned with parameters to new. See also Text::CSV::Separator for separator guessing.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.