6 ms·

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

103 points by ConceitedCode 12y ago

kbenson 12y 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 https://metacpan.org/pod/distribution/App-fsql/bin/fsql
pessimizer 12y 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 http://csvfix.byethost5.com/csvfix15/csvfix.html
- alaithea 12y ago
  Why? I know and love csvkit, but never heard of csvfix.
  - peterwwillis 12y ago
    It looks like csvfix has more features.
    - pessimizer 12y ago
      And no python dependency (although that hardly matters.) edit: This is a better link http://neilb.bitbucket.org/csvfix/ http://neilb.bitbucket.org/csvfix/
denimboy 12y 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.
- buckie 12y 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/generated/pandas.io.parsers.read_csv.html http://pandas.pydata.org/pandas-docs/version/0.13.1/generate...
  - codygman 12y ago
    http://hackage.haskell.org/package/csv-conduit http://hackage.haskell.org/package/csv-conduit http://hackage.haskell.org/package/cassava 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 ;)
    - buckie 12y ago
      I've been working to get Haskell approved at my place of employment for 1.5 years but getting the US Gov't to change is rather hard; I've had to sit the "tech" people down and explain that javascript != java... with that baseline, explaining 'Why Haskell' is non-trivial. Come September, after 1.5 years of effort, I should have 'all the FOSS'. Until then I have to wait. It's worth noting that iPython Notebook + pandas vs. cassava + conduits (even with iHaskell Notebook) serve very different ends. If I need to explore how to do something, I'd use Haskell. But I'm still in phase 2 (phase 1: collect underpants) and I've yet to find anything as powerful and flexible as the iPython Notebook + pandas + hdf5 stack that also just works. I can just move faster with that stack than anything else I've ever seen. That being said, I'm knowingly deferring bugs to the runtime -- 'tis the cost of python. If you're unfamiliar with pandas, the "quick vignette" here[0] is decent enough. The reason pandas is awesome, IMO, isn't actually because pandas is awesome (which it is) but because it's embedded in a full language. Julia, R, etc... can do the same stuff (maybe faster), but I wouldn't also want to program, say, a production web-app in them (though I have high hopes for Julia). Fuzzy parsing on dates: pandas by default uses dateutil[1] which is both awesome and slow. 10GB CSV... : yeah... it's "fast for python" but pandas is admittedly doing a lot in that time, namely putting it into a data structure that is very friendly to time series analysis. [0] http://pandas.pydata.org/ http://pandas.pydata.org/ [1] https://labix.org/python-dateutil https://labix.org/python-dateutil
joncooper 12y ago
Do any of y'all have an opinion on which of the tools mentioned here is the most robust to junky input?
- alaithea 12y 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.
  - joncooper 12y ago
    That alone would be helpful. Thanks!
Malarkey73 12y 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?
- peterwwillis 12y ago
  They're not replacements for those tools. They're new tools that work with a specific tabular data format.
  - Malarkey73 12y 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.
    - dragonwriter 12y ago
      > So I personally just don't see the benefit from a less generalised version of coreutil tools Others have pointed out specific examples, but the general reason (which I don't think I've seen explicitly articulated) is that CSV isn't strictly line oriented -- for intsance, records can span multiple "physical" lines, because line breaks within quoted strings are part of the field value, not separate records -- so line-oriented tools don't work with CSV generally (they may if you happen to have CSV where nothing inconsistent with a line-oriented view happens to be done, but that's luck not something that is generally true of CSV.)
    - peterwwillis 12y ago
      I'm an experienced Unix admin, and I actually wrote my own versions of these csv tools long ago instead of using coreutils. I had a lot of different datasets to with with, and it turned it there were so many inconsistencies in the CSV data that I needed a tool that could handle it all. It also became super simple to extract and munge data with options for tabular data. With two tiny commands I can exclude rows, include rows, sed columns, merge tables, and export new tables, all with just csv files. So it's pretty handy.
fsiefken 12y ago
Would be nice if a tool works on querying markdown/org-mode tables... correlations, averages and sums would be nice too.
dinedal 12y 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 https://github.com/dinedal/textql
rwmj 12y ago
csvtool: https://forge.ocamlcore.org/plugins/scmgit/cgi-bin/gitweb.cgi?p=csv/csv.git;a=blob;f=examples/csvtool.ml;h=950e9aa6705c45f1315b285d055033e42b5d9ea4;hb=HEAD#l512 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.
- zmmmmm 12y ago
  > You will already have this program in your Linux distro Yum disagrees on CentOS 6.4.
  - jacktasia 12y 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)
    - zmmmmm 12y ago
      Ah, apologies then. I actually saw that but completely discounted it because it looked like an OCaml library rather than a command line tool!
  - rwmj 12y ago
    This works with yum for installing programs: yum install /usr/bin/csvtool
smackfu 12y ago
No, everyone must think they can start by splitting on commas and descending slowly into madness. That is the way of the programmer.
- deleted 12y ago
  [deleted]

103 points by ConceitedCode 12y 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 https://metacpan.org/pod/distribution/App-fsql/bin/fsql

pessimizer 12y 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 http://csvfix.byethost5.com/csvfix15/csvfix.html

alaithea 12y ago
Why? I know and love csvkit, but never heard of csvfix.
- peterwwillis 12y ago
  It looks like csvfix has more features.
  - pessimizer 12y ago
    And no python dependency (although that hardly matters.) edit: This is a better link http://neilb.bitbucket.org/csvfix/ http://neilb.bitbucket.org/csvfix/

denimboy 12y 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.

buckie 12y 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/generated/pandas.io.parsers.read_csv.html http://pandas.pydata.org/pandas-docs/version/0.13.1/generate...
- codygman 12y ago
  http://hackage.haskell.org/package/csv-conduit http://hackage.haskell.org/package/csv-conduit http://hackage.haskell.org/package/cassava 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 ;)
  - buckie 12y ago
    I've been working to get Haskell approved at my place of employment for 1.5 years but getting the US Gov't to change is rather hard; I've had to sit the "tech" people down and explain that javascript != java... with that baseline, explaining 'Why Haskell' is non-trivial. Come September, after 1.5 years of effort, I should have 'all the FOSS'. Until then I have to wait. It's worth noting that iPython Notebook + pandas vs. cassava + conduits (even with iHaskell Notebook) serve very different ends. If I need to explore how to do something, I'd use Haskell. But I'm still in phase 2 (phase 1: collect underpants) and I've yet to find anything as powerful and flexible as the iPython Notebook + pandas + hdf5 stack that also just works. I can just move faster with that stack than anything else I've ever seen. That being said, I'm knowingly deferring bugs to the runtime -- 'tis the cost of python. If you're unfamiliar with pandas, the "quick vignette" here[0] is decent enough. The reason pandas is awesome, IMO, isn't actually because pandas is awesome (which it is) but because it's embedded in a full language. Julia, R, etc... can do the same stuff (maybe faster), but I wouldn't also want to program, say, a production web-app in them (though I have high hopes for Julia). Fuzzy parsing on dates: pandas by default uses dateutil[1] which is both awesome and slow. 10GB CSV... : yeah... it's "fast for python" but pandas is admittedly doing a lot in that time, namely putting it into a data structure that is very friendly to time series analysis. [0] http://pandas.pydata.org/ http://pandas.pydata.org/ [1] https://labix.org/python-dateutil https://labix.org/python-dateutil

joncooper 12y ago

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

alaithea 12y 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.
- joncooper 12y ago
  That alone would be helpful. Thanks!

Malarkey73 12y 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?

peterwwillis 12y ago
They're not replacements for those tools. They're new tools that work with a specific tabular data format.
- Malarkey73 12y 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.
  - dragonwriter 12y ago
    > So I personally just don't see the benefit from a less generalised version of coreutil tools Others have pointed out specific examples, but the general reason (which I don't think I've seen explicitly articulated) is that CSV isn't strictly line oriented -- for intsance, records can span multiple "physical" lines, because line breaks within quoted strings are part of the field value, not separate records -- so line-oriented tools don't work with CSV generally (they may if you happen to have CSV where nothing inconsistent with a line-oriented view happens to be done, but that's luck not something that is generally true of CSV.)
  - peterwwillis 12y ago
    I'm an experienced Unix admin, and I actually wrote my own versions of these csv tools long ago instead of using coreutils. I had a lot of different datasets to with with, and it turned it there were so many inconsistencies in the CSV data that I needed a tool that could handle it all. It also became super simple to extract and munge data with options for tabular data. With two tiny commands I can exclude rows, include rows, sed columns, merge tables, and export new tables, all with just csv files. So it's pretty handy.

fsiefken 12y ago

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

dinedal 12y 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 https://github.com/dinedal/textql

rwmj 12y ago

csvtool: https://forge.ocamlcore.org/plugins/scmgit/cgi-bin/gitweb.cgi?p=csv/csv.git;a=blob;f=examples/csvtool.ml;h=950e9aa6705c45f1315b285d055033e42b5d9ea4;hb=HEAD#l512 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.

zmmmmm 12y ago
> You will already have this program in your Linux distro Yum disagrees on CentOS 6.4.
- jacktasia 12y 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)
  - zmmmmm 12y ago
    Ah, apologies then. I actually saw that but completely discounted it because it looked like an OCaml library rather than a command line tool!
- rwmj 12y ago
  This works with yum for installing programs: yum install /usr/bin/csvtool

smackfu 12y ago

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

deleted 12y ago
[deleted]