10 ms·
Miller is like sed, awk, cut, join, and sort for name-indexed data such as CSV
- chromano 11y agoI see a lot of value on this tool, because I've been in the same situation as you. I can't thank you enough for this, a tool that I've been wanting to implement for so long.
- john_kerl 11y agothanks! i use it regularly. but there are surely many corner cases & bugfixes i haven't encountered yet. i'd be happy to get your feedback.
- smegel 11y agoIf you work with this sort of data a lot, you might want to look at R.
- brudgers 11y agoYour comment brings up an interesting point. Sed, Awk, etc. are single purpose tools and that's one of the reasons their so long lived according to the common wisdom of *Nix. The other side of that wisdom is that once you build a swiss-army-knife, it gets harder to argue against just using a full blown programming language, like R, TCL, Perl, Python, etc.
- uxcn 11y agoI use R, and I still use ad-hoc commands from time to time too. I guess mlr and xsv lie somewhere between, which I've personally never really considered. R is specifically designed for manipulating datasets though, and has a lot more functionality/dependencies. If you're already familiar with the language, and okay with the dependencies, it is vastly more powerful, more succinct, and arguably easier to use. Otherwise, something in between probably still makes sense.
- ketralnis 11y agoNot exactly the same thing, but I often use a tiny tool I wrote called shellql[1] that lets you just type in SQL like cat foo.csv | shellql "select f1, count(*) from tbl where f2='thingiwant' group by f1 order by count(*) desc limit 5" It has some limitations on the formats of CSV that it can take, namely that string fields have to be quoted, but you can see from the code that there's not much to it if you wanted to change that. It'd also be easy enough to take in multiple CSV files, join them together, etc. sqlite's pretty badass. [1] https://gist.github.com/ketralnis/8124b3ead9d94e00dcb2 https://gist.github.com/ketralnis/8124b3ead9d94e00dcb2
- trimbo 11y agoAlso check out Google's Crush-tools https://github.com/google/crush-tools https://github.com/google/crush-tools
- rwmj 11y agoOr csvtool (written 2005, available in all good Linux distros): https://github.com/Chris00/ocaml-csv/blob/master/examples/csvtool.ml#L510 https://github.com/Chris00/ocaml-csv/blob/master/examples/cs...
- michaelmior 11y agoI love csvkit[0] for this purpose [0] https://github.com/onyxfish/csvkit https://github.com/onyxfish/csvkit
- pella 11y agosimple csvkit tutorial: " - Convert Excel to CSV: in2csv data.xls > data.csv - Convert JSON to CSV: in2csv data.json > data.csv - Print column names: csvcut -n data.csv - Select a subset of columns: csvcut -c column_a,column_c data.csv > new.csv - Reorder columns: csvcut -c column_c,column_a data.csv > new.csv - Find rows with matching ells: csvgrep -c phone_number -r 555-555-\d{4}" data.csv > matching.csv - Convert to JSON: csvjson data.csv > data.json - Generate summary statistics: csvstat data.csv - Query with SQL: csvsql --query "select name from data where age > 30" data.csv > old_folks.csv - Import into PostgreSQL: csvsql --db postgresql:///database --insert data.csv - Extract data from PostgreSQL:: sql2csv --db postgresql:///database --query "select * from data" > extract.csv " see more: http://csvkit.readthedocs.org/en/0.9.1/# http://csvkit.readthedocs.org/en/0.9.1/#
- loevborg 11y agocsvkit is excellent. I like `csvlook` for display csv files in the terminal. Can anyone recommend an easy way to convert csv to xls on Linux? I know that libreoffice and gnumeric can do it in headless mode, but it seems terrible overkill.
- functional_test 11y agoAs others have mentioned, you can find variations of this around online, but if you wish to roll you own (perhaps to add other features or what have you), you could knock it out in about 20 lines of Python using the built in CSV library and Openpyxl [1]. [1] https://openpyxl.readthedocs.org/en/latest/ https://openpyxl.readthedocs.org/en/latest/
- icebraining 11y agoA good way to find those kinds of conversion tools is to search using the x2y pattern. For example, searching "csv2xls" returns: https://github.com/ferkulat/csv2xls https://github.com/ferkulat/csv2xls https://github.com/alexprengere/csv2xls https://github.com/alexprengere/csv2xls http://www.bitools.eu/csv2xls/ http://www.bitools.eu/csv2xls/
- acveilleux 11y agoI have my own little toolkit of csv parsing tools that do most of these (using python's csv module.) I suspect most people who do deal with bulk CSV files all the time do as well. I'd be curious about how gracefully it handles large CSV files. The lack of quoting support kills it for my use case however. I wrote my own tools, starting with csv_cut because cut(1) didn't do quoting.
- john_kerl 11y agoquoting is key but i've not yet stumbled for lack of it. but it's very much worth doing. miller handles large files well. it's fast enough, and doesn't hold entire file contents unless it has to (e.g. sort). so gigabytes of input data are fine.
- ggrothendieck 11y agoRegarding quoting support, check out https://github.com/dbro/csvquote https://github.com/dbro/csvquote
- acveilleux 11y agoNot sufficient, but close. It substitutes the quoted character for another one.
- dbro 11y agoHi- I'm curious to know what your use case is. Can you explain why substitution is not sufficient?
- acveilleux 11y ago(1) I filter on column content using regex and dealing with a sub character adds complexity. (2) Many of my columns are free-form text containing commas, carriage returns, new lines, tab, vertical tabs and file separator (0x1c). Occasionally, text is in UCS-2/UTF-16 or uses UTF-8 and foreign characters (a non-trivial quantity of the text I process is in French for example.) (If you read between the lines here, some columns can contain MLLP-encoded HL7 messages, others contain free-form text and I'm in the medical field.)
- nileshtrivedi 11y agoThis is nice! It'd be great to download a .deb package for it.
- dajobe 11y agoThere is also the (impossible to google) https://github.com/harelba/q https://github.com/harelba/q that lets you SQL over CSV and is unix pipeline friendly.
- meowface 11y agoThis is great. Wonder why they didn't go with "cq" or something, though.
- singingfish 11y agodoes it bring any advantages over https://metacpan.org/pod/DBD::CSV https://metacpan.org/pod/DBD::CSV? (not being perl is not an advantage).
- pyre 11y agoWell, DBD::CSV is just a driver for DBI, not a command that is "unix pipeline friendly"... So there's that.
- singingfish 11y agoPerl is pipeline friendly though ...
- pyre 11y agoDBD::CSV is a library, which you could use to build a command, but doesn't seem to come with one out of the box. q is just a command, but not a library. One could use DBI + DBD::CSV + Perl to build something similar to q, but that's a batteries not included solution.
- kinleyd 11y agoMiller looks very useful, and so does q. Thanks to the op and to you for the introductions.
- 11y ago
- natch 11y agoThis is lovely. Portable C, awesome.
- Scarbutt 11y agoHe says he used GNU C99, how is that portable C? http://johnkerl.org/miller/doc/whyc.html#C_is_old-school http://johnkerl.org/miller/doc/whyc.html#C_is_old-school
- john_kerl 11y agomy aim is portability within a 21st century context ... I need to find a succinct way to say that. the word "portable" is the wrong choice; sounds like it would compile on everything ever which I don't aim for.
- ghshephard 11y agoNot totally portable. They have built some ctags option dependencies that I'm going to have to futz with in order to get the makefile to run. 12:58 shephard:c shephard$ make all ctags -R . ctags: illegal option -- R usage: ctags [-BFadtuwvx] [-f tagsfile] file ... make: *** [tags] Error 1 [Edit - No "-R" option for ctags in OS X 10.8.5, or in OpenBSD Current - http://www.openbsd.org/cgi-bin/man.cgi/OpenBSD-current/man1/ctags.1?query=ctags http://www.openbsd.org/cgi-bin/man.cgi/OpenBSD-current/man1/... Edit2 - Now, it seems to be breaking on lemon not being explicitly called out in the path during a make: lemon filter_dsl_parse.y make[1]: lemon: No such file or directory make[1]: *** [filter_dsl_parse.c] Error 1 make: *** [dsls] Error 2 13:07 shephard:c shephard$ ]
- mhd 11y agoI sometimes dumb down CSV to what sqlite understands and then just import it there. Gonna have a look at this, but whether it's SQL or Ruby/Python/Perl structures, I generally prefer to spend the least amount possible within actual CSV. Even more so if it contains hierarchical data.
- jongraehl 11y agoTo build: install flex and ctags, and -Wno-error=unused-but-set-variable
- justhjkust 11y agoThere's also xsv: https://github.com/BurntSushi/xsv https://github.com/BurntSushi/xsv
- pentestercrab 11y agoDoes anyone happen to know of similar or generally useful tools for dealing with YAML files? Would ideally like to be able to run SQL-like queries.
- Pamar 11y agoCan anyone suggest something similar for Windows?
- Cyykratahk 11y agoPowerShell can handle CSV files natively. Here are the equivalent commands from the article: Import-Csv example.csv | Select-Object year, price | Export-Csv -NoTypeInformation example-cut.csv Import-Csv example.csv | Sort-Object year | Export-Csv -NoTypeInformation example-sorted.csv Import-Csv example.csv | Where-Object year -eq 1999 | Export-Csv -NoTypeInformation example-filtered.csv PowerShell can also deal with JSON and XML in much the same way.
- jevgeni 11y agoPowerShell has solved this problem incredibly well. It often feels more Unix in its philosophy, than some of the newer *nix tools, I've encountered.
- useerup 11y agoThis neatly really illustrates why objects matter. As cool as miller is, jq is also decidedly cool, but for json. Both claim that they are "like" awk, sed, cut, paste etc, but for $Format, where $Format is csv/tsv and json, respectively. They both deal with much the same tasks, and there is considerable overlap. What your PowerShell examples do not say, is how - after Import-Csv - you are no longer constrained to tools written for csv's (or json or ...). At that point the records of the csv file have become objects in the PowerShell type system (which is an extension of the .NET type system). Another point about the corresponding functionality in PowerShell is how you do not need to convert back to csv, json or whatever. Your Export-Csv commands in your examples are only needed if you want to finally save the results as csv. Otherwise you would just keep the objects in a variable or flowing through the pipeline/script. So TFAs 2nd example could be written ipcsv flins.csv | group county -n If the data had been in json, one would write cat flins.json | convertfrom-json | group county -n Which also means that data can be export through any cmdlet that exports or converts a stream of objects: Export-Csv, ConvertTo-Csv, ConvertTo-Html, ConvertTo-Json, ConvertTo-Xml, ...
- chatman 11y agoLicense is unclear.
- john_kerl 11y agobsd but i'll emphasize
- sordina 11y agoI made a little tool called SQLBong that does something similar by embedding Sqlite3 under the hood: https://github.com/sordina/SQLBong https://github.com/sordina/SQLBong
- deleted 11y ago[deleted]
- djrobstep 11y agoNothing wrong with this approach I suppose but wouldn't most people be better off to just use a database for this sort of thing?
- dmix 11y agoDepends on your familiarity with the unix toolset vs use cases, an amusing (if somewhat contrived) example of this: http://aadrake.com/command-line-tools-can-be-235x-faster-than-your-hadoop-cluster.html http://aadrake.com/command-line-tools-can-be-235x-faster-tha... Most CSV analysis is static, not something ongoing with inserts/updates/etc, so using a combination of unix tools to handle the data processing seems like a sufficient solution for many scenarios.
- john_kerl 11y ago+1 and nice link :)
- unfamiliar 11y agoI use CSV all the time because I am constantly generating new datasets and it is nice to be able to literally cat|pbcopy them and paste them into DataGraph to see the results. Every major language has some sort of csv reader (usually into a dataframe type object) which makes it very easy to work with. Databases in my experience are laborious to set up and I would need to learn the API rather than just println("a,b,c,d").
- jazzido 11y agothe little known csvfix (https://bitbucket.org/neilb/csvfix https://bitbucket.org/neilb/csvfix) is pretty good too.
- jevgeni 11y agoThis thing is convenient, but I don't understand why it needs a DSL? Why do I need to use `mrl --csv sort ...`, instead of `mrl --csv ... | sort`?
- asdfaoeu 11y agoBecause otherwise it will sort the header line. It looks like the reason for the --csv is because csv isn't the default format.
- san_dimitri 11y agoI am surprised no ones looking at Apache Drill. https://drill.apache.org https://drill.apache.org
- technofiend 11y agoI've used it out of curiosity and it works, but honestly I just fell back to my primary weapon of choice: Pentaho. This isn't because I'm not comfortable searching data at the command line, it's more because of momentum in that all my other data is already in a database and I usually need to join datasets, not just search them. It's trivial to create a two hop transformation in Pentaho. The first step reads your data out of a CSV, the second loads it to a table. Pentaho will generate the DDL needed to create the table based on discovered field names and types found in the import step based on data introspection. Having said that I've often wished for a tool exactly like this that's lighter weight than drill (and doesn't expose my data on a web page like drill does) so I will definitely bookmark this for the next time it's needed. Hopefully it makes it into the Redhat EPEL.
- jakobegger 11y agoIt looks like this tool doesn't support semicolon-separated CSV files, quotes, or numbers using a comma as decimal separator. Too bad. It's a pity that the CSV file format is so fragmented. It's all too common that a CSV file written by tool A can't be read by tool B. Only very few tools do it right. For example, I think the ruby CSV module has a heuristic to automatically detect the "style" of CSV files, which is pretty neat.
- john_kerl 11y agosemicolons etc you get with FS RS OS etc. number commas I can probably implement with locale; i'll look. and quotes are definitely to do. :)
- deleted 11y ago[deleted]
- gambiter 11y agoCSV stands for 'Comma-Separated Values', not 'Semicolon-Separated Values'. Seeing as this was downvoted, I'm just pointing out that bemoaning how the CSV format is fragmented and then using it incorrectly is the precise reason it is fragmented. It wasn't originally meant to be extended in so many ways, so there really isn't room to complain when a library doesn't magically figure out your personal delimiter or qualifier.
- im3w1l 11y agoAuto-detecting format sounds like a recipe for injection attacks.
- iampims 11y agoI found the pandas tests to be a great source for implementing robust CSV parsers: https://github.com/pydata/pandas/blob/master/pandas/io/tests/test_parsers.py https://github.com/pydata/pandas/blob/master/pandas/io/tests...
- pessimizer 11y agoWhenever somebody posts a csv manipulation tool, it's obligatory for everyone else to post their favorite one too: http://neilb.bitbucket.org/csvfix/ http://neilb.bitbucket.org/csvfix/ is mine; manual at http://neilb.bitbucket.org/csvfix/manual/csvfix16/csvfix.html http://neilb.bitbucket.org/csvfix/manual/csvfix16/csvfix.htm...
- burntsushi 11y ago> And initial experiments with Rust are intriguing. Yet with none of them could I obtain the throughput I get in C: see the Performance page. I'd be curious to see how `xsv` (written in Rust) compares performance wise to Miller. (I can't compile Miller---I commented on the issue tracker.) > Thus the absence of in-process multiprocessing is only a slight penalty in this particular application domain — parallelism here is more easily achieved by running multiple single-threaded processes, each handling its own input files, either on a single host or split across multiple hosts. I disagree. CSV files can be indexed[1] very simply, providing random access. This makes it trivial to split a large CSV file into chunks which can be operated on in parallel. This lifts the burden off the user to do the splitting and merging manually, which can be become quite tedious when you want to do frequency analysis on the fields. This type of indexing is not usually seen because it requires support from the underlying CSV parser to produce correct byte offsets. But you've written your own parser here in Miller, so you should be able to do something similar to `xsv`. Moreover, random access gets you slicing proportional to the slice. i.e., extract the last 10 records in a 4M row CSV file is instanteous. This opens up other doors too, like potentially faster joining and fast random sampling. P.S. I love the name. :-) [1] - http://burntsushi.net/rustdoc/csv/index/index.html http://burntsushi.net/rustdoc/csv/index/index.html
- john_kerl 11y agoSeveral feedbacks incorporated at https://github.com/johnkerl/miller/commit/a1d117d3b299bbf27309f157b0a8c90fea7ac066 https://github.com/johnkerl/miller/commit/a1d117d3b299bbf273... https://github.com/johnkerl/miller/commit/7aead02c71a76fb281273563e7e65d3be018bf52 https://github.com/johnkerl/miller/commit/7aead02c71a76fb281... Also there is one issue closed (thanks epilanthanomai!) and two open. Remaining feedback items on the miller todo.txt. Thanks all for the initial feedback -- exactly what I was looking for by posting here!
- kazinator 11y agoYou can name fields in awk so that you don't have to refer to them numerically as $8, $13 or whatever. Example: BEGIN { first=1; second=2; third=3 } $first > 42 { print $third } Here, $first means $ applied to the variable first. Since first evaluates to 1, it means $1. Broken CSV handling that doesn't respect quotes is easily done in awk using comma as the field separator.
- cben 11y agoWhat about non-broken CSV? Is there an easy way to tell awk to skip escaped or quoted delimeters?
- john_kerl 11y agoRFC4180 CSV support: https://news.ycombinator.com/item?id=10132831 https://news.ycombinator.com/item?id=10132831
- john_kerl 11y agohttps://github.com/johnkerl/miller/releases/tag/v2.0.0 https://github.com/johnkerl/miller/releases/tag/v2.0.0