Working with CSV Files from the Command Line

April 30th, 2009 by Mitch Frazier in

Your rating: None Average: 4.4 (10 votes)

How to extract and manipulate CSV data from the command line.

Download in .ogv format
__________________________
Mitch Frazier is an Associate Editor for Linux Journal and the Web Editor for linuxjournal.com.


Special Magazine Offer -- Free Gift with Subscription
Receive a free digital copy of Linux Journal's System Administration Special Edition as well as instant online access to current and past issues. CLICK HERE for offer

Linux Journal: delivering readers the advice and inspiration they need to get the most out of their Linux systems since 1994.

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
Anonymous's picture

I recommend the csv module

On April 30th, 2009 Anonymous (not verified) says:

I recommend the csv module in python.

http://docs.python.org/library/csv.html

It is really easy to use and handles fields with embedded newlines, quotes, commas with flying colors!

Give it a shot some time, you won't be disappointed :).

-TH

Administra.tion.ca's picture

Big caveat

On April 30th, 2009 Administra.tion.ca (not verified) says:

Most CSV files I get these days have quoted strings containing commas inside the quoted field between the separators.

You'd better be very sure of your CSV contents if you're going to use cut like this.

It'll fail on this CSV file:

1,Cat food,55.69,05/04/2009
2,Power bill,149.75,03/04/2009
3,Rent,"1,350.00",01/04/2009

Mitch Frazier's picture

Use Grep

On May 2nd, 2009 Mitch Frazier says:

My original thinking was to use grep:

   grep -i '[^,]*@[^,]*' stuff.csv ...

to extract the email but I decided on cut to simplify the "look" of it a bit for the video.

Of course your sample doesn't include any emails so that wouldn't work... so you'd have to adapt/change it to extract the right field.

A perhaps more robust method is to convert the CSV file to use tabs rather than commas. With tabs cut should work most of the time. Still might be that you'd have quoted tabs, but unless your CSV came from a database dump that seems unlikely.

__________________________

Mitch Frazier is an Associate Editor for Linux Journal and the Web Editor for linuxjournal.com.

Rob Russell's picture

How will grep help us parse?

On May 5th, 2009 Rob Russell (not verified) says:

That grep will identify lines that are CSV, but won't parse out the values. And yes, I deal with tons of huge CSV files that go in and out of databases, so it's important that I get my scripts right.

The only thing I've found that comes close to what I need is http://perlmeme.org/tutorials/parsing_csv.html -- so I'm in the process of rewriting that in to something bash can invoke from inside shell scripts.

Mitch Frazier's picture

Forgot an option

On May 5th, 2009 Mitch Frazier says:

Forgot the -o option:

  grep -o -i '[^,]*@[^,]*' stuff.csv ...

that will show you only the part that matched.

__________________________

Mitch Frazier is an Associate Editor for Linux Journal and the Web Editor for linuxjournal.com.

Rob Russell 's picture

But that's still not "parsing"

On May 7th, 2009 Rob Russell (not verified) says:

Sure, that'll extract an email address, but it doesn't fit any accepted definition of "parsing," since it assumes that you have prior knowledge of what's in the field and can't properly handle cases of having more than one @ in any line.

You're handling strings of text of which you can predict formatting, but you are certainly not using techniques that are transferrable to the general case of "I need to parse a CSV file."

Mitch Frazier's picture

That's Correct

On May 8th, 2009 Mitch Frazier says:

You're 100% right, neither cut nor grep are generalized solutions for parsing CSV files, but then again I never said they were. As far as I know I never used the word parse, that was your word.

What I meant to say was that grep is both a floor wax and a dessert topping.

__________________________

Mitch Frazier is an Associate Editor for Linux Journal and the Web Editor for linuxjournal.com.

Gumnos's picture

No need for uniq

On April 30th, 2009 Gumnos (not verified) says:

GNU sort has a --unique (-u) argument that checks sorts and checks for uniqueness in the same pass. So you can just use "sort -uf" or "sort --ignore-case --unique" skipping uniq altogether.

-Tim

Mitch Frazier's picture

Thanks

On May 2nd, 2009 Mitch Frazier says:

Yes it does and that's why I always say one should re-RTFM once in a while to refresh one's memory and check for new features.

I might also add that one shouldn't jump to the the conclusion that the -u option makes uniq obsolete: uniq has some other useful options that come in handy once in a while. So re-RTFM applies to uniq also.

__________________________

Mitch Frazier is an Associate Editor for Linux Journal and the Web Editor for linuxjournal.com.

mattcen's picture

Beat me to it

On May 1st, 2009 mattcen says:

I'm glad somebody mentioned this; I found out about that a couple of months ago. Much better than having to pipe to yet another process :D

Post new comment

Please note that comments may not appear immediately, so there is no need to repost your comment.
The content of this field is kept private and will not be shown publicly.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <pre> <ul> <ol> <li> <dl> <dt> <dd> <i> <b>
  • Lines and paragraphs break automatically.

More information about formatting options

Newsletter

Each week Linux Journal editors will tell you what's hot in the world of Linux. You will receive late breaking news, technical tips and tricks, and links to in-depth stories featured on www.linuxjournal.com.
Sign up for our Email Newsletter

Tech Tip Videos

From the Magazine

December 2009, #188

If last month's Infrastrucuture issue was too "big" for you then try on this month's Embedded issue. Find out how to use Player for programming mobile robots, build a humidity controller for your root cellar, find out how to reduce the boot time of your embedded system, and if you're new to embedded systems find out the basics that go into one. You can also read about the Beagle Board, the Mesh Potato and a spate of other interestingly named items. And along with our regular columns don't miss our new monthly column: Economy Size Geek.







Read this issue