3 ms·

> I'd argue CSV's biggest strength is that it is easy to parse correctly Not sure I resonate with that. I work with large CSVs of varying provenance every day

• Someone1234 9y ago
  > Excel's CSV parsing isn't the greatest, but does a surprisingly decent job considering how ill-defined CSV is. No, it really doesn't. Save this as a CSV, open it in Excel, hit save, and then review the raw CSV: "1000000000012345", "Hello", "2000000000067890", "World", Here's what Excel (O365) does to it for me: 1E+15, Hello, 2E+15, World, That data is now permanently lost. And this corruption occurs for almost all EAN/UPCs. There are many ways to get around this in Excel, but the default experience is data corruption and has been for most of my lifetime. It is a terrible application for CSV. If they followed the standard every column would be what it is: Text. > XML isn't really on anyone's radar in the big data world. But is a central theme in the business to business back-end systems world. I'm talking about ordering, invoicing, remittances, hospital records, hospital billing, and so on. No clue what "big data" is, we deal with billions of transactions a year, but that's likely not what you're referring to.
  • wenc 9y ago
    1. I think in the first instance, Excel performs type-inference and coerces it into a numeric type and writes a truncated precision version of numeric fields in CSV, which is an abominable sin. There are workarounds for this [1], but agree this is terrible behavior due to CSV's lack of types. Arguably, there should have been an explicit Excel switch which forces all CSV data to be parsed as raw strings. I've seen problems with Excel CSVs that are worse than that: I have serial numbers that have leading 0's that have semantic meaning, like 000002324122323. Most CSV parsers cannot tell that this isn't a numeric type and so handle it wrongly. So I resort to [1]. 2. Big data is of course somewhat ambiguous nomenclature as well, but is nowadays typically understood to mean the Hadoop ecosystem or similar. Data is typically ingested into a distributed file system (HDFS, S3, etc.) in formats such as Parquet, Avro, JSON and often CSV. A schema-on-read database like Hive sits on top of this layer and presents a SQL interface to the user. Tools like Apache Spark provide programmatic transformations that operate on the data on the large. CSV is often promoted as a format for storing structured data due to ease of ingestion and inspection (all you need is a text editor for troubleshooting). However, you pay a performance penalty every time an analytic query is run because CSV doesn't support indexes, predicate pushdowns, compression, etc. and records can sometimes be uninterpretable under certain schemas (in which case they are simply excluded or dropped). Sometimes having one too many commas in a row can completely mess things up (all the columns get shifted in a record) -- I found this out the hard way when Spark exported malformed CSVs with un-escaped commas. [1] http://support.pitneybowes.com/SearchArticles/VFP06_KnowledgeWithSidebarTroubleshoot?id=kA280000000TTCfCAO&popup=false&lang=en_US http://support.pitneybowes.com/SearchArticles/VFP06_Knowledg...
• laumars 9y ago
  sqlite wouldn't solve any of those issues because it's dynamically typed and what you're asking for is something which is strictly typed. In that regard you're better off with a data format like YAML. I would also disagree that Excel does a good job with CSV. Well, maybe opening files but certainly not saving them as it rewrites values in weird ways. Back when I used to have to handle CSVs regularly (which admittedly was a while ago now) I found OpenOffice Calc to be far superior in terms of CSV support. I'd assume the same is true for LibreOffice Calc but honestly I've not done a huge amount with CSV since.