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

61–70 of 102 posts

Re: Q: Run SQL Directly on CSV Files

#61

Post by dima55 »

usgroup wrote: post #16

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

There's also this:

  https://github.com/dkogan/vnlog
This is effectively a set of wrappers around awk, sort, join, etc that knows about field names, and lets you use the vanilla shell tools in a much nicer way. It's exactly what you want for the 99% use case. If your problem is complicated enough that you feel like sql is what you want, maybe leaving the shell is ok at that point.

Re: Q: Run SQL Directly on CSV Files

#63

Post by singingfish »

walshemj wrote: post #22
Tepix wrote: post #9

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

Text::CSV_XS is my goto module for csv just use that and whack it into MySQL postgress would be my advice.

nah sqlite is what you want when you want a step up from CSV.

Re: Q: Run SQL Directly on CSV Files

#65

Post by hyperbovine »

z92 wrote: post #55
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...

Usability suffers, if you can't type the delimiters easily.

I can’t stand it when I’m banging out a CSV by hand and have to manually escape the field and record separators. Happens all the time.

Re: Q: Run SQL Directly on CSV Files

#66

Post by gdfasfklshg4 »

brianm wrote: post #39
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!

If people used them then the characters would almost certainly be represented in terminals as table spacing characters...

Re: Q: Run SQL Directly on CSV Files

#69

Post by rusk »

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

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

Re: Q: Run SQL Directly on CSV Files

#70

Post by zimpenfish »

herdrick wrote: post #10

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

Discovered csvkit last week and it was a lifesaver. Had to remove a bunch of columns from a large CSV file with many columns - csvcut was way easier than trying to do it with awk or cut.
Post reply on HN