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

31–40 of 102 posts

Re: Q: Run SQL Directly on CSV Files

#31

Post by qwerty456127 »

iblaine wrote: post #7

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

> you can get by with some simple bash string operations. Doing that same operation w/SQL seems like overkill.

Coding bash seems an overkill for just so many people. Bash is scary while SQL is intuitive by design.

Re: Q: Run SQL Directly on CSV Files

#32

Post by barrkel »

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?

More interesting: any kind of delimiter, including chars from utf8 and windows-1252, and you need to detect encoding too. And CSV embedded in CSV, a result of flattening an XML source. And fixed width files, not CSV but where you see CSV you may need to support. And let's not get into date parsing or other typed data, and type inference over sample files.

Re: Q: Run SQL Directly on CSV Files

#33

Post by snazz »

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?

For those reasons I much prefer tab-delimited files. Does anyone know if Q supports that?

Re: Q: Run SQL Directly on CSV Files

#34

Post by Avshalom »

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?

Without diving into the source code I can only say Q pops up a couple times a year in either as posts or in cli recommendation threads so I suspect it's at least reasonably robust.

Re: Q: Run SQL Directly on CSV Files

#35

Post by Dylan16807 »

codeulike wrote: post #24
setr wrote: post #12

Earlier quoted context omitted.

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 hand…

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.

Re: Q: Run SQL Directly on CSV Files

#39

Post by brianm »

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

Ability to eyeball it on terminal is huge though :-(

Never underestimate ergonomics like screen printability!

Re: Q: Run SQL Directly on CSV Files

#40

Post by 83457 »

Dylan16807 wrote: post #35
codeulike wrote: post #24

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

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 call it a day.
Post reply on HN