15 ms·
Miller CLI – Like Awk, sed, cut, join, and sort for CSV, TSV and JSON
- enriquto 5y agoI'm a bit surprised by the need of verbose "--icsv --ocsv"... Shouldn't it be trivial to see that the input is csv? (and in that case, the output could be csv by default).
- zimpenfish 5y agoI like how `csvkit` does it; assumes CSV input by default, `-t` for TSV input, `-T` for TSV output. Given I run `csvformat -T` many times a week, I appreciate the brevity.
- heinrichhartman 5y ago> Shouldn't it be trivial to see that the input is csv? There is no reliable way to infer csv files: CSV files do not have a magic number. There are all kinds of separators used and quoting rules differ widely. It's super annoying if a tool works on a 1K line CSV file, but breaks down if I have a 3 line file because it can't infer the type. I much prefer my tools not to be "90%-smart", but predictable.
- Y_Y 5y agoI assumed GP was referring to the filename extension. Of course people put all sorts of nonsense into files and then call them ".csv", but it's not a bad heuristic to use that to guess the format as a default, and even guess that the output format should be the same, in the absence of other flags.
- enriquto 5y ago> It's super annoying if a tool works on a 1K line CSV file, but breaks down if I have a 3 line file because it can't infer the type. How can that be? Is it possible to have such an ambiguous file? I mean, if a file contains a single number on a single name it can be anything, but the interpretation is the same. Can you create a file that has different contents depending on whether it is interpreted as csv or tsv?
- faho 5y ago>Can you create a file that has different contents depending on whether it is interpreted as csv or tsv? Easily! a\tb,c Is "a\tb" and "c" as a csv, but "a" and "b,c" as a tsv! In practice, if the first line contains only a tab or a comma it might be enough to infer that as the separator, but: 1. that would fail on single-column files (by misinterpreting them as multi-column if the unused separator appears) 2. that couldn't infer anything on files where both separators appear So it would only be a 90% (or maybe 99%) solution.
- darkwater 5y agoIf it's a single column file, you - the user - should know it and act accordingly. Yeah scripting usage on random input, II know, but in that case then you would specify the input type. But heuristic defaulting to the most common usage in case of doubt (that would be CSV in the CSV/TSV doubt) for the interactive use is the way to go. Or at least, it's what I would expect personally as a user.
- faho 5y agoI can see that argument, but I kinda don't agree? The issue with the heuristic is that it can fail depending on the input, and the input can easily change in a way that kills the heuristic. Say you run the tool on a file, and it detects csv input and all is well. Then you update the file, and now it includes a tab character in the first line and the heuristic detects it as a tsv and now it fails - or the heuristic now gives up, or whatever. Sure you can "improve the heuristic", but you can still, always, have the data change in a way that it defeats it. You now need to either be careful with the data or _know_ that you should specify the format, without the tool telling you. Everything seems to work immediately, and then later it blows up. That's a problem akin to e.g. bash and filenames with spaces. Everything works, until someone has a space in a filename, and then you get told that you should have known to quote everything all along (the solution there, would be to abolish word splitting). To coin a pithy phrase: When a tool is easy to misuse and a user misuses it, blame the tool, not the user. Now, if I were writing this thing I would make the logic much simpler: Make it default to csv (or whichever format is more common). Now the way to break the "heuristic" is to give data in the wrong format. But if you use csv, you don't have to explicitly give the format and your data can't break the heuristic (unless it switches format, which you would know about).
- qwerty456127 5y agoIf a text file has the same number of commas, tabs or semicolons on every line it most probably is (but, obviously, is not guaranteed to be) a CSV/TSV/SSV. Defining every flavour of these is hardly possible with a simple command line so I would rather let the user to specify an entire configuration file for this. We probably need an entire CSV schema language.
- carlmr 5y agoThat's already not true for Quoted entries that contain the separator. Which I think is a common CSV use case. But I do agree you could have a heuristic. E.g. ends in .csv and contains a lot more commas/semicolons/tabs than you would expect in normal text in the first 1-5 lines. You could still have the flag as a fallback when you need something that's completely reliable.
- enriquto 5y ago> I much prefer my tools not to be "90%-smart", but predictable. I understand your preference, but please recognize that it is not universal. Some of us much prefer a super-simple tool that fails for some particular cases, while requiring special options to be completely general. Thus you can use the heuristic defaults interactively (where you'll notice the errors easily), and write scripts with the more explicit form.
- memetomancer 5y agoIn turn I get what you are saying, but in this case it is not a trivial problem. CSV files seem simple on the surface but there are all sorts of gotchas. For example, there's plenty of variation between platforms/applications when it comes to just terminating a line. Are we using CR, LF, CR+LF, LF+CR, NL, RS, EOL? What do we do when the source file is produced by an app that uses one approach but doesn't care about the others (allows their occurrence)? If those others should appear in the data would our "90%-smart" tool make the wrong determination on line termination for the whole file? would everything just break or would this tool churn along and wreck all the data? how long until you noticed? By my estimation, the "90%-smart" tool would be about 30% dependable unless used only with a known source and format, meaning it wouldn't need to be smart in the first place.
- enriquto 5y ago> CSV files seem simple on the surface but there are all sorts of gotchas. My point is that supporting "general CSV files" is useless. Restricting your tooling to "simple CSV files" is good. But my opinions are not very representative. I also think that it is perfectly acceptable for a shell script to fail badly when it encounters filenames with spaces.
- chasil 5y agoWouldn't it be wonderful if we actually used ASCII as it was designed? Oct Dec Hex Char ---------------------------------------- 034 28 1C FS (file separator) 035 29 1D GS (group separator) 036 30 1E RS (record separator) 037 31 1F US (unit separator) https://ronaldduncan.wordpress.com/2009/10/31/text-file-formats-ascii-delimited-text-not-csv-or-tab-delimited-text/ https://ronaldduncan.wordpress.com/2009/10/31/text-file-form...
- otabdeveloper4 5y agoSee also my own foray into this space: https://tkatchev.bitbucket.io/ https://tkatchev.bitbucket.io/ You might find it useful.
- natrys 5y agoThanks for tab. I have used it a bit in the pipeline of some of my projects. Like jq, I think tab is another good example of a DSL that allows tersely and quickly getting things done. Same as Perl I suppose, but cleaner and more functional. Raku is also great, but it adds big dependencies whereas tab is single binary which is always nice. If you are taking feature request, string interpolation or a sprintf function would make report generation kind of things easier for me.
- p6steve 5y agoA single binary dependency may be a better fit for one project as you outline, but I would make the wider case for a common core language that provides a DSL toolkit with a recursive descent style Grammar capability. This helps to build critical mass across multiple DSLs to maintain and improve a secure code base. In turn, DSL authors get a more productive development environment. Raku embeds Grammar support for DSLs and will soon have AST introspection for "Slang" authors to reach in and build elements directly...
- raiph 5y agoI'm still looking forward to a single binary for each of the following: * NQP * Rakudo * Rakudo programs Building on https://yakshavingcream.blogspot.com/2019/08/summer-in-review.html https://yakshavingcream.blogspot.com/2019/08/summer-in-revie...
- traceroute66 5y agoIs this (supposedly) better than Burntsushi toolset (e.g. XSV[1]) or just different ? [1] https://github.com/BurntSushi/xsv https://github.com/BurntSushi/xsv
- chaosfox 5y agoI love xsv, but its been 3 years since last release and the PRs are accumulating.. miller seems much more active.
- sam_goody 5y agoHow does this compare to JQ? Aside for that it appears to support more formats (CSV [which has partial JQ support], and TSV), of course. I find it very helpful when people compare their new toy or service with what is already pretty well known.
- awild 5y agoMiller is not new (at least 6 years old is what I can gleam from the Github page). It is somewhat similar to JQ, but specifically for record based formats and not documents. The example page [0] has quite a few nice examples. A recent snippet from my own usage, looking for duplicate ids in a large-ish csv.gz: mlr uniq -c -g id then filter '$count > 1' mlr --ijson --ocsv filter '$success' then stats1 -a mean,min,max,stddev -f duration [0]: https://miller.readthedocs.io/en/latest/data-examples.html https://miller.readthedocs.io/en/latest/data-examples.html
- xvilka 5y agoThere's also rq (record query)[1] that also supports CSV and JSON but not TSV though. It's written in Rust. [1] https://github.com/dflemstr/rq https://github.com/dflemstr/rq
- mmahemoff 5y agoI don't think Sed and Awk should be on this list. It's not really able to transform text into something else, is it? Even the tutorial leans on sed for cleansing ahead of Miller. https://www.ict4g.net/adolfo/notes/data-analysis/miller-quick-tutorial.html https://www.ict4g.net/adolfo/notes/data-analysis/miller-quic...
- majkinetor 5y agoYes it can. https://miller.readthedocs.io/en/latest/features.html https://miller.readthedocs.io/en/latest/features.html
- yung_steezy 5y agoI always use sqlite3 for working with large CSV files: $ sqlite3 $ .mode csv $ .import my.csv foo $ SELECT * FROM foo WHERE name = 'bar'; It reads the header in automatically for the field names and then stores all the values as strings.
- greazy 5y agoThis is extremely ugly, I have no idea why you'd do this. If you're going to call a tool in interactive mode on the CLI, then use R for proper data handling.
- skrebbel 5y agoYou can't understand that there are people who know SQL and not R?
- momirlan 5y agoI don't think the comment refers to people, just to better ways of doing things. People learn.
- lugged 5y agoIn my experience people rarely learn anything after you insult them and call their work ugly. If AP had a "better way of doing things" maybe they should have actually given us an example of what it looks like. Right now, all I think about R is that snobby, gatekeeping assholes use it to do things I can already do easily in SQL.
- deleted 5y ago[deleted]
- deleted 5y ago[deleted]
- halayli 5y agoIf there is anything ugly here, it's your communication. Aside from the fact that OP's example can run in a non-interactive mode, it makes absolutely no difference to the discussion.
- chrisweekly 5y agoSee LNAV (https://lnav.org https://lnav.org) for a "mini-ETL" CLI powertool with embedded SQLite; it's fantastic.
- throw0101a 5y ago> Like Awk, sed, cut, join, and sort […] So basically Perl? :)
- awild 5y agoI make a lot of use of Miller at work and it has been a godsend. People tend to underestimate tools like this, but I'm able to answer a lot of questions about our data that other people would take longer for or don't have the capacity to check. It's great as a first step and if anything more complex is needed we usually spin up a python script. So it's very much in the philosophy of small composable tools but for records.
- msluyter 5y agoSome other useful tools for csv files, in particular: csvkit, a set of command line tools for manipulating csvs: https://csvkit.readthedocs.io/en/latest/ https://csvkit.readthedocs.io/en/latest/ visdata, a quite terminal based csv explorer: https://www.visidata.org/ https://www.visidata.org/
- mxuribe 5y agovisdata is pretty mind-blowing...at least, that's how i felt when i saw the demo the first time: https://www.youtube.com/watch?v=N1CBDTgGtOU https://www.youtube.com/watch?v=N1CBDTgGtOU
- calvinmorrison 5y agoJust yesterday I was opining in the IRC about what it would be like to have a operating system that was build from the ground up that supports smarter data formats than just text. The concept of everything as a file, has awesome benefits I believe, but that doesn't meant files need to be unstructured text. There's a core problem, that with text languages (like AWK or structured regex (sed, etc) you end up both having to parse AND manipulate data. which is no fun and prone to errors. Abstracting away all of that into codecs that all of coreutils could speak would be very cool to me. The second issue is structured data vs unstructured text. CSVs, or other table based formats make sense sometimes, and sometimes you want to be able to query things more easily. JSON provides that. What's the synthesis of both I don't know... but maybe this is closer to heaven. I'd like to _minimize_ the amount of parsing or text manipulation done in any program and be able to focus on extracting and inserting the data I need, like a rosetta stone that just handles everything for me while I work. I want to be able to do: awk "{print $schoolName}" or awk "{print $4}" and it just works - json, text, CSV.
- platz 5y agoso, powershell?
- kybernetikos 5y agoSounds like it. Another option might be nushell. https://www.nushell.sh/ https://www.nushell.sh/
- oauea 5y agoYou should take a look at the late TempleOS. It's not a serious contender but has a lot of interesting ideas in that direction
- pisipisipisi 5y agoHe is dead now. RIP
- zokier 5y ago
- smashah 5y agoNice. Any reason why installation section is not prominent and on the top of the readme? It's even in an non-intuitive place in the documentation.
- nyolfen 5y agoi recently came across `jq`, which i use for this purpose
- nyolfen 5y agoi recently came across jq, which i use for json parsing: https://stedolan.github.io/jq/ https://stedolan.github.io/jq/
- dima55 5y agoSimilar, but using the ACTUAL awk, sed, join, sort tools you already have and know about: https://github.com/dkogan/vnlog/ https://github.com/dkogan/vnlog/
- flakiness 5y agoThe comparison between their ongoing C version [1] and the upcoming go version [2] is nice to see. The README talks a bit more the performance comparison and the C version's strength, but still. [1] https://github.com/johnkerl/miller/tree/main/c https://github.com/johnkerl/miller/tree/main/c [2] https://github.com/johnkerl/miller/tree/main/go https://github.com/johnkerl/miller/tree/main/go
- citilife 5y agoNot exactly the same, but we wrote a library to easily load any delimited type of file and finds header (even if not first row). It also works to load JSON, Parquet, AVRO and loads it into a dataframe. Not CLI exactly, but pretty easy: https://github.com/capitalone/dataprofiler https://github.com/capitalone/dataprofiler Can Install: pip install dataprofiler[ml] --user How it works: csv_data = Data('your_file.csv') # Load: delimited, JSON, Parquet, Avro csv_data.data.head(10) # Get head csv_data.data.sort_values(by='name', inplace=True) # Sort Anyway, pretty interesting Miller CLI. I'm not sure how the header detection works, especially if the header isn't the first row (which is often the case)
- snthpy 5y agoThanks. This looks very cool.
- lazyeye 5y agoFor windows users NimbleText is a great little utility for working with tabular data:- https://nimbletext.com/ https://nimbletext.com/
- csmarshall 5y agoI don't know about MillerCLI's portability, but RecordStream (https://github.com/benbernard/RecordStream https://github.com/benbernard/RecordStream) is my go to swiss army knife.
- muad_kyrlach 5y agoGot a csv or json you need to eff-with from the command line? It's Miller time, baby!
- lenkite 5y agoI just prefer using R data-frames - the convenience of a full data-science language shell for getting statistical data out of CSV. One can also make gorgeous graphs using ggplot2.
- nwmcsween 5y agoA bit OT but there really need to be a new paradigm shift for a new OS interface that has less coupling and more flexibility compared to piping.
- brushfoot 5y agoIf you have Windows, you have a tool that can do this already installed: PowerShell. (If you don't have Windows, PowerShell is free and open source [MIT license]. One of the first things I install on a new Linux or macOS box lately.) The first example in PowerShell: > Import-Csv example.csv | Sort-Object Color, Shape To filter, use `Where-Object`: > Import-Csv example.csv | Where-Object { $_.Color -eq 'purple' } | Sort-Object Color, Shape Or using aliases: > ipcsv example.csv | ? { $_.Color -eq 'purple' } | sort Color, Shape JSON uses the same syntax -- just replace `Import-Csv` with `Get-Content` and `ConvertFrom-Json`: > Get-Content example.json | ConvertFrom-Json | Select-Object Color -Unique > purple > red > yellow There's also `Group-Object` for aggregation.
- simonw 5y agoI added a new feature to my sqlite-utils CLI tool in June which covers similar ground: it now lets you load CSV, TSV and JSON data into an in-memory SQLite database and execute SQL queries against it: https://simonwillison.net/2021/Jun/19/sqlite-utils-memory/ https://simonwillison.net/2021/Jun/19/sqlite-utils-memory/ Example usage: sqlite-utils memory example.csv "select * from t order by color, shape" # Defaults to outputting JSON, you can add # --csv or --tsv for those formats or # --table to output as a rendered table More docs here: https://sqlite-utils.datasette.io/en/stable/cli.html#querying-data-directly-using-an-in-memory-database https://sqlite-utils.datasette.io/en/stable/cli.html#queryin...