6 ms·
CSV as a Data Source
- bdon 13y agoThe type/error detection would be really cool paired with something like DataWrangler: http://vis.stanford.edu/wrangler/ http://vis.stanford.edu/wrangler/
- thingsilearned 13y agoYeah, 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.
- porker 13y agoOoh, which companies? I haven't come across any yet.
- dude_abides 13y agoEg. http://www.trifacta.com/ http://www.trifacta.com/ founded by the guys who started DataWrangler :)
- porker 13y agoThanks!
- michaelrbock 13y agoAs someone who has worked in an industry where FTP'ing massive CSV files was the norm, I must say this looks really nice.
- thingsilearned 13y agoThanks!
- netvarun 13y agoThis is really cool! Are there any open source libraries to do steps 2 and 3 - auto type detection and error correction/detection?
- thingsilearned 13y agoNot really. We utilize python-dateutil to help recognize the different date formats, but everything else we built ourselves. http://labix.org/python-dateutil http://labix.org/python-dateutil
- tbrownaw 13y agoThe 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.
- akinder 13y agoWhat 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
- tbrownaw 13y agoYes, 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.
- justinator 13y agoFrom 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.
- steveklabnik 13y agotext/csv is defined in RFC 4180: https://tools.ietf.org/html/rfc4180 https://tools.ietf.org/html/rfc4180
- nemetroid 13y agoStrictly, the definition in RFC 4180 mandates ASCII which makes it unusable for many purposes. I guess there's nothing practical stopping you from using another encoding though.
- jeremysmyth 13y agoMySQL has supported CSV as a data source for quite some time: http://dev.mysql.com/doc/refman/5.6/en/csv-storage-engine.html 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.
- thingsilearned 13y agoYeah, 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 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.
- tryitnow 13y agoGood 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.
- justinator 13y agoPerl's DBI also has a driver for CSV as the datasource, https://metacpan.org/module/DBD::CSV https://metacpan.org/module/DBD::CSV
- eli 13y agoI 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=24659 http://www.microsoft.com/en-us/download/details.aspx?id=2465... Windows only, natch.
- Ecio78 13y agoMicrosoft SQL Server has a couple of methods for querying CSV/XLS(X) files (I remember at least OPENROWSET and BULK INSERT), but sometimes it's a bit tricky to use/configure (have the right OLEDB driver, use INI files for configuration etc..) or the behaviour is not very consistent/well-documented Edit: of course with BULK INSERT you don't directly query the file, you must load it to a (temp) table
- caissy 13y agoThat is really nice. I remember implementing something similar to step 2 and 3 on AppEngine a few years back for a project. It turned out quite nice and non tech-savvy users used it without much problems. We managed to have something that could easily work with different CSV formats (MS Excel and others).
- dergachev 13y agoIf you use SQL, check out http://github.com/dergachev/csv2sqlite 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.
- eli 13y agoHey neat. A csv2sqlite script is one of the first things I wrote in Python: https://github.com/elidickinson/csv-tools https://github.com/elidickinson/csv-tools It's a terrible hack, but I actually still use it pretty frequently.
- choffstein 13y agoIs 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.
- maspwr 13y agoThat 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.
- hedonist 13y agoA vastly under-rated and under-appreciated art form, indeed.
- guard-of-terra 13y agoData 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.
- thejteam 13y agoIf 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.
- sehrope 13y agoYou'd think so but when the use case involves dynamically retrieving fresh data it becomes one of the last things to work on. Lots of people have CSV data and it makes sense to support it but since it's generally a manual process it doesn't deliver a good "flow" for the customer. If they have to repeatedly export/import their data manually to have the chart data update it'll seem like a chore. Compare this to connecting to a customer's database directly. You can refresh your (cached) chart data on your own schedule and the customer doesn't need to be involved at all. Oh and if anyone is thinking, "Yeah but I can schedule a cron job to extract the CSV file and publish it every X minutes", yes you could do that but again that's work for the customer. If the pitch is "sign up, plug in to your database, and boom charts!" there really should be a "schedule automated cron job" component. All that aside, it is useful to be able to manually add data like this. Particularly for static data where the manual work is infrequent.
- zbanks 13y agoOnce 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.
- AdrianRossouw 13y agoI'm rather fond of using [1] csvkit for doing my csv wrangling. That and google refine (now open refine). [1] http://csvkit.readthedocs.org/ http://csvkit.readthedocs.org/
- JulianMorrison 13y agoCSV is the biggest pile of nuisance you'd never expect from a seemingly simple data format. - Header line or none? - "\n" or "\r\n"? - Is there a newline at the last line? How about two? - Escape quotes with doubling or backslash? How about both in the same file? How about both, inconsistently, in different fields? How about quotes including a newline and commas? - Strings always quoted? Only if necessary? Is ,, a null or an empty string, or an error? - How about mixed line lengths? Are missing trailing entries nulls? How about multiple data types in a file, with the first field being type, and line length only fixed per type? I have generally found "TSV with a rule that data cannot represent tabs or newlines, period" as vastly superior.
- jwilliams 13y agoTSV has all those same issues. However, you just have less frequent need of a tab, so most of the escaping edge cases never come up. Those aspects are defined in RFC 4180 - just a lot of systems don't bother. How would you define a simpler data format?
- JulianMorrison 13y agoIt's a lot easier to administratively ban tabs/newlines in your data than it is to ban commas, and TSV doesn't have escape mechanisms or quoting. So you actually can parse it with line.chomp().split("\t"), and that doesn't break horrendously. TSV is streamable and minimally wasteful, I rather approve of it. Netstrings are better though if having sized data and nested data is needed. They are proof against all the ills of quoting and escapes.
- jwilliams 13y agoLargely agree. I was just picking up on "vastly superior". All the issues of line breaks, nulls, column lengths, headers still remain. You're adding the constraint that you can't use tabs or newlines in your data (to use a newline in your String, you'd need to escape it). In all other cases, you need escaping, and once you've assumed escaping then CSV and TSV aren't really any different.
- funstr 13y agoCSV format does not define encoding. Also customers usually think about CSV as export/import format from/to Microsoft Excel. Unfortunately each Office localization uses different output encoding and delimiters (and date format). And you cannot suppose that the encoding is UTF-8. The most complete importer of CSV file which I have seen is in Open Office. It is also worth mentioning that Excel can import CSV with other than system locale and different delimiters - but it just not work through open file command.
- zbanks 13y agoThis also detects the file encoding & delimiters.
- funstr 13y agoAutomatic file encoding detection is only heuristic and cannot be in principle exact. But I do not want to criticize the import functionality. I just wanted to say that CSV is an obsolete format and should not be used for data interchange.