One of my all-time favorite (and somehow still-obscure / relatively unknown) tools is called `lnav` ^1. It's a mini-ETL powertool with embedded SQLite, perfect for wrangling log files or other semi-structured data (a few millions of rows are no problem), it's intuitive and flexible...
One-liner for running queries against CSV files with SQLite
51–60 of 131 posts
Re: One-liner for running queries against CSV files with SQLite
#52This is far more useful for SQL users than chaining several sed/awk/sort commands on pipe (although a bit against nix principles).
For a *nixy approach, try lnav (https://lnav.org)
Re: One-liner for running queries against CSV files with SQLite
#53the .import command used for actually loading the CSV is kinda picky about your CSVs being well-formatted. I don't think it supports embedded newlines at all.
Re: One-liner for running queries against CSV files with SQLite
#54Using DuckDB [1]: duckdb -c "SELECT passenger_count, COUNT(*), AVG(total_amount) FROM taxi.csv GROUP BY ALL" DuckDB will automatically infer you are reading a CSV file from the extension, then automatically infer column names from the header, together with various CSV properties (data types, delimiter, quote type, etc). You don't even need to quote the table name as long as the file is in your current directory and t…
A bit clunky, but works.
ps | awk '$1=$1' OFS=, | duckdb :memory: "select PID,TTY,TIME from read_csv_auto('/dev/stdin')"Re: One-liner for running queries against CSV files with SQLite
#55Earlier quoted context omitted.
How does the column data type inference work? I've run into that challenge myself in the past.
The CSV auto-detector was implemented by Till Döhmen, who did his master thesis on the subject [1] and has actually written a paper about it [2]. Essentially we have a list of candidate types for each column (starting with all types). We then sample a number of tuples from various parts of the file, and progressively reduce the number of candidate types as we detect conflicts. We then take the most restrictive type f…
This is fantastic, thanks.
My solution is a lot less smart - I loop through every record and keep track of which potential types I've seen for each column: https://sqlite-utils.datasette.io/en/latest/python-api.html#...
Implementation here: https://github.com/simonw/sqlite-utils/blob/3fbe8a784cc2f3fa...
Re: One-liner for running queries against CSV files with SQLite
#56I am a data scientists. I have used a lot of tools/libraries to interact with data. SQLite is my favorite. It is hard to beat the syntax/grammar. Also, when I use SQLite I do not output using column mode. I pipe to `tv` (tidy-viewer) to get a pretty output. https://github.com/alexhallam/tv transparency: I am the dev of this utility
piping to jq (using json mode of course) also works well for this
Re: One-liner for running queries against CSV files with SQLite
#57Another great tool written in Go is CSVQ [0][1] that can be used as a command line or a library.
csvq 'select id, name from `user.csv`'
Re: One-liner for running queries against CSV files with SQLite
#58Since many people are sharing one-liners with various tools... OctoSQL[0]: octosql 'SELECT passenger_count, COUNT(*), AVG(total_amount) FROM taxi.csv GROUP BY passenger_count' It also infers everything automatically and typechecks your query for errors. You can use it with csv, json, parquet but also Postgres, MySQL, etc. All in a single query! [0]: https://github.com/cube2222/octosql Disclaimer: author of OctoSQL
I love the simplicity. Is there support for joins / use of multiple tables?
Re: One-liner for running queries against CSV files with SQLite
#59sqlite3 :memory: -cmd '.mode csv' ... It should be a war crime for programs in 2022 to use non-UNIX/non-GNU style command line options. Add it to the Rome Statute's Article 7 list of crimes against humanity. Full blown tribunal at The Hague presided over by the international criminal court. Punishable by having to use Visual Basic 3.0 for all programming for the rest of their life.
Let me introduce you to bazel ...
bazel build //:--foobar --//::\\
Re: One-liner for running queries against CSV files with SQLite
#60Can somebody post the equivalent in Windows cmd?