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

    $ sqlite3
    $ .mode csv
    $ .import my.csv foo
    $ SELECT * FROM foo WHERE name = 'bar';

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.

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/

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)

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

[1] https://github.com/johnkerl/miller/tree/main/c

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

You might find it useful.

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.

Even the tutorial leans on sed for cleansing ahead of Miller. https://www.ict4g.net/adolfo/notes/data-analysis/miller-quic...

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.

So basically Perl? :)

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 

[1] https://github.com/dflemstr/rq

It's even in an non-intuitive place in the documentation.

https://nimbletext.com/