[username_my1]

it blows my mind that you can use sqlite with csv as input and then query it, it sounds so logical and useful yet I never came by it.

we have lots of reporting in CSV, can't wait to start using it to run queries quickly

[shortrounddev2]

You should checkout powershell; it supports converting CSV into in-memory structured data and then you can run regular powershell queries on that data:

    $> csvData = @"
       Name,Department,Salary
       John Doe,IT,60000
       Jane Smith,Finance,75000
       Alice Johnson,HR,65000
       Bob Anderson,IT,71000
       "@;
    
    $> csvData
         | ConvertFrom-Csv
         | Select Name, Salary
         | Sort Salary -Descending
    
       Name          Salary
       ----          ------
       Jane Smith    75000
       Bob Anderson  71000
       Alice Johnson 65000
       John Doe      60000

You can also then convert the results back into CSV by piping into ConvertTo-Csv

    $> csvData
         | ConvertFrom-Csv
         | Select Name, Salary
         | Sort Salary -Descending
         | ConvertTo-Csv
         
       "Name","Salary"
       "Jane Smith","75000"
       "Bob Anderson","71000"
       "Alice Johnson","65000"
       "John Doe","60000"

[llimllib]

nushell does too:

    /tmp/> "Name,Department,Salary
    ::: John Doe,IT,60000
    ::: Jane Smith,Finance,75000
    ::: Alice Johnson,HR,65000
    ::: Bob Anderson,IT,71000" |
    :::     from csv | 
    :::     select Name Salary | 
    :::     sort-by -r Salary
    ╭───┬───────────────┬────────╮
    │ # │     Name      │ Salary │
    ├───┼───────────────┼────────┤
    │ 0 │ Jane Smith    │  75000 │
    │ 1 │ Bob Anderson  │  71000 │
    │ 2 │ Alice Johnson │  65000 │
    │ 3 │ John Doe      │  60000 │
    ╰───┴───────────────┴────────╯

[rzmk]

qsv (https://github.com/jqnatividad/qsv) also has a sqlp command which lets you run Polars SQL queries on CSV(s).

Here I'll:

  - Send the csv data from stdin (using echo and referred to in the command by -)
  - Refer to the data in the query by stdin. You may also use the _t_N syntax (first table is _t_1, then _t_2, etc.), or the file name itself before the .csv extension if we were using files.
  - Pipe the output to the table command for formatting.
  - Also, the shape of the result is printed to stderr (the (4, 2) below).

  $ echo 'Name,Department,Salary
    John Doe,IT,60000
    Jane Smith,Finance,75000
    Alice Johnson,HR,65000
    Bob Anderson,IT,71000' |
    qsv sqlp - 'SELECT Name, Salary FROM stdin ORDER BY Salary DESC' |
    qsv table
    
    (4, 2)
    Name           Salary
    Jane Smith     75000
    Bob Anderson   71000
    Alice Johnson  65000
    John Doe       60000

[smooc]

You do realize that this happened also on the article? I.e. In memory and autoinference

[WorldMaker]

You might find a lot of interesting tools in the Datasette ecosystem. Data dashboarding for SQLite with all sorts of import and export and visualization plugins.

https://datasette.io/

[whartung]

What’s really interesting about it is that Awk is now, finally, getting support for CSV. But I bet a large amount of Awk+CSV use cases can be met with SQLite and SQL+CSV.

[nuc1e0n]

AWK's new CSV and UTF-8 support is great, but when querying data I think in terms of SQL.

[fiddlerwoaroof]

The clickhouse-local tool is also really great for querying a bunch of systems, and it has connectors and converters for a whole bunch of other systems and formats.

[j0hnyl]

Have you used duckdb? It's great for that.

[wiredfool]

Look at duckdb. Queries against csv, parquet, Jason, locally or via http. It’s like SQLite, but faster and better.

[dima55]

Or the vnlog tools can do this. There are many ways to do data processing on the commandline now.

[ISL]

There's a chance this HN post is going to more than halve my awk-usage....

[freedude]

perl with the right plugins makes data janitor situations simplified.