24 ms·
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
by username_my1 3y ago
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
- dima55 3y agoOr the vnlog tools can do this. There are many ways to do data processing on the commandline now.
- wiredfool 3y agoLook at duckdb. Queries against csv, parquet, Jason, locally or via http. It’s like SQLite, but faster and better.
- freedude 3y agoperl with the right plugins makes data janitor situations simplified.
- ISL 3y agoThere's a chance this HN post is going to more than halve my awk-usage....
- WorldMaker 3y agoYou 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/ https://datasette.io/
- j0hnyl 3y agoHave you used duckdb? It's great for that.
- shortrounddev2 3y agoYou 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"
- rzmk 3y agoqsv (https://github.com/jqnatividad/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 3y agoYou do realize that this happened also on the article? I.e. In memory and autoinference
- llimllib 3y agonushell 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 │ ╰───┴───────────────┴────────╯
- marzell 3y agofor something a bit more robust, check out DuckDB. It's a library you can embed, use it to run SQL on local files as well as connect to databases, do joins, analytics, etc.
- scumola 3y agoAgreed. The article mentioned duckdb and I'm her to thumbs-up the use of DuckDB wholeheartedly. If you like the world of public CSV files as data sources that you can query or cross-query, duckdb is the tool for you. Just follow the demo on the duckdb website and you'll be wow'd for sure.
- fiddlerwoaroof 3y agoThe 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.
- whartung 3y agoWhat’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 3y agoAWK's new CSV and UTF-8 support is great, but when querying data I think in terms of SQL.