Miller CLI – Like Awk, sed, cut, join, and sort for CSV, TSV and JSON

by jabo • 5 years ago

273 62 5 years ago

enriquto4• 5 years ago

I'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• 5 years ago

I 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• 5 years 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• 5 years ago

I 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.

enriquto4• 5 years 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?

qwerty456127• 5 years ago

If 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.

enriquto4• 5 years 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.

otabdeveloper4• 5 years ago

See also my own foray into this space: https://tkatchev.bitbucket.io/

You might find it useful.

natrys• 5 years ago

Thanks 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• 5 years ago

A 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...

traceroute66• 5 years ago

Is this (supposedly) better than Burntsushi toolset (e.g. XSV[1]) or just different ?

[1] https://github.com/BurntSushi/xsv

chaosfox• 5 years ago

I love xsv, but its been 3 years since last release and the PRs are accumulating.. miller seems much more active.

sam_goody• 5 years ago

How 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.

awild2• 5 years ago

Miller 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

xvilka• 5 years ago

There'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

mmahemoff• 5 years ago

I 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-quic...

majkinetor2• 5 years ago

Yes it can.

https://miller.readthedocs.io/en/latest/features.html

yung_steezy• 5 years ago

I 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.

sdmac99• 5 years ago

duckdb is another good option as well.

tyingq• 5 years ago

This stack exchange answer makes that into a fairly simple bash alias: https://unix.stackexchange.com/questions/424555/how-to-inser...

inanutshellus• 5 years ago

holy moly, that's rad. definitely adding this to my toolbox, thanks!

noisy_boy• 5 years ago

Great tip. A simple utility script can be used for repeated use/customization: https://pastebin.com/qEkimk7W

Another advantage is that this can be executed to import as part of initialization and get to sqlite prompt with the table ready for sql execution:

    $ csv_to_table.sh sample-test.csv

Or it can be executed using heredoc for non-interactive execution as well:

    $ csv_to_table.sh sample-test.csv <<-CMD
    select * from sample_test limit 10;
    CMD
    ... output ...

majkinetor2• 5 years ago

Not bad. Here is the Windows equivalent with Excel separator:

    PS> gsudo choco install sqlite3

    PS > sqlite3
    sqlite> .mode csv
    sqlite> .separator ; \n
    sqlite> .import my.csv foo
    sqlite> SELECT * FROM foo WHERE name = 'bar';

For CSV that contain up to several 100k records native PowerShell solution is way faster and more practical:

    Get-Content my.csv | ConvertFrom-Csv | ? name -eq bar

FYI, I maintain miller choco package: https://community.chocolatey.org/packages/miller

psanford• 5 years ago

I do the same. I wrote[0] a little wrapper around this workflow that does a few additional niceties: 1) normalizes column names and removes whitespace, 2) supports importing multiple csv files without importing the header line as a record.

[0] https://github.com/psanford/csv2sqlite

chrisweekly• 5 years ago

See LNAV (https://lnav.org) for a "mini-ETL" CLI powertool with embedded SQLite; it's fantastic.

throw0101a• 5 years ago

Like Awk, sed, cut, join, and sort […]

So basically Perl? :)

awild2• 5 years ago

I 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• 5 years ago

Some other useful tools for csv files, in particular:

csvkit, a set of command line tools for manipulating csvs: https://csvkit.readthedocs.io/en/latest/

visdata, a quite terminal based csv explorer: https://www.visidata.org/

mxuribe• 5 years ago

visdata 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

calvinmorrison• 5 years ago

Just 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• 5 years ago

so, powershell?

kybernetikos• 5 years ago

Sounds like it. Another option might be nushell. https://www.nushell.sh/

oauea• 5 years ago

You 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• 5 years ago

He is dead now. RIP

zokier• 5 years ago

Maybe the most notable system that had "files as structured data" is the IBM S/360 lineage of mainframes.

In the context of IBM mainframe computers in the S/360 line, a data set (IBM preferred) or dataset is a computer file having a record organization.
Data sets are not unstructured streams of bytes, but rather are organized in various logical record[3] and block structures determined by the DSORG (data set organization), RECFM (record format), and other parameters

https://en.wikipedia.org/wiki/Data_set_(IBM_mainframe)

Remarkably S/360 predates UNIX by several years, so its definitely not a novel concept. Albeit apparently IBM systems cost order of magnitude more than PDPs of the time.

Of course there are also classic LISP machines where afaik pretty much everything was a SEXPR, but I have even less knowledge about the details of those.

brushfoot2• 5 years ago

You should check out PowerShell. It's based on exactly that concept. Everything is an object.

`Import-Csv` and `ConvertFrom-Json` return objects that you can manipulate using LINQ or PowerShell's `*-Object` cmdlets (`Select-Object`, `Where-Object`, `Group-Object`, etc.). `Get-Process` returns objects representing your system's processes, `Get-Service` returns objects for your system's services, and so on.

Being able to chain that in your pipeline is incredibly powerful. You can send data into and out of CSVs and various other providers in a single line. It's a huge productivity boost.

smashah• 5 years ago

Nice. 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.

nyolfen2• 5 years ago

i recently came across `jq`, which i use for this purpose

nyolfen2• 5 years ago

i recently came across jq, which i use for json parsing: https://stedolan.github.io/jq/

dima55• 5 years ago

Similar, but using the ACTUAL awk, sed, join, sort tools you already have and know about: https://github.com/dkogan/vnlog/

flakiness• 5 years ago

The 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

[2] https://github.com/johnkerl/miller/tree/main/go

citilife• 5 years ago

Not 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

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• 5 years ago

Thanks. This looks very cool.

lazyeye• 5 years ago

For windows users NimbleText is a great little utility for working with tabular data:-

https://nimbletext.com/

csmarshall• 5 years ago

I don't know about MillerCLI's portability, but RecordStream (https://github.com/benbernard/RecordStream) is my go to swiss army knife.

muad_kyrlach• 5 years ago

Got a csv or json you need to eff-with from the command line? It's Miller time, baby!

lenkite• 5 years ago

I 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• 5 years ago

A 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.

brushfoot2• 5 years ago

If 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• 5 years ago

I 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/

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#queryin...

Miller CLI – Like Awk, sed, cut, join, and sort for CSV, TSV and JSON

Related Stories