A vastly under-rated and under-appreciated art form, indeed.
CSV as a Data Source
21–30 of 53 posts
Re: CSV as a Data Source
#22This is really cool! Are there any open source libraries to do steps 2 and 3 - auto type detection and error correction/detection?
Not really. We utilize python-dateutil to help recognize the different date formats, but everything else we built ourselves.
Re: CSV as a Data Source
#23MySQL 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.
Good call. I'm a financial analyst who is becoming increasingly comfortable with code in general, but I can tell you 80-90% of business users lose interest as soon as anything SQL comes up. Heck, 70% of business users chicken out if an Access db is involved.
So this should really get a much, much bigger audience to use chartio.
Re: CSV as a Data Source
#24If 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.
Hey neat. A csv2sqlite script is one of the first things I wrote in Python: https://github.com/elidickinson/csv-tools
It's a terrible hack, but I actually still use it pretty frequently.
Re: CSV as a Data Source
#25MySQL 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.
I haven't used it in ages, but Log Parser -- a fantastic old Microsoft skunkworks project -- lets you run full SQL directly against CSV files, Apache logs, the Windows Registry, Active Directory, file paths...
http://www.microsoft.com/en-us/download/details.aspx?id=2465...
Windows only, natch.
Re: CSV as a Data Source
#26Earlier quoted context omitted.
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.
text/csv is defined in RFC 4180: https://tools.ietf.org/html/rfc4180
Re: CSV as a Data Source
#27Earlier quoted context omitted.
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.
Eg. http://www.trifacta.com/ founded by the guys who started DataWrangler :)
Re: CSV as a Data Source
#28Data isn't "locked up" in CSV, rather it is available in CSV.
CSV is one of the most open things possible.
As a programmer I do a lot of one-time makeshift data reports for other people, and I always use CSV (or precisely, tab separated) because that's what every program happily emits and consumes. If it does not, it's trivial to transform thanks to UNIX sort, awk and uniq.
Re: CSV as a Data Source
#29Data isn't "locked up" in CSV, rather it is available in CSV. CSV is one of the most open things possible. As a programmer I do a lot of one-time makeshift data reports for other people, and I always use CSV (or precisely, tab separated) because that's what every program happily emits and consumes. If it does not, it's trivial to transform thanks to UNIX sort, awk and uniq.
If I were to build a MVP for any type of data charting or visualization or analysis the first data format that I would think of supporting would be CSV.
Re: CSV as a Data Source
#30Data isn't "locked up" in CSV, rather it is available in CSV. CSV is one of the most open things possible. As a programmer I do a lot of one-time makeshift data reports for other people, and I always use CSV (or precisely, tab separated) because that's what every program happily emits and consumes. If it does not, it's trivial to transform thanks to UNIX sort, awk and uniq.
Once your CSV (or TSV) files start having quoted fields, they become very tricky to parse using standard multi-purpose tools like sort, awk, & uniq.
It's hard enough when you have delimiters in quoted fields, but dealing with quoted newlines starts to become unreasonable, especially for line-based tools.
CSV files, as you say, are absolutely wonderful to create. Problems come up when you try to parse files other people write. Not everyone follows RFC 4180.