Eep, no Python 3 support? :(
Q: Run SQL Directly on CSV Files
11–20 of 102 posts
Re: Q: Run SQL Directly on CSV Files
#12Real-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?
All of your “horrors” seem...correct? Its comma delimited, so anything that is between two commas should be parsed without issue; if it’s a string with a comma in it, and unquoted, you simply have a broken csv file. If its quoted, than anything until the next (unescaped) quote is fine, including commas
Unless you’re trying to parse csv files with regexes, none of those should be difficult, or even unexpected, to handls with a PEG parser, or any equivalent device
Ofc if you’re accepting ambiguity then its just arbitrary how you handle it, but none of your examples afaict present any ambiguity (I’m assuming strings are either quoted or unquoted, with the former primarily allowing commas/newlines in strings; escaping exists as well; comma delimited columns, newline delimited rows)
Re: Q: Run SQL Directly on CSV Files
#13Looks like PostgreSQL has a foreign data wrapper for CSV.
https://wiki.postgresql.org/wiki/Foreign_data_wrappers
In fact, seems it has several.
Re: Q: Run SQL Directly on CSV Files
#14So is this a wrapper around https://www3.sqlite.org/csv.html ? It mentions sqlite.
Looks like it is implemented in python(2) with use of the stdlib sqlite3 module. https://github.com/harelba/q/blob/master/bin/q
Re: Q: Run SQL Directly on CSV Files
#15> 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.
Maybe my bash just isn't good enough anymore, but I do this all the time. In fact, I was just doing some work this morning where I needed to load a CSV into SQLite for analysis.
Since I still had the file, I ran some queries on the same file via Q to test it out. And it works great! (With some handholding to get quoting and delimiters right). The major downside was it's pretty slow, presumably because it reloads the file into a SQLite database each time you run a command. So, I'll probably stick with loading CSVs into SQLite myself, but I could see this being a useful tool for running one-off analysis on data from stdin.
Re: Q: Run SQL Directly on CSV Files
#16I think tools like this don’t exist much because it typically makes more sense to just put it into a database or at least SQLite ... then do whatever you want from there.
Meanwhile for all your streaming, filtering and aggregating need there is awk.
I’ve never been board enough to write my own little sql library for awk, but I’d be surprised if it doesn’t exist.
Re: Q: Run SQL Directly on CSV Files
#17If 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-...
Ah, I thought you were going to link to the fascinating skunkworks Log Parser tool https://www.microsoft.com/en-us/download/details.aspx?id=246... Wonder if it still runs on Windows 10?
Re: Q: Run SQL Directly on CSV Files
#18> 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.
The devil you know; I find myself with the opposite problem on occasion. "If only I could just grep and awk out what I wanted instead of using some contrived query and arcane data connectors"
Re: Q: Run SQL Directly on CSV Files
#19I think tools like this don’t exist much because it typically makes more sense to just put it into a database or at least SQLite ... then do whatever you want from there. Meanwhile for all your streaming, filtering and aggregating need there is awk. I’ve never been board enough to write my own little sql library for awk, but I’d be surprised if it doesn’t exist.
That's exactly what q (the linked tool) and almost all other tools like it do - it loads into an sqlite database and provides some handy wrappers, that's it.
Re: Q: Run SQL Directly on CSV Files
#20I think tools like this don’t exist much because it typically makes more sense to just put it into a database or at least SQLite ... then do whatever you want from there. Meanwhile for all your streaming, filtering and aggregating need there is awk. I’ve never been board enough to write my own little sql library for awk, but I’d be surprised if it doesn’t exist.
Sqawk is an Awk-like program that uses SQL and can combine data from multiple files. It is powered by SQLite.