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

Q: Run SQL Directly on CSV Files

harelba.github.io

Post reply on HN
Print view View original

41–50 of 102 posts

Re: Q: Run SQL Directly on CSV Files

#41

Post by yellowapple »

Another trick along these lines is to cut out the middleman (middleprogram? middleware?) and use SQLite to do it:

    $ sqlite3
    sqlite3> .mode csv
    sqlite3> .import foo.csv foo
    sqlite3> SELECT * FROM foo WHERE bar = 'baz'; -- you get the gist...
    (a bunch of rows)
Q seems to be much easier to use (and certainly easier to remember), but I've always found it handy to have the full power of SQL at my fingertips when needing to do a bunch of CSV manipulations.

Re: Q: Run SQL Directly on CSV Files

#42

Post by ppande »

Sqlite virtual tables. You can literally query any data source. All you need is a module implementation of interface exposed by sqlite for the data source of interest. I once wrote a module that could query protocol buffers encoded data

Re: Q: Run SQL Directly on CSV Files

#43

Post by Cyph0n »

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

There is also a similar TSV manipulation tool suite written in D: https://github.com/eBay/tsv-utils

Re: Q: Run SQL Directly on CSV Files

#44

Post by bhengaij »

rudolfwinestock wrote: post #29

“Any file is a database if you awk hard enough.” —Bryan Horstmann-Allen https://twitter.com/neilkod/status/914217352564137984 Furthermore, no thread on CSV files can be complete without mentioning this infamous bit of fact-trolling: the ASCII standard has had unit- and record-delimiters baked into it from the beginning. https://ronaldduncan.wordpress.com/2009/10/31/text-file-form...

And they're literally named thus too. US, FS.

We just need an rcat to print them clearly.

Re: Q: Run SQL Directly on CSV Files

#45

Post by bhengaij »

delinka wrote: post #8

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-...

Been on windows for 18 years now, never heard of this before. Sometimes I wonder if Microsoft really sucks at marketing dev tools

Re: Q: Run SQL Directly on CSV Files

#46

Post by bhengaij »

devy wrote: post #4
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.

His name is Burnt Sushi I think.

Re: Q: Run SQL Directly on CSV Files

#49

Post by samatman »

codeulike wrote: post #6

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?

Just this weekend was filtering commas out of unquoted dollar values in a CSV
Post reply on HN