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

Re:Miller CLI – Like Awk, sed, cut, join, and sort for CSV, ... by yung_steezy on Wednesday August 25, 2021 @06:10AM (#28299729)

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.

[ Parent | on HN ]

Re:Miller CLI – Like Awk, sed, cut, join, and sort for CSV, ... by majkinetor on Wednesday August 25, 2021 @11:02AM (#28302499)

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

[ Parent | on HN ]

Re:Miller CLI – Like Awk, sed, cut, join, and sort for CSV, ... by noisy_boy on Wednesday August 25, 2021 @10:50AM (#28302364)

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

[ Parent | on HN ]

Re:Miller CLI – Like Awk, sed, cut, join, and sort for CSV, ... by tyingq on Wednesday August 25, 2021 @08:38AM (#28300871)

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

[ Parent | on HN ]

Re:Miller CLI – Like Awk, sed, cut, join, and sort for CSV, ... by psanford on Wednesday August 25, 2021 @11:45AM (#28303106)

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

[ Parent | on HN ]

Re:Miller CLI – Like Awk, sed, cut, join, and sort for CSV, ... by inanutshellus on Wednesday August 25, 2021 @09:27AM (#28301369)

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

[ Parent | on HN ]

Re:Miller CLI – Like Awk, sed, cut, join, and sort for CSV, ... by sdmac99 on Wednesday August 25, 2021 @07:07AM (#28300068)

duckdb is another good option as well.

[ Parent | on HN ]

[dead]

Re:Miller CLI – Like Awk, sed, cut, join, and sort for CSV, ... by halayli on Wednesday August 25, 2021 @08:15AM (#28300645)

If there is anything ugly here, it's your communication.

Aside from the fact that OP's example can run in a non-interactive mode, it makes absolutely no difference to the discussion.

[ Parent | on HN ]

Re:Miller CLI – Like Awk, sed, cut, join, and sort for CSV, ... by skrebbel on Wednesday August 25, 2021 @06:43AM (#28299943)

You can't understand that there are people who know SQL and not R?

[ Parent | on HN ]

Re:Miller CLI – Like Awk, sed, cut, join, and sort for CSV, ... by momirlan on Wednesday August 25, 2021 @07:13AM (#28300107)

I don't think the comment refers to people, just to better ways of doing things. People learn.

[ Parent | on HN ]

Re:Miller CLI – Like Awk, sed, cut, join, and sort for CSV, ... by lugged on Wednesday August 25, 2021 @07:16AM (#28300129)

In my experience people rarely learn anything after you insult them and call their work ugly.

If AP had a "better way of doing things" maybe they should have actually given us an example of what it looks like.

Right now, all I think about R is that snobby, gatekeeping assholes use it to do things I can already do easily in SQL.

[ Parent | on HN ]