5 ms·

We did a bit of work for laboratories this year and csv is not an uncommon exchange format between labs. In general almost all exchange formats are text based,

• ACow_Adonis 6y ago
  Did it mention that it was in the context of csv or an excel file? I only say that because, as someone who is painfully aware of the limitations and problems of those formats, I'm similarly aware of getting "that web-guy" on a project who proclaims "lets put things in a modern xlm format!", and lo and behold the process is now an order of magnitude slower and the xml format an order of magnitude larger than the simple delimited tabular format or stream. I'm also painfully aware of the old systems (and how old health systems are) with fixed sized buffers and processes, so I can see how this would happen in the context of a lot of computing. Edit: i see later on someone is mentioning that twitter suggests it had to do with excel file size limitations...
• morsch 6y ago
  CSV seems like a good choice for this kind of tabular, linear data.
  • mark-r 6y ago
    Have you seen the kind of mess Excel can make with a CSV file? The names of several genes were recently changed so that Excel would stop mangling them.
    • whimsicalism 6y ago
      CSV != Excel. I work CSVs regularly and can't recall the last time I opened up Excel (intentionally)
      • mark-r 6y ago
        Doesn't Excel by default capture the .csv extension so that it gets called automatically when you try to open the file? Since Excel is one of the few standard pieces of software that knows how to open CSV, it gets used a lot of times when it shouldn't. There's another post I made comparing Excel to a swiss army knife, and there's a reason for that.
        • adwww 6y ago
          I don't know many engineers - even in the data team - with an Office license.
          • mark-r 6y ago
            I think our company has a company-wide license for Office. When IT sets up a machine you get it automatically. Microsoft works hard to get those kind of setups to be common-place.
            • adwww 6y ago
              Last time I worked in a big corporate you had to fill in a long form to request a license for anything you needed. If you didn't use it again within a fortnight or so it got yoinked away... The startups I've worked at since have all been big on GSuite.
        • foobar1962 6y ago
          The problem with Excel is that it changes the data, silently.
        • jugg1es 6y ago
          Sure it does but it doesn't give a f*k - once you open it in excel, it does its' own formatting and will save that incorrect formatting even if you save the CSV as a CSV
      • mark-r 6y ago
        P.S. It doesn't matter if you never personally load a CSV into Excel. You need to ensure nobody upstream or downstream from you does either.
    • jugg1es 6y ago
      Excel removes leading zeros from numeric fields no matter what you do and you cannot turn it off. This is a huge problem in healthcare where many patient identifiers have leading zeros
      • mark-r 6y ago
        Zip codes are a problem for the same reason.
  • iagovar 6y ago
    CSV is terrible for anything containing human text though. It's a nightmare. I use SQLite as files a lot for this reason.
• tannhaeuser 6y ago
  > exchange formats are text based, with labs saying they will upgrade to „modern xml formats“ Worth noting that XML is also a text format. SGML even can treat CSVs as markup. There's nothing wrong with CSVs/TSVs anyway - it's a concise tabular format using only minimal special coding for a record and a field separator, as envisioned by ASCII and EDIFACT. The problem seems more like that there was no error checking in place to capture file write errors, or more generally the use of non-reproducible, manual operating practices which seems common in data processing.
  • ralphael 6y ago
    ..this right here. No error checking, inadequate testing, no reconciliation process. Excel is used extensively in many industries. Any file could be cut off in processing by any number of reasons, one off errors for e.g. So the solution is to "fix" the process by using the existing broken process and smaller files....
  • theptip 6y ago
    > There's nothing wrong with CSVs/TSVs I can see the theoretical purity of this statement, but based on my experience working with CSV files generated by actual non-technical users I have to disagree here. There are a number of footguns here that are really subtle and the average non-technical user has no hope of spotting them. Problems that I've seen in the wild, off the top of my head: * Windows vs. Linux line terminators breaks some CSV libraries. * Encoding can change depending on what program emitted the CSV file, and auto-detecting encoding is not perfect. For example, Excel for Mac uses Linux encoding by default, IIRC. * Excel does wacky things when you export a "CSV" in the wrong format; real users use Excel to generate their CSVs, not Python. For example if you import the string "0123456789" in an Excel sheet, it infers "number" and strips the leading "0" when you export. Now your bank account/routing numbers are invalid! * "What's a TSV?" -- if users use CSV, how do you handle commas in the data? It's nontrivial to train users to do their CSV upload as a TSV. Etc. In practice we needed to build a fairly beefy helpdesk article with accumulated wisdom on how to not break your CSV exports, and most users don't read/remember these steps until they experience the trauma first-hand. I'd say the CSV format is deceptively simple -- it's quite easy to do the right thing as a developer where the source and sink are both code you control, but in the wild it gets messy really quickly.
• secondcoming 6y ago
  XML would fix the max rows issue, but open you up to OOM issues instead!
  • andor 6y ago
    Sorry, but if you run into OOM issues by parsing an XML file, you're using the wrong API. The DOM for a large XML document will of course take tons of space in memory. The key to parsing XML files quickly and with low memory consumption is to only keep in memory what's necessary, by streaming over the elements. https://en.wikipedia.org/wiki/Simple_API_for_XML https://en.wikipedia.org/wiki/Simple_API_for_XML
    • llarsson 6y ago
      Correct. And to add to this: apparently the lost data was due to the data that exceeded the 16k rows XLS supports, so the amount of data per file was apparently not huge to begin with. So even a shitty XML parser should do just fine here.
• smsm42 6y ago
  There's nothing wrong with CSV, especially if you don't have arbitrary-text data (if you do, just don't do CSV). Excel though adds an addition layer of services, which turns it into a nightmare if used as something it explicitly wasn't made to be - a database.
• aschatten 6y ago
  There is nothing wrong with CSV or text base formats. You can even use AWS Athena to query CSV feels stored in S3. It's a good format for data import/export, that many systems can natively understand or have tools to parse, given it's known how to interpret data. It was a data pipeline issue. Software has little to do with it. If they received data in json and tried to interpret it as CSV, the same could have happened. I believe Excel even warns when you open file that has too many rows.
  • tetrahedr0n 6y ago
    I agree. Tools exist, for analysts and engineers (MS Access comes to mind for the analyst, python for the engineer), that would rectify the problem. And I think it's a fair assumption to say that those tools would be readily available. Kinda sounds like a management issue, as well. No one ever said "hey you know XLS doesn't support all of this data"? What a mess.
• iainmerrick 6y ago
  As the article notes, the CSV part of the pipeline was fine; it only broke after they imported the data to XLS. One lesson I’d draw from that is to favor simple human-readable text formats like CSV, where they’re suitable for the job at hand.