Hacker News Reader

Comment by nkcmr | Hacker News Reader

Yesss. Glad to see this getting some love. I use this tool very frequently in my day-to-day and my work would be so much harder without it.

A lot of folks mentioning great projects/solutions that work the same but I love me some good unix piping action:

    cat data.csv | xsv select 1,3 | q 'select * from - where col1 != "foo"'

(xsv: also invaluable on the terminal: https://github.com/BurntSushi/xsv)

View on HN

Is that any different than using sqlite3 directly like this?

    cat data.csv | sqlite3 -csv ':memory:' '.import /dev/stdin data' 'select col1, col3 from data where col1 != "foo"'

You can of course use the .import command directly on the file, but keeping it there to show piping.

sqlite has become my preferred way to work with csv files in an adhoc manner. I do it often enough that I wrote a small wrapper around sqlite to make it easier to work with multiple csv files with overlapping column names, and to normalize column names: https://github.com/psanford/csv2sqlite

Using sqlite3 for any task makes me so happy, I try to use it even when it's overkill

If you want the best of two worlds, you can also give bigbash [1] a try. It generates a bash statement using unix tools from a sql query that can be run on csv files.

[1] http://bigbash.it/

Is that better than:

  cut -d, -f1,3 data.csv | grep -v '^foo'

I'm sure there are some things where you need sql/q, but this isn't one of them, and there is a lot of utility in productive with bare POSIX tools.

Expressing an 'or' or having any logic to the condition becomes quickly annoying with usual *nix tools, and most people use SQL much more often than they forget how to use sed and awk.

egrep '(a|b)'

If always splitting on ',' is correct for your data, then your solution is probably fine. You probably want '^foo,' though, otherwise yours includes things where 'foo' is just a prefix of the first column value.

But CSV permits quoted fields that contain literal ','. So in that case, splitting on ',' would be incorrect.

This breaks down when you have commas in your CSV data like if you had something like locations as the quoted string "Atlanta, GA"

And yet another set of tools is vnlog (https://github.com/dkogan/vnlog). It assumes you have whitespace-separated data, with a legend comment, then you can do the above thing with

  < data.vnl vnl-filter -p thiscolumn,thatcolumn 'thiscolumn != "foo"'

If you don't strictly need csv, those tools are quite nice, and have a really friendly learning curve. Disclaimer: I'm the author

Why use xsv in this case (as opposed to SELECT col1, col3)?

Mostly for just demonstrating how it combines with other tools in my workflow over pipes :D

Even shorter with r and probably a good deal quicker

r -e "data.table::fread(data.csv)[col1 != "foo", .(1,3)]"

I don't know anything about R and I couldn't get this to work. First, I had to install R. Then it told me that 'data.table' was unrecognized. So I searched around and installed that. Then, 'data.csv' is not quoted. So fixed that. So I tried running it on some data I have:

    > data.table::fread("version_downloads.csv")[date == "foo", .(1,3)]
    Error: character string is not in a standard unambiguous format
    Execution halted

Dunno how to fix this now.

Fix your strings. :)

That's not helpful. CSV data is what it is. If you're advertising an alternative for processing CSV data but it chokes on valid CSV, then that's kind of a show-stopper IMO.

And I have no idea which string to fix or how to even fix it. The failure mode here is not great.

Might be the datatypes. fread automagically tries to determine the datatypes of your columns by jumping around the file as it's being read.

https://www.rdocumentation.org/packages/data.table/versions/...

Or you can pass colClasses = 'character' as an argument to read everything as a string, but that will be much much slower.

fread also reads from stdin so you can pass bash commands into the first parameter instead of using bash to run r via R -e.

I guess that's nifty, but it makes it much less universal. It's also not clear to me why reading everything as a string would be slower. If anything, it seems to me like it would be faster, since you wouldn't need to do type guessing.

Full disclosure, I'm the author of 'xsv' and the thing I was most interested in here was performance.

Reply on news.ycombinator.com