Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Q – Run SQL Directly on CSV or TSV Files

harelba.github.io

Post reply on HN
Print view View original

11–20 of 62 posts

Re: Q – Run SQL Directly on CSV or TSV Files

#11

Post by cm2187 »

Another solution would be to leverage the existing SQL engines.

Someone mentioned sqlite virtual table.

My home made solution to the very same problem is creating a simple winform that you can drag and drop spreadsheets and csv files onto, analyses them, creates an instance of localdb if there isn't one running, creates the table and uploads the data (so it reads the csv file twice). Then I can use my loved and trusted SQL Server Management studio with the MS SQL engine. The same UI allows to quickly delete tables and databases and create new database in two clicks. (future development: auto-normalise the table to reduce disk space and improve performance).

What lacks is good import tools. Most csv import tools are super picky in term of the format of the data (dates in particular) and have too many steps.

Re: Q – Run SQL Directly on CSV or TSV Files

#12

Post by eatonphil »

cm2187 wrote: post #11

Another solution would be to leverage the existing SQL engines. Someone mentioned sqlite virtual table. My home made solution to the very same problem is creating a simple winform that you can drag and drop spreadsheets and csv files onto, analyses them, creates an instance of localdb if there isn't one running, creates the table and uploads the data (so it reads the csv file twice). Then I can use my loved and trust…

Q is built on top of sqlite. :)

Re: Q – Run SQL Directly on CSV or TSV Files

#13

Post by jfoutz »

redsaz wrote: post #4

A satisfied user here. Found it very useful when tools like cut and sort weren't enough, usually when I need to do a join on two different tables (err, files). Left joins work, but I don't think right joins are supported. I've used this in combination with jq as well. I'll use jq to convert json to CSV, and then use SQL to do whatever else.

Have you looked at the Unix command ‘join’? This is a cool tool, but I think join is pretty much everywhere.

Re: Q – Run SQL Directly on CSV or TSV Files

#14

Post by WASDx »

redsaz wrote: post #4

A satisfied user here. Found it very useful when tools like cut and sort weren't enough, usually when I need to do a join on two different tables (err, files). Left joins work, but I don't think right joins are supported. I've used this in combination with jq as well. I'll use jq to convert json to CSV, and then use SQL to do whatever else.

You also probably have GNU join installed: https://www.gnu.org/software/coreutils/manual/html_node/join...

Re: Q – Run SQL Directly on CSV or TSV Files

#15

Post by SoftTalker »

redsaz wrote: post #4

A satisfied user here. Found it very useful when tools like cut and sort weren't enough, usually when I need to do a join on two different tables (err, files). Left joins work, but I don't think right joins are supported. I've used this in combination with jq as well. I'll use jq to convert json to CSV, and then use SQL to do whatever else.

Same. You can go a long way with cut, sort, etc. and also awk with its pattern matching. But if you're handy with SQL, that can often feel more natural and certainly things like joins among separate CSV files, as well as sums and other aggregates, are easier.

If you have "unclean" CSV data, e.g. where the data contains delimiters and/or newlines in quoted fields, you might want to pipe it through csvquote.

https://github.com/dbro/csvquote

Re: Q – Run SQL Directly on CSV or TSV Files

#17

Post by MilStdJunkie »

Reminds me of the textQL extension that's available in Asciidoc.

Point it to an external CSV file, enable TextQL, and bam, there's your query returned as a table. Handy for parts lists, inventory, that kind of crap.

https://github.com/dinedal/textql

https://gist.github.com/mojavelinux/8856117

Re: Q – Run SQL Directly on CSV or TSV Files

#19

Post by SoftTalker »

Previous discussion:

https://news.ycombinator.com/item?id=18453133 (284 points|devy|4 years ago|96 comments)

https://news.ycombinator.com/item?id=27423276 (121 points|thunderbong|1 year ago|63 comments)

https://news.ycombinator.com/item?id=24694892 (11 points|pcr910303|2 years ago|2 comments)

Re: Q – Run SQL Directly on CSV or TSV Files

#20

Post by margarina72 »

eatonphil wrote: post #2

Q is a great project! Here's a comparison of Q against some of the other tools out there [0] (including dsq, which I wrote). And there's a benchmark in there too [1]. Whichever tool you end up using, I'm sure it will help out with your CLI data exploration! [0] https://github.com/multiprocessio/dsq#comparisons [1] https://github.com/multiprocessio/dsq#benchmark

dsq is a great tool, using it regularly to work against csv files, and really nice to use.
Post reply on HN