13 ms·
Miller: Like Awk, sed, cut, join, and sort for CSV, TSV, and tabular JSON
- pcthrowaway 4y agoMiller is a welcome addition to my toolbox. I just wish it was as 'easy' to use as jq... part of that might be how infrequently I work with CSVs
- coldtea 4y agoHuh? jq has far more difficult syntax...
- mywittyname 4y agoThe syntax is pretty crazy, so there's a learning curve. But once your over the hump, jq is easy to use for transformations or "querying" of large volumes of json data.
- hesdeadjim 4y agoYea agreed. The moment I need to do anything complicated I start getting this gut feeling I’d be better off just writing a quick Go cli tool.
- asicsp 4y agoPrevious discussion: https://news.ycombinator.com/item?id=28298729 https://news.ycombinator.com/item?id=28298729 (273 points | Aug 25, 2021 | 66 comments)
- dang 4y agoThanks! Macroexpanded: Miller – tool for querying, shaping, reformatting data in CSV, TSV, and JSON - https://news.ycombinator.com/item?id=29651871 https://news.ycombinator.com/item?id=29651871 - Dec 2021 (33 comments) Miller CLI – Like Awk, sed, cut, join, and sort for CSV, TSV and JSON - https://news.ycombinator.com/item?id=28298729 https://news.ycombinator.com/item?id=28298729 - Aug 2021 (66 comments) Miller v5.0.0: Autodetected line-endings, in-place mode, user-defined functions - https://news.ycombinator.com/item?id=13751389 https://news.ycombinator.com/item?id=13751389 - Feb 2017 (20 comments) Miller is like sed, awk, cut, join, and sort for name-indexed data such as CSV - https://news.ycombinator.com/item?id=10066742 https://news.ycombinator.com/item?id=10066742 - Aug 2015 (76 comments)
- carb 4y agoThis is nice. I use `column` for pretty printing CSV/TSV but it fixes two tiny gaps in `sort` (skipping header lines) and `jq` (parsing CSV input. `jq` supports `@csv` for output conversion but not input). $ cat example.csv color,shape,flag,index yellow,triangle,1,11 red,square,1,15 red,circle,1,16 red,square,0,48 purple,triangle,0,51 red,square,0,77 # pretty printing $ column -ts',' example.csv color shape flag index yellow triangle 1 11 red square 1 15 red circle 1 16 red square 0 48 purple triangle 0 51 red square 0 77 # sorting with skipped headers is a mess. $ (head -n 1 example.csv && tail -n +2 example.csv | sort -r -k4 -t',') | column -ts',' color shape flag index red square 0 77 purple triangle 0 51 red square 0 48 red circle 1 16 red square 1 15 yellow triangle 1 11
- 2h 4y ago> sorting with skipped headers is a mess I like these command line tools, but I think they can cripple someone actually learning programming language. For example, here is a short program that does your last example: https://go.dev/play/p/9bASZ97lLWv https://go.dev/play/p/9bASZ97lLWv
- cosmojg 4y agoIt's a matter of perspective. I like programming languages, but I think they can cripple someone actually learning Unix! At the end of the day, you should just use whatever tools make you the most productive most quickly.
- coldtea 4y agoThe whole point of UNIX userland is to not have to write a custom program for every simple case that just needs recombining some existing basic programs in a pipeline...
- 2h 4y ago> simple case that's just it though, the last example is not a simple case, hence why the last example is awkward by the commenters own admission. command line tools are fine, but you need to know when to set the hammer down and pick up the chainsaw.
- melx 4y ago+1 for easy install (`dnf install miller` on my Fedora). But seems like it cannot handle a simple use case: CSV without header. $ mlr --csv head -n 20 pp-2002.csv mlr: unacceptable empty CSV key at file "pp-2002.csv" line 1. You have to explicitly pass it (FYI `implicit-csv-header` is terrible arg name) $ mlr --csv --implicit-csv-header head -n 20 pp-2002.csv While `head` obliges rightly $ head -n 20 pp-2002.csv
- coldtea 4y agoWell, head just reads lines. A CSV row can optionally be multiple lines. Also head doesn't do anything with the data or the format, aside from printing line by line, so doesn't need to know any column names.
- melx 4y agohead reads CSV row that has multiple lines. Miller is being offered as "like awk, sed, head.." (emphasis on head - mine) and yes it offers more, but it does not behave "like" the *nix tools it refers to.
- 0cf8612b2e1e 4y agoMiller is designed around the idea of structured data. This is a higher bar than naively manipulating text, and the user has to be more deliberate to extract fields. Doing cli manipulation of a csv that contains quoted commas is challenging with the standard tools.
- coldtea 4y ago>head reads CSV row that has multiple lines. It just reads lines, doesn't know anything about rows. So if you want, say, the first 100 rows of a csv that has multiline rows, miller can give it, while head can't. Head will just print the "N" first lines - whether those are 1, 22, 36 or N actual csv rows. >Miller is being offered as "like awk, sed, head.." (emphasis on head - mine) and yes it offers more, but it does not behave "like" the nix tools it refers to.* That's the whole idea. That it behaves in a way more suited to the csv format, and more coherent than 5-6 different text-focused tools. The claim is not "this is awk, sed, head, sort remade for csv with identical interfaces and behavior" but "this is a tool to work with csv files and do what you'd normally have to jump through hoops to do with awk, sed, head, sort which don't understand csv structure".
- snidane 4y agoGreat tool. BUT, leaks memory like crazy. Despite documentation stating the verbs are fully streaming. > Fully streaming verbs > These don't retain any state from one record to the next. They are memory-friendly, and they don't wait for end of input to produce their output. https://miller.readthedocs.io/en/6.7.0/streaming-and-memory/#fully-streaming-verbs https://miller.readthedocs.io/en/6.7.0/streaming-and-memory/...
- coldtea 4y ago>BUT, leaks memory like crazy. Huh? a) Isn't it written in Golang, which has a GC? Does it do custom buffer based management? b) Isn't it supposed to be run on a file and get some output - as opposed to an interactice session? Why would it matter if it leaks, then, and how could it leak, as the memory is returned to the OS when it ends?
- john_kerl 4y agoIt's indeed written in Go now, which indeed has GC, and ultimately all memory is freed ... but there are indeed some issues around intermediate retention of memory, taking more memory than one would have expected. Some gains were made on https://github.com/johnkerl/miller/pull/1133 https://github.com/johnkerl/miller/pull/1133 and https://github.com/johnkerl/miller/pull/1132 https://github.com/johnkerl/miller/pull/1132
- rustyf 4y agoEven without aliases, I still prefer PowerShell on Windows. Once you've turned the text into an object, you can pipeline it to hell. Get-Content .\example.csv | ConvertFrom-Csv | Where-Object -Property color -eq red
- 7thaccount 4y agoEver try this on large files? A lot of PowerShell commands I make like this can take minutes to run when a combination of Linux commands and Awk might take a couple of seconds.
- unxdfa 4y agoI much prefer this approach as you only have to parse stuff once during the processing pipeline rather than at every step. Unfortunately after working with it for a few years I utterly despise PowerShell for many other reasons.
- cbolton 4y agoCan you share some of the reasons you don't like it?
- unxdfa 4y agoYes: memory ceiling is huge, WinRM is buggy and unreliable, performance is "variable", totally inconsistent handling of dates and times, being .Net it's UTF-16 internally so streams are painful to work with, escape characters argh, variable scoping is horrible, most of the API you have to hit to get stuff done is wrapping ancient bits of weird win32 and DCOM and WMI thus has inconsistent error handling and ability to actually do what it is told. I've probably forgotten a few things.
- hobs 4y agoWhile powershell has gotten a lot faster in the most recent versions its still pretty slow for anything involving computation; most of the type my equivalent python code beats the pants off of it. The expressiveness is nice, but oftentimes modules won't support it or require weird ways of using the data to get the performance you want (mostly by dropping out of the pipe.) The choices around Format- vs Out- vs Convert- are Very Confusing for new people and the "object in a shell but also text sometimes" way of displaying things is weird and until recently things like -NoTypeInformation or managing encoding in files was just pointlessly weird. The module support and package management is still entirely in the stone ages and I regularly see people patching in C# in strings to get the behavior they want. "Larger" modules tend to get Really Slow - the azure modules especially are just an example of how not to do it. The way it automatically unwraps collections is cool, but gets weird when you have a 1 vs many option for your output, and you might find yourself defensively casting things to lists or w/e. The typing system in general is nice to get started on but declaring a type does not fix it, so assignment can just break your entire world. There's still a lot to love about the language when you are getting things done in a windows environment its great to glue together the various pieces of the system but I find the code "breaks" more often than equivalent python code.
- avodonosov 4y agoHow is it better that SQL for these tasks on tabular data? That's the first question I have after reading the title. Haven't read the article. Edited the first sentence. Originally it was "Why it is not called SQL if works on tabular data?". My point, if one wants cat, sort, sed, join on tabular data, SOL is exactly that. Awk is too powerful, not sure about it.
- LMMojo 4y agoBecause SQL (Structured Query Language) is the language used to access/manipulate the data, not the name for that kind of data. There have been, and are, many databases, which essentially use tabular data, which are not SQL database.
- garciasn 4y ago1. Because it doesn't use SQL syntax. 2. Because it's closer to an amalgamation of the standard shell scripting tools (cut, sort, jq, etc) than it is to a SQL variant.
- kbouck 4y agoand miller also has a powerful (and intuitive imo.) dsl should you need to go beyond what the simple command line switches offer. so, it remains simple and concise for the easy problems, yet can scale up to address the trickier ones as well. i've used csvkit, sqlite, jq, etc. miller is my favorite tool for data-fu (with sqlite being a close 2nd)
- coldtea 4y ago>How is it better that SQL for these tasks on tabular data? It has far better handling of a CSV/TSV file on the command line directly and is compasable in shell pipelines. >My point, if one wants cat, sort, sed, join on tabular data, SOL is exactly that. SQL is a language for data in the form of tables in relational databases. While it can do sorting or joining or some changes, it is meant for a different domain than these tools, which other constraints, other concerns, and other patterns of use... You don't need to load anything to a db, for starters. You also normally don't care for involving a DB in order to use in a shell script, or for quick shell exploration. You also can't mix SQL and regular unix userland in a pipeline (well, with enough effort you can, but it's not something people do or need to do).
- mcdonje 4y agoWhile I get what they mean by "tabular JSON", that's a bit of a misnomer.
- didgetmaster 4y agoI was wondering what they meant by that term as well. Does every JSON document in the array have to have the exact same structure (including the ordering of key-value pairs)? What happens if row #1000 introduces a new key-value pair not seen before? What if the value for a key is an array?
- kbouck 4y agoIt generally means an sequence/array of flat json objects, each key represents a column name, and the value representing the row value for that column. Nested json can also be "flattened". > "... ordering of key-value pairs" order of appearance of key-value pairs in input does not matter. > "... if row #100 introduces a new key-value pair" this is sparse data. miller handles this with the "unsparsify" verb: $ cat in.json { "a": 1, "b": 2 } { "a": 3, "b": 4 } { "a": 5, "b": 6, "c": 7 } without unsparsify: $ cat in.json | mlr --j2p cat a b 1 2 3 4 a b c 5 6 7 with unsparsify: $ cat in.json | mlr --j2p unsparsify then cat a b c 1 2 - 3 4 - 5 6 7 unsparsify can also set default values: $ cat in.json | mlr --j2p unsparsify --fill-with 0 then cat a b c 1 2 0 3 4 0 5 6 7 > "... What if the value for a key is an array?" Array value treatment seems to depend on output format. for output types that can represent arrays, they are preserved: $ cat in-array.json { "a": [0,1,2], "b": [5,6,7] } { "a": [3,4,5], "b": [8,9,0] } $ cat in-array.json | mlr --jsonl cat {"a": [0, 1, 2], "b": [5, 6, 7]} {"a": [3, 4, 5], "b": [8, 9, 0]} for formats like csv/fixed-width, arrays are flattened into columns, one for each array element: $ cat in-array.json | mlr --j2p cat a.1 a.2 a.3 b.1 b.2 b.3 0 1 2 5 6 7 3 4 5 8 9 0 flatten separator can also be set: $ cat in-array.json | mlr --j2p --flatsep _ cat a_1 a_2 a_3 b_1 b_2 b_3 0 1 2 5 6 7 3 4 5 8 9 0
- password4321 4y agoDiscussion of a similar tool last month: yq: command-line YAML, JSON, XML, CSV and properties processor https://news.ycombinator.com/item?id=34656022 https://news.ycombinator.com/item?id=34656022 Also mentions gojq, Benthos, xsv, Damsel, a 2nd yq, htmlq, cfn-flip, csvq, zq, and zsv.
- networked 4y agoI have made a repository cataloguing tools like this: https://github.com/dbohdan/structured-text-tools https://github.com/dbohdan/structured-text-tools.
- account-5 4y agoThanks for this, good job. I always mean to do something similar but just end up bookmarking HN threads I then never look at.
- samstave 4y agoGuilty as charged... I have so many bookmarks I forget to look at... APPlet Idea: an app that picks a random bookmark from your saves and sends you a reminder to click it and read it... and schedule when you want to see it - like every 7AM send me a link from my bookmarks. Set an alarm for 15 minutes to get me back on schedule.
- damascus 4y agoSo chatgpt and I made this: https://github.com/erasei/randombookmark https://github.com/erasei/randombookmark Load in developer mode and when you click the icon it will open a new tab with a random bookmark.
- jefftk 4y agoThere's also GoAWK, which supports CSV natively ("-i csv"): https://github.com/benhoyt/goawk https://github.com/benhoyt/goawk
- glomgril 4y ago
- jbverschoor 4y agoSeems cool. Maybe a rainbow / alternating color per column would be nice
- deleted 4y ago[deleted]
- ar9av 4y agoLooks cool! It'd help to have some more full examples (including output) of what some common use-cases do in the README. After looking through this, I'm still scratching my head trying to think of what problem this could solve in my own practice, and I work with a lot of csv files daily
- mdaniel 4y agoIn some sense, there's no answer to your question because everyone's tool workflow is their own. That said, the most recent invocation for me was `mlr --icsv --ojson cat < a515b308-9a0e-4e4e-99a2-eafaa6159762.csv` to fix up CloudTrail csv and after that I happen to be more muscle-memory with jq but conceptually next I could have `filter '$eventname == "CreateNodegroup"'`
- deleted 4y ago[deleted]
- fdgsdfogijq 4y agoI've been using chat-GPT to work with these types of files now.
- sitkack 4y agoChatGPT is pretty good at giving working code snippets for Pandas, you can describe the transforms you want and a sketch of the data (column names and sample rows) and it will usually give back working code.
- 0xbadcafebee 4y agoCsvkit is the usual way to handle working with CSVs (or Excel, or JSON, or.... Postgres): https://csvkit.readthedocs.io/en/latest/index.html#why-csvkit https://csvkit.readthedocs.io/en/latest/index.html#why-csvki... brew install csvkit and enjoy
- hindsightbias 4y agoShell tool favorite from the past: https://github.com/stefan-schroedl/tabulator https://github.com/stefan-schroedl/tabulator
- zX41ZdbW 4y agoI recommend using clickhouse-local[1] for these tasks. It does SQL; it supports all imaginable data formats, streaming processing, and connecting to external data sources. It also outperforms every other tool[2]. [1] https://clickhouse.com/blog/extracting-converting-querying-local-files-with-sql-clickhouse-local https://clickhouse.com/blog/extracting-converting-querying-l... [2] https://colab.research.google.com/github/dcmoura/spyql/blob/master/notebooks/json_benchmark.ipynb https://colab.research.google.com/github/dcmoura/spyql/blob/...
- jsyolo 4y agoor sqlite for CSV/TSV, haven't tried it for json.
- zX41ZdbW 4y agoHow does it look from command-line for streaming processing of CSV/TSV?
- jsyolo 4y agoyou can pipe to it
- nousermane 4y agoYep: $ cat foo.tsv name foo bar Alice 10 8888 Bob 20 9999 $ cat foo.tsv | sqlite3 -batch \ -cmd ".mode tabs" \ -cmd ".import /dev/stdin x" \ -cmd "select foo from x where bar > 9000;" 20
- deleted 4y ago[deleted]
- ydant 4y agoduckdb and being able to write: select a,b,c from '*.jsonl.gz' has been a huge improvement to my workflows.
- 4y ago
- 4dayworkweek4u 4y agoThis is basically what NuShell does (with the shell -- but also with files too). Why don't these guys join the NuShell team?
- WinstonSmith84 4y agoThis is a cool tool. And it works well with GPT4. I asked ChatGPT4 the following: ``` I'd like to use Miller from John Kerl on the CSV called test.csv. It has 3 columns, Name, City and State. I'd like to filter City where it's either Chicago or Boston. I want to remove the State column and add a new column called NewColumn. This new column shall be filled with the value of the Name column appended with the letter `_new`. Finally the output shall be saved into the file called myfile.csv. Write the command to do that ``` And it replied with mlr --csv filter '$City == "Chicago" || $City == "Boston"' then cut -x -f State then put '$NewColumn = $Name . "_new"' test.csv > myfile.csv I'm sure that someone familiar with Miller would write this command faster than writing the text to GPT, but as a newbie I'd have spent much longer. Also each steps is described perfectly