Miller is a powerful command line tool to query and convert data from CSV, TSV, JSON. Here this is a cross-platform tool that you can use to extract./filter data from a CSV or JSON file with conditions. It comes with tons of flags and syntaxes that you can use to query data. With this, you can get what you want and in any way you like. It supports aggregate functions of Excel as well in case you want to get sum, average, minimum or maximum from a specific CSV column.
We have covered tools to query CSV using SQL before, but here Miller is the most advanced one yet. You can use it on Linux, MAC, and Windows right from their binary builds. It comes with a detailed documentation that you have to go through in order to understand the complete working. You can read the data from a file or from standard input and then reshape it the way you want. There are some custom functions built in that you can for conditional formatting.
Free tool to query, convert data from CSV, TSV, JSON: Miller
You can download the Miller binary from here. No matter what platform you are on, you will be able to use it. After downloading the binary file, “mlr” is some separate folder. Or, you can move it to system folders such as “/us/bin” or “C:/Windows/“, etc.
Now, you can download or create a sample CSV file in the directory that you want to use. The very basic syntax to read a CSV file and filter by a specific column is below.
Syntax:./mlr --csv sort -f Sorting_Column CSV_FileExample:./mlr --csv sort -f marks test.csv
In the output above, you can see how it works. This is the most basic syntax that you use. See some more complicated commands below.
You can use mlr head and mlr tail to return number of rows from the end of beginning. See the syntax below to get the last 3 rows.
/mlr --csv head -n 3 cities.csv
To convert the CSV to JSON, you can issue the command below. Or, you can pipe the output to a file to save it. The command below will convert the first row to JSON. If you want to increase the number of rows, then you can simply change the n parameter. Or simply remove it to convert the entire CSV file to JSON.
./mlr --icsv --ojson head -n 1 test.csv
As I mentioned earlier that it supports aggregate functions, so you can use them. Count, Min, Max, and Mean are available. Use the following command syntax to do that. The following command lists stats from the “marks” column.
./mlr --icsv --opprint --from test.csv stats1 -a count,min,mean,max -f marks
In the screenshots above, you can see the output generated by the various commands. I have explained the basic functioning of this command line tool above. Rest you can head to the official docs and then see the syntax for advanced usage such as system functions, user defined functions, and more. There are examples for converting the data as well as converting JSON arrays and some more.
Final thoughts:
If you want to query JSON/CSV files in the way you want, then Miller is the tool you need. I have explained some of the most useful commands above already. But you can go through the docs to learn the advanced usage. It is an amazing tool that you can use to reshape data from CSV files and more. So, give it a try on your own and let me know what you think about it.
