CSVKit: CSV utilities that includes csvsql, csvgrep, csvstat, and more

http://csvkit.readthedocs.org/en/0.7.3/

by ConceitedCode • 12 years ago

103 34 12 years ago

csvkit.readthedocs.org

kbenson• 12 years ago

I like fsql[1], as it allows me to combine multiple CSV, TSV, LTSV. JSON and YAML files as SQL tables and query against them. Very useful.

[1] https://metacpan.org/pod/distribution/App-fsql/bin/fsql

pessimizer2• 12 years ago

I'm going to continue to throw it out there when anybody mentions csvkit, but I can't believe that more people haven't settled on http://csvfix.byethost5.com/csvfix15/csvfix.html

alaithea2• 12 years ago

Why? I know and love csvkit, but never heard of csvfix.

peterwwillis3• 12 years ago

It looks like csvfix has more features.

denimboy• 12 years ago

Also openrefine (formally google refine)

  http://openrefine.org/

is like a GUI version of csvkit.

It can do external look ups, fuzzy matching, and has its own programming languages Jython and GREL.

buckie2• 12 years ago

I tried out OpenRefine previously on the large CSV's I have to deal with at work largely on the recommendation of HN comments. On small stuff to medium size stuff, it's pretty nice. But for larger sets (+1GB) it starts to slow down and eventually will fail to commit changes when the set is big enough.

The only answer I've found to consistently work quickly, with the ability to explore the data, is the killer combo of iPython Notebook + pandas' read_csv[0] + a lot of RAM -- 10GB CSV on disk becomes ~20GB in memory (don't know why yet). When I say quick, I mean 10GB CSV un-cached disk to RAM in <5Min including fuzzy parsing on dates.

The nice part is, when you have things figured out, you can enable a chunked reading to get back in-core on machines of lesser specs. Further, you can dump the pandas DataFrame to HDF, thereafter having ludicrous-speed IO & 'where' queries.

Still though, OpenRefine is much more turn key and feature rich.

[0]http://pandas.pydata.org/pandas-docs/version/0.13.1/generate...

codygman2• 12 years ago

http://hackage.haskell.org/package/csv-conduit http://hackage.haskell.org/package/cassava

These libraries should be able to work with data that large, though I can't say whether they meet your requirements yet. I'm not sure what exactly "10GB CSV un-cached disk to RAM in <5Min including fuzzy parsing on dates".

Namely, I don't know what you mean by date fuzzy parsing or what your output looks like after. Perhaps I need to open ipython notebook and import pandas ;)

joncooper2• 12 years ago

Do any of y'all have an opinion on which of the tools mentioned here is the most robust to junky input?

alaithea2• 12 years ago

That depends on what you want to do with your junky input. And if it's junky enough, no tool is going to be able to divine what you want to do with the data until you do some manual cleanup. However, just to put it out there, csvkit has a utility, csvclean, that will split a file based on which lines are valid csv and which are not, allowing you to more quickly dive into what's wrong with the bad lines.

joncooper2• 12 years ago

That alone would be helpful. Thanks!

Malarkey733• 12 years ago

I'm not totally sure why csvgrep csvsort csvcut csvjoin are needed as replacements for grep sort, cut or join?

The syntax doesn't seem noticeably clearer?

And - without testing - I presume csvkit in Python is a bit slower than the GNU coreutils in C?

peterwwillis3• 12 years ago

They're not replacements for those tools. They're new tools that work with a specific tabular data format.

Malarkey733• 12 years ago

The data I come across is a mix of comma, tab, and space delimited though predominantly what the commenter above would call "line oriented".

So I personally just don't see the benefit from a less generalised version of coreutil tools

... but fair does I'm sure there are plenty who have use for a more specific tool.

icebraining• 12 years ago

Grep understands text files, not CSV syntax. They're not equivalent. For example, the following CSV document has two lines, but grep treats them as three:

  1,2,5,"Hello"
  3,4,5,"How are
  you doing?"

dbro• 12 years ago

That's correct, and as you illustrate it's the possibility to have newlines and commas inside quoted fields that complicates things for grep/awk/cut/etc.

So instead of making a more complex version of tools like grep, we can make the data simple for these tools to understand. That's what https://github.com/dbro/csvquote does. It can be run in a pipeline before the grep stage, and allow grep/cut/awk/... to work with unambiguous field and record delimiters. Then it can restore the newlines and commas inside the quoted fields at the end of the pipeline.

teleclimber• 12 years ago

And don't forget AWK!

I've been playing with these old school tools lately on medium sized tabular data files and I've been impressed by the performance and capabilities.

acdha3• 12 years ago

The syntax doesn't seem noticeably clearer?

From the very first example:

    csvgrep -c 1 -m ILLINOIS

using coreutils grep you'd need something like this:

    grep -E '^ILLINOIS,' 2009.csv

Oh, wait, that doesn't work because the file was last saved by something which quotes it.

    grep -E '^"?ILLINOIS"?,'

except that would produce unexpected results with 'ILLINOIS"' so it really needs to be something like:

    grep -E '^(["]{0,1})ILLINOIS\1,'

Bear in mind that this is the simplest possible case and doesn't even touch on issues like quoting in the shell or needing to handle files which have embedded separators as data values (imagine what happens when our erstwhile grep data-miner needs to check the stats for `WASHINGTON, DISTRICT OF COLUMBIA`…).

In all but the most trivial cases it's safer and easier to use tools designed for the job. csvkit also has the very nice property that it's callable as a Python library so when you outgrow a simple shell processing pipeline you could migrate your notes / scripts to a full Python program without having to retest everything related to basic file I/O, unicode, etc. which you would otherwise need to do when switching readers.

(Bear in mind that the author works in data journalism – the target user is not a grizzled Unix expert but someone who has a bunch of CSV files and a full time job which is not shell scripting)

And - without testing - I presume csvkit in Python is a bit slower than the GNU coreutils in C?

Perhaps but it'd be unlikely for it to be noticeable for n less than millions on a remotely modern system – the Python regular expression engine has quite decent performance and if that became an issue, PyPy will even JIT them for you. In the very few cases I've seen where there is a noticeable difference it was always because the Python version was decoding Unicode and the shell tools were running with LC_ALL=C, which meant that corrupt data made it further before being caught and, in some cases, either failing to match all of the records or subtly quietly things by not extracting all of a combined character, etc.

For the target use-case, however, this is likely all to be many, many orders of magnitude less than the time most people would spend debugging regex patterns.

Malarkey733• 12 years ago

I take you're point and those other well made points about line continuations below -- but you're over egging it here as

cut -d, -f1 2010.csv | grep ILLINOIS

works just fine for this data. I emphasise again - I take your overall point though.

erikb• 12 years ago

I also don't really understand why you want to grep the whole block instead of the single word 'ILLINOIS'.

fsiefken• 12 years ago

Would be nice if a tool works on querying markdown/org-mode tables... correlations, averages and sums would be nice too.

dinedal• 12 years ago

I'd love to add similar features to textql[1]. am currently working on a v2 that does a lot more, but right now it fills a gap not in CSVKit, which is the direct execution of SQL on flat files.

[1] https://github.com/dinedal/textql

rwmj2• 12 years ago

csvtool:

https://forge.ocamlcore.org/plugins/scmgit/cgi-bin/gitweb.cg...

You will already have this program in your Linux distro since I wrote it about a decade ago and it has been maintained by a small team ever since then. It also handles all the quirks of Excel CSV files and is used extensively in production.

zmmmmm2• 12 years ago

You will already have this program in your Linux distro

Yum disagrees on CentOS 6.4.

jacktasia• 12 years ago

It's actually in the package "ocaml-csv.x86_64" on CentOS 6.4, which does come up if you "yum search csvtool" (for me anyway)

rwmj2• 12 years ago

This works with yum for installing programs:

    yum install /usr/bin/csvtool

smackfu• 12 years ago

No, everyone must think they can start by splitting on commas and descending slowly into madness. That is the way of the programmer.

CSVKit: CSV utilities that includes csvsql, csvgrep, csvstat, and more

Related Stories