XAN: A Modern CSV-Centric Data Manipulation Toolkit for the Terminal

I prefer to use clickhouse-local for all my CSV needs as I don't need to learn a new language (or cli flags) and can just leverage SQL.

    clickhouse local --file medias.csv --query "SELECT edito, count() AS count from table group by all order by count FORMAT PrettyCompact"

   ┌─edito──────┬─count─┐
   │ agence     │     1 │
   │ agrégateur │    10 │
   │ plateforme │    14 │
   │ individu   │    30 │
   │ media      │   423 │
   └────────────┴───────┘

Can't help but thinking how handy PowerShell is out of the box for tasks like this.

Translating the examples from the ReadMe, having read the file with:

  $medias = Get-Content .\medias.csv | ConvertFrom-Csv

  xan view medias.csv
  $medias | Format-Table

  xan flatten -c medias.csv
  $medias | Format-List

  xan search -s outreach internationale medias.csv | xan view
  $medias | Where-Object { $_.outreach -eq "internationale" } | Format-Table

  xan select foundation_year,name medias.csv | xan view
  $medias | Select-Object -Property foundation_year, name | Format-Table

  xan sort -s foundation_year medias.csv | xan view -s name,foundation_year
  $medias | Sort-Object -Property foundation_year | Select-Object -Property name, foundation_year | Format-Table

  # Some medias of our corpus have the same ids on mediacloud.org
  xan dedup -s mediacloud_ids medias.csv | xan count && xan count medias.csv
  $medias | Select-Object -ExpandProperty mediacloud_ids -Unique | Measure-Object; $medias | Measure-Object -Property mediacloud_ids

  xan frequency -s edito medias.csv | xan view
  $medias | Group-Object -Property edito | Sort-Object -Property Count -Descending

I was going to mention BurntSushis excellent xsv, also written in rust ... but I see xan mantions its a fork + rewrite of xsv.

I use Pandas for most of my CSV work. It's super fast and very powerful. There's a bit of a learning curve. I can then use Python scripts to manipulate massive CSV files.

I tend to use csvkit for more complicated transformations, and OCaml's csvtool[0] for the simpler ones. For intermediate transformations I wrote my own csved[1] script, which reads for every line of a CSV reads it into @F, applies a Perl expression to that array, then writes it out. With the -h option you can also use the %F hash to access fields by name. It's very fast.

It looks like xsv and xan are in the "csvkit but faster" niche, which is nice, but now I must learn another set of commands.

And there are now many more recent utilities called csvtool, including a Perl and a Python one.

[0] https://github.com/Chris00/ocaml-csv

[1] https://github.com/gpvos/csved

People will do anything to avoid learning SQL.

I recently came across miller (https://github.com/johnkerl/miller). I don't know how these tools compare.

Something that would be insanely useful is if your tool could be users to do validations.

For example being able to define data types for each column and say required columns. And then run your tool as a validator and take the errors as an array that’d be amazing!

Coming from a dev who’s just over processing CSV files back into my apps.

This looks really nice to use!

There are a lot of tools that one can use on Linux cli to work with csv. But many of them have become unmaintained. Or have terrible docs. Or have really awkward usage (looking at you, “yq”).

See also: csvkit (https://csvkit.readthedocs.io)

I am using Goawk with good results

This could be a nice pre-filter for VisiData…?

I appreciate tools for CSV to tame that ungodly mess. But I loathe attempts to glorify the cursed and putrid sack of shit that it really is.