mjhjmtu

I am probably a little bit too late, but there is another tool worth mentioning: csvkit

http://csvkit.readthedocs.org/

It has a lot of command line tools that can:

• reformatting CSV files,


• convert to and from CSV from various formats (JSON, SQL, XLS),


• the equivalent of cut, grep, sort and others, but CSV-aware,


• join different CSV files,


• do general SQL queries on data from CSV files.

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.

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

http://neilb.bitbucket.org/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.

If you want to use the command-line (and do not create an entire program to do the job), you'd like to use rows, a project I'm working on: it's a command-line interface to tabular data but also a Python library to use in your programs. With the command-line interface you can pretty-print any data in CSV, XLS, XLSX, HTML or any other tabular format supported by the library with a simple command:

rows print myfile.csv

If myfile.csv is like this:

state,city,inhabitants,area
RJ,Angra dos Reis,169511,825.09
RJ,Aperibé,10213,94.64
RJ,Araruama,112008,638.02
RJ,Areal,11423,110.92
RJ,Armação dos Búzios,27560,70.28

Then rows will print the contents in a beautiful way, like this:

+-------+-------------------------------+-------------+---------+
| state | city | inhabitants | area |
+-------+-------------------------------+-------------+---------+
| RJ | Angra dos Reis | 169511 | 825.09 |
| RJ | Aperibé | 10213 | 94.64 |
| RJ | Araruama | 112008 | 638.02 |
| RJ | Areal | 11423 | 110.92 |
| RJ | Armação dos Búzios | 27560 | 70.28 |
+-------+-------------------------------+-------------+---------+

Installing

If you are a Python developer and already have pip installed on your machine, just run inside a virtualenv or with sudo:

pip install rows

If you're using Debian:

sudo apt-get install rows

Other Cool Features

Converting Formats

You can convert between any supported format:

rows convert myfile.xlsx myfile.csv

Querying

Yes, you can use SQL into a CSV file:

$ rows query 'SELECT city, area FROM table1 WHERE inhabitants > 100000' myfile.csv
+----------------+--------+
| city | area |
+----------------+--------+
| Angra dos Reis | 825.09 |
| Araruama | 638.02 |
+----------------+--------+

Converting the output of the query to a file instead of stdout is also possible using the --output parameter.

As a Python Library

You can you in your Python programs too:

import rows
table = rows.import_from_csv('myfile.csv')
rows.export_to_txt(table, 'myfile.txt')
# `myfile.txt` will have same content as `rows print` output

Hope you enjoy it!

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)

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

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.

Have a look also at GNU Recutils and crush-tools.

(via http://www.reddit.com/r/commandline/comments/mfcu9/anyone_using_gnu_recutils_is_it_outdatedsuperceded/)

Miller is another nice tool for manipulating name-based data, including CSV (with headers). To extract the first column of a CSV file, without caring about its name, you’d do something like

printf '"first,column",second,thirdn1,2,3n' |
 mlr --csv --implicit-csv-header --headerless-csv-output cut -f 1

To use python from the command line, you can check out pythonpy (https://github.com/Russell91/pythonpy):

$ echo $'a,b,cnd,e,f' | py '[x[1] for x in csv.reader(sys.stdin)']
b
e

try "csvtool" this package
it's handy command line tool for handling CSV files

cissy will also do command-line csv processing. It's written in C (small/lightweight) with rpm and deb packages available for most distros.

Using the example:

echo '"this, is the first entry", this is the second, 34.5' | cissy -c 1
"this, is the first entry"

or

echo '"this, is the first entry", this is the second, 34.5' | cissy -c 2
 this is the second

or

echo '"this, is the first entry", this is the second, 34.5' | cissy -c 2-
 this is the second, 34.5

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

The github repo Structured Text Tools has a useful list of relevant linux command line tools. In particular, the Delimiter Separated Values section lists several CSV capable tools that directly support the operations requested.

An awk solution

awk -vq='"' '
func csv2del(n) 
 for(i=n; i<=c; i++)
 if(i%2 == 1) gsub(/,/, OFS, a[i])
 else a[i] = (q a[i] q)
 out = (out) ? out a[i] : a[i]
 return out
c=split($0, a, q); out=X;
 if(a[1]) $0=csv2del(1)
 else $0=csv2del(2)1' OFS='|' file

I'd recommend xsv - A fast CSV command line toolkit written in Rust (Github).

Written by Ripgrep's author.

Featured in How we made our CSV processing 142x faster (Reddit thread).

One of the best tool is Miller (http://johnkerl.org/miller/doc/index.html). It is like awk, sed, cut, join, and sort for name-indexed data such as CSV, TSV, and tabular JSON.

In example

echo '"this, is the first entry", this is the second, 34.5' | 
mlr --icsv --implicit-csv-header cat

gives you

1=this, is the first entry,2= this is the second,3= 34.5

If you want a TSV

echo '"this, is the first entry", this is the second, 34.5' | 
mlr --c2t --implicit-csv-header cat

gives you (it's possible to remove the header)

1 2 3
this, is the first entry this is the second 34.5

If you want first and third column, changing their order

echo '"this, is the first entry", this is the second, 34.5' | 
mlr --csv --implicit-csv-header --headerless-csv-output cut -o -f 3,1

gives you

 34.5,"this, is the first entry"