CSV as a Data Source • Hacker News Board

Re: CSV as a Data Source

#11

Post by thingsilearned » Thu, Aug 01, 2013, 6:48 PM UTC

jeremysmyth wrote: post #8

MySQL has supported CSV as a data source for quite some time: http://dev.mysql.com/doc/refman/5.6/en/csv-storage-engine.ht... You can run full SQL queries directly against a text file as if it was a table.

Yeah, and that's how we used to direct customers who wanted upload CSV data. We even wrote some nice tutorials on how to set it up

https://chartio.com/education/databases/excel-to-mysql

But we found that many people either aren't technical enough, or didn't want to go through the hassle of setting up a MySQL instance, defining a schema, and cleaning the data. So now we do that for them.

Re: CSV as a Data Source

#12

Post by alayne » Thu, Aug 01, 2013, 6:50 PM UTC

tbrownaw wrote: post #7

The biggest issue I have with csv files, is when the people sending them don't get the $%^&@ quotes right . The second biggest issue, is when they say "sorry, we're not able to do that" after I tell them exactly how to fix what they're sending.

I prefer DSV files for this reason http://en.wikipedia.org/wiki/Delimiter-separated_values

I have a simple scheme where comma is the delimiter and , in content is escaped as \,. There are no quotes around values.

Re: CSV as a Data Source

#13

Post by tbrownaw » Thu, Aug 01, 2013, 6:50 PM UTC

akinder wrote: post #10

tbrownaw wrote: post #7

The biggest issue I have with csv files, is when the people sending them don't get the $%^&@ quotes right . The second biggest issue, is when they say "sorry, we're not able to do that" after I tell them exactly how to fix what they're sending.

What is the proper way to encode the quotes? I'm playing around with writing a Livejournal data dump reader and will have to do the quote dance pretty soon. In LJ files they are encoded like so: col1,col2,col3,"Longer data and somethin ""with"" quotes",col5

Yes, that's how they're supposed to be. Any field with quotes, or commas, or newlines in the data needs to have quotes around it. Any quotes in the data need to be escaped by preceding them with another quote.

Re: CSV as a Data Source

#14

Post by dergachev » Thu, Aug 01, 2013, 6:56 PM UTC

If you use SQL, check out http://github.com/dergachev/csv2sqlite

Its a little ruby script that in 1 command takes one or several CSV files, parses their structure into simplistic sqlite table definitions, and then creates a new sqlite database file populated with structure and data from these CSVs.

Re: CSV as a Data Source

#15

Post by sehrope » Thu, Aug 01, 2013, 6:57 PM UTC

tbrownaw wrote: post #7

The biggest issue I have with csv files, is when the people sending them don't get the $%^&@ quotes right . The second biggest issue, is when they say "sorry, we're not able to do that" after I tell them exactly how to fix what they're sending.

Substringed JSON arrays work great for this as well. Basically you just convert each row to an array, convert the array to JSON, and the strip the leading and trailing brackets ("[" and "]"). Newlines will be properly represented as \n as well as other funky characters.

Re: CSV as a Data Source

#16

Post by porker » Thu, Aug 01, 2013, 7:03 PM UTC

thingsilearned wrote: post #5

bdon wrote: post #2

The type/error detection would be really cool paired with something like DataWrangler: http://vis.stanford.edu/wrangler/

Yeah, DataWrangler is an awesome project. There are actually a few great companies coming out with products based around it. We may just integrate with one of those.

Ooh, which companies? I haven't come across any yet.

Re: CSV as a Data Source

#17

Post by choffstein » Thu, Aug 01, 2013, 7:29 PM UTC

Is it possible to set this up so that you can point it to a remote URL to fetch the CSV? For example, I'd love it if I could point it towards a Yahoo! Finance API call that returns a CSV file and have my dashboard auto-update. Or Dropbox URL. Or any remote URL. That would be awesome.

Re: CSV as a Data Source

#18

Post by justinator » Thu, Aug 01, 2013, 7:31 PM UTC

tbrownaw wrote: post #13

akinder wrote: post #10

Earlier quoted context omitted.

What is the proper way to encode the quotes? I'm playing around with writing a Livejournal data dump reader and will have to do the quote dance pretty soon. In LJ files they are encoded like so: col1,col2,col3,"Longer data and somethin ""with"" quotes",col5

Yes, that's how they're supposed to be. Any field with quotes, or commas, or newlines in the data needs to have quotes around it. Any quotes in the data need to be escaped by preceding them with another quote.

From what I understand, you're basically right, but you're basically right only in a sense that that's what most people do. There's no "CSV Data Format" spec. It's all just what most people agree on, most of the time. Unless someone has another idea.

Re: CSV as a Data Source

#19

Post by justinator » Thu, Aug 01, 2013, 7:32 PM UTC

jeremysmyth wrote: post #8

MySQL has supported CSV as a data source for quite some time: http://dev.mysql.com/doc/refman/5.6/en/csv-storage-engine.ht... You can run full SQL queries directly against a text file as if it was a table.

Perl's DBI also has a driver for CSV as the datasource,

https://metacpan.org/module/DBD::CSV

Re: CSV as a Data Source

#20

Post by maspwr » Thu, Aug 01, 2013, 7:36 PM UTC

choffstein wrote: post #17

Is it possible to set this up so that you can point it to a remote URL to fetch the CSV? For example, I'd love it if I could point it towards a Yahoo! Finance API call that returns a CSV file and have my dashboard auto-update. Or Dropbox URL. Or any remote URL. That would be awesome.

That exact use case is not currently possible, but it's something we've talked a bit about. We're still considering doing something like it in the future.