Q: Run SQL Directly on CSV Files

#1

Post by devy » Wed, Nov 14, 2018, 7:40 PM UTC

Q: Run SQL Directly on CSV Files
harelba.github.io

Re: Q: Run SQL Directly on CSV Files

#2

Post by cbcoutinho » Wed, Nov 14, 2018, 8:45 PM UTC

I've never heard of this before, but it reminded me of this other command line tool for wrangling csv files in a cli. This tool makes use of SQL syntax much more than xsv, so there isn't 100% overlap here.

https://github.com/BurntSushi/xsv

Re: Q: Run SQL Directly on CSV Files

#3

Post by minxomat » Wed, Nov 14, 2018, 8:46 PM UTC

So is this a wrapper around https://www3.sqlite.org/csv.html ? It mentions sqlite.

Re: Q: Run SQL Directly on CSV Files

#4

Post by devy » Wed, Nov 14, 2018, 8:47 PM UTC

cbcoutinho wrote: post #2

I've never heard of this before, but it reminded me of this other command line tool for wrangling csv files in a cli. This tool makes use of SQL syntax much more than xsv, so there isn't 100% overlap here. https://github.com/BurntSushi/xsv

Thanks! xsv is new to me. I love Andrew Gallant's ripgrep(rg) grepping cmd tool.

Re: Q: Run SQL Directly on CSV Files

#5

Post by hprotagonist » Wed, Nov 14, 2018, 8:52 PM UTC

Related, but with a free web interface https://github.com/simonw/datasette , https://github.com/simonw/csvs-to-sqlite

Re: Q: Run SQL Directly on CSV Files

#6

Post by codeulike » Wed, Nov 14, 2018, 8:57 PM UTC

Real-world CSV files generally contain some or all of the following horrors:

- some strings enclosed in speechmarks, but some not

- empty fields

- speechmarks within strings

- commas within strings

- carriage returns within strings

How does Q do up against a CSV file with those traits?

Re: Q: Run SQL Directly on CSV Files

#7

Post by iblaine » Wed, Nov 14, 2018, 8:59 PM UTC

> Have you ever stared at a text file on the screen, hoping it would have been a database so you could ask anything you want about it?

Not really. I could see this being helpful for debugging, but at that point you can get by with some simple bash string operations. Doing that same operation w/SQL seems like overkill.

Re: Q: Run SQL Directly on CSV Files

#8

Post by delinka » Wed, Nov 14, 2018, 9:05 PM UTC

If you're on Windows, you've had the ability to do this for Quite Some Time®: https://support.microsoft.com/en-us/help/850320/creating-an-...

Re: Q: Run SQL Directly on CSV Files

#9

Post by Tepix » Wed, Nov 14, 2018, 9:17 PM UTC

The perl CPAN module DBD::CSV also lets you do this. It was started in 1998 and still gets updates.

Re: Q: Run SQL Directly on CSV Files

#10

Post by herdrick » Wed, Nov 14, 2018, 9:34 PM UTC

csvkit includes csvsql, which does this. I’ve used it and liked it. https://csvkit.readthedocs.io/en/1.0.3/tutorial/3_power_tool...