This would make a nice complement to xsv and csvkit but really needs to support python3. That is the main downside to using it.
Q: Run SQL Directly on CSV Files
81–90 of 102 posts
Re: Q: Run SQL Directly on CSV Files
#82Re: Q: Run SQL Directly on CSV Files
#83This would make a nice complement to xsv and csvkit but really needs to support python3. That is the main downside to using it.
csvkit actually supports SQL queries on csv files. But performance is pretty poor.
Re: Q: Run SQL Directly on CSV Files
#84“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...
oh, I love that! I'm going to use these delimiters from now on.
Re: Q: Run SQL Directly on CSV Files
#85Earlier quoted context omitted.
Yes, it would be valid CSV. I suppose my point is that naive attempts to roll-your-own CSV parsers tend to fail on the points I listed. Hopefully Q does not do that.
They do? Commas and quotes are the two basic features of CSV, so it seems very strange to forget to implement half.
[deleted]
Re: Q: Run SQL Directly on CSV Files
#86Earlier quoted context omitted.
They do? Commas and quotes are the two basic features of CSV, so it seems very strange to forget to implement half.
There is a lot of inconsistency out there. I have seen csv files saved in Excel not be import-able by Access because the latter doesn't handle breaks in fields correctly. I've seen csvs saved from various systems such as sql mngmnt studio grid view and wufoo exports not generate csv correctly. There are many lazy attempts at csv generators out there that just throw breaks between records and commas between fields and…
And even if they do wrap all fields in double quotes it is very common to forget to escape double quotes in fields, then it depends on the parser as to whether it can determine the proper structure of the record.
Re: Q: Run SQL Directly on CSV Files
#87Re: Q: Run SQL Directly on CSV Files
#88I've done a similar thing, but using jq syntax on any object-like file: https://github.com/jzelinskie/faq Ya know how some people want everything to be vim after they use it? I'm surprised more people haven't done that with other tools.
> faq is pronounced "fah queue".
So... "fuck you"?
Re: Q: Run SQL Directly on CSV Files
#89Fun fact: MySQL has actually supposed CSV files as a storage backend for quite some time now[1] 1: https://dev.mysql.com/doc/refman/8.0/en/csv-storage-engine.h...
Came here to post the same. This can be used in very interesting ways, either for quick ad-hoc queries or for import/export functionality (ETL), where the core of your data is in InnoDB but you keep a CSV version for one of the phases of your processing, to interface with other tools.
This may looks like a dirty hack compared to a clean API, but if you have performance considerations or need to interface with some legacy thing that happens to understand CSV, this can be a good choice.
Re: Q: Run SQL Directly on CSV Files
#90Earlier quoted context omitted.
> the ASCII standard has had unit- and record-delimiters baked into it from the beginning. That's mad that I've never heard of that before. I'm amazed this isn't used more often!
They don’t really get used IMO because if you’re storing ASCII or Unicode text, you have to be prepared for those characters to be in your data. And if you’re storing binary data, delimiters don’t really cut it in the first place.
No it's fine. Just base64 encode the binary data. It's common and streams, or you can capture and convert the entire field at once if you need to seek around in the binary data.