6 ms·
I so hate CSV. I am on the receiving end: I have to parse CSV generated by various (very expensive, very complicated) eCAD software packages. And it's often ga
by jwr 2y ago
I so hate CSV.
I am on the receiving end: I have to parse CSV generated by various (very expensive, very complicated) eCAD software packages. And it's often garbage. Those expensive software packages trip on things like escaping quotes. There is no way to recover a CSV line that has an unescaped double quote.
I can't point to a strict spec and say "you are doing this wrong", because there is no strict spec.
Then there are the TSV and semicolon-Separated V variants.
Did I mention that field quoting was optional?
And then there are banks, which take this to another level. My bank (mBank), which is known for levels of programmer incompetence never seen before (just try the mobile app) generates CSVs that are supposed to "look" like paper documents. So, the first 10 or so rows will be a "letterhead", with addresses and stuff in various random columns. Then there will be your data, but they will format currency values as prettified strings, for example "34 593,12 USD", instead of producing one column with a number and another with currency.
- byyll 2y agoI was recently writing a parser for a weird CSV. It had multiple header column rows in it as well as other header rows indicating a folder.
- mjw_byrne 2y agoI used to be a data analyst at a Big 4 management consultancy, so I've seen an awful lot of this kind of thing. One thing I never understood is the inverse correlation between "cost of product" and "ability to do serialisation properly". Free database like Postgres? Perfect every time. Big complex 6-figure e-discovery system? Apparently written by someone who has never heard of quoting, escaping or the difference between \n and \r and who thinks it's clever to use 0xFF as a delimiter, because in the Windows-1252 code page it looks like a weird rune and therefore "it won't be in the data".
- ethbr1 2y ago> Big complex 6-figure e-discovery system? Apparently written by someone who has never heard of quoting... It's because about a certain size, system projects are captured by the large consultancy shops, who eat the majority of the price in profit and management overhead... ... and then send the coding work to a lowest-cost someone who has never heard of quoting, etc. And it's a vicious cycle, because the developers in those shops that do learn and mature quickly leave for better pay and management. (Yes, there's usually a shit hot tiger team somewhere in these orgs, but they spend all their time bailing out dumpster fires or landing T10 customers. The average customer isn't getting them.)
- deepsun 2y agoJust a nitpick about consultancy shops -- I've had a chance of working in one in eastern europe and noticed that it's approach to quality was way better than client's. It also helped that client paid by hours, so consultancy company was incentivized to spend more time on refactorings, improvals and testing (with constant pushback from client). So I don't buy the consultancy company sentiment, it always boils down to engineers and incentives.
- ethbr1 2y agoHow big was the one you worked for? In my experience, smaller ones tend to align incentives better. Once they grow past a certain size though, it's a labor arbitrage game. Bill client X, staff with resources costing Y (and over-represented), profit = X-Y, minimize Y to maximize profit. PwC / IBM Global Services wasn't offering the best and brightest. (Outside of aforementioned tiger teams)
- deepsun 2y agoI agree with you in general, although my case was the other way around. My company was 10k+ people. But my client was probably the most technically advanced company at that time, with famously hard interviews for their own employees. My employer also didn't want to lose the client (it was beginning of collaboration), and since everyone wanted to work there (and move to US+California) my shop applied pretty strong filter for their own heads, even before sending them to client's vendor-interview. And client was very-very happy with the quality, and that we didn't fight for promotions and could maintain very important, but promotion-poor projects. Up to the point that client trusted to completely gave couple of projects fully to my shop. When you don't need to fight for promotions, code quality also improves.
- deleted 2y ago[deleted]
- recursive 2y ago"Enterprise software" has been defined as software that is purchased based on the decisions of people that will not use it. I think that explains a lot.
- mjw_byrne 2y agoYep, we had a constant tug of war between techies who wanted to use open-source tools that actually work (Linux, Postgres, Python, Go etc.) and bigwigs who wanted impressive-sounding things in Powerpoint decks and were trying to force "enterprise" platforms like Palantir and IBM BigInsights on us. Any time we were allowed to actually test one of the "enterprise" platforms, we'd break it in a few minutes. And I don't mean by being pathologically abusive, I mean stuff like "let's see if it can correctly handle a UTF-8 BOM...oh no, it can't".
- mharig 2y ago[dead]
- danso 2y ago> Then there will be your data, but they will format currency values as prettified strings, for example "34 593,12 USD", instead of producing one column with a number and another with currency. To be fair, that's not a problem with CSV but with the provider's lack of data literacy.
- remram 2y agoYeah, you can also use Parquet/JSON/protobuf/XLSX and store numbers as strings in this format. CSV is just a container.
- raxxorraxor 2y agoTry to live in a country where "," is the decimal point. Of course this causes numerous interoperability issues or hidden mistakes in various data sets. There would have been many better separators... but good idea to bring formatting into it as well...
- Moru 2y agoThere was a long period of my life that I thought .csv meant cemicolon separated because all I saw was cemicolon separated files and I had no idea of the pain.
- mrweasel 2y agoNot sure if they still do this, but Klarna would send us ", " separated files. If there wasn't a space after the comma then it was to be read as a decimal point. Most of the CSV parser don't/didn't allow you to specify multi-character separators. In the end I just accepted that we had one field for krona and for öre and most fields would need to have a leading space removed.
- raxxorraxor 2y agoMicrosoft did this very extensively. Many Non-English versions of Excel do save CSV-files with a semicolon as a separator and it probably was handled differently too in normal Excel files. But it goes even further, it affected their scripting languages even to this day with newer languages like their BI script (forgot the name of the language). For example, parameters of function calls aren't separated by ',' anymore and ';' is used instead. But only in the localized versions. That of course means that you have to translate these scripts depending on the locale set in your office suite, otherwise they are full of syntax errors...
- pwenzel 2y agoI hate CSV too. If I have to use it, I'll live with TSV or some other special-charter delimited format.
- NikkiA 2y agoI'd much rather it be something that is neither used in normal* text/numbers, nor whitespace, thus non-printable delimiters wins for me. * Don't mind me extending 'normal' here to include human-written numbers with thousand seperators.
- INTPenis 2y agoThe only times I hated CSV was when it came from another system I had no control over. For example Windows and their encodings, or some other proprietary BS. But CSV under controlled circumstances is very simple. And speaking of Wintendo, the bonus is often that you can go straight from CSV to Excel presentation for the middle management.
- imtringued 2y agoI agree and as a result I have completely abandoned CSV. I use the industry standard that everyone understands: ECMA-376, ISO/IEC 29500 aka .xlsx. Nobody has any problems producing or ingesting .xlsx files. The only real problem is the confusion between numbers and numeric text that happens when people use excel manually. For machine to machine communication .xlsx has never failed me.
- Hackbraten 2y agoOff the top of my head: https://learn.microsoft.com/en-us/office/troubleshoot/excel/1900-and-1904-date-system https://learn.microsoft.com/en-us/office/troubleshoot/excel/... Now you might argue that ECMA-376 accounts for this, because it has a `date1904` flag, which has to be 0 for 1900-based dates and 1 for 1904-based dates. But what does that really accomplish if you can’t be sure that vendors understand subtleties like that if they produce or consume it? Last time I checked (maybe 8 years ago), spreadsheets created on Windows and opened on Mac still shifted dates by four years, and the bug was already over twenty years old at that time. And the year-1904 issue is just the one example that I happen to know. I have absolutely zero confidence in anything that has touched, or might have touched, MS Excel with anything short of a ten-foot pole.
- Gormo 2y agoParsing Excel files in simple data interchange use cases that don't involve anyone manually using spreadsheets is an instance of unnecessary complexity. There are plenty of alternatives to CSV that remain plaintext, have much broader support, and are more rigorous than Excel in ensuring data consistency. You can use JSON, XML, ProtoBuf, among many other options.
- eternauta3k 2y agoBut everyone already has a GUI installed for editing xlsx files...
- Gormo 2y agoWhich introduces even more problems when manually editing files is out of scope.
- edoceo 2y agoThis RFC maybe? https://www.ietf.org/rfc/rfc4180.txt https://www.ietf.org/rfc/rfc4180.txt
- mcpeepants 2y agoThe RFC explicitly does not define a standard
- SoftTalker 2y agoIf only there were character codes specifically meant to separate fields and records.... we wouldn't have to worry so much about quoted commas or quoted quotes.
- mjw_byrne 2y agoThere's just no such thing as a delimiter which won't find its way into the data. Quoting and escaping really are the only robust way.
- saulpw 2y agoYou can disallow all control characters (ASCII < 32) other than CR/LF/TAB, which is reasonable. I don't know of any data besides binary blobs which uses those. I've never heard of anyone inlining a binary file (like an image) into a "CSV" anyway.
- mjw_byrne 2y agoIf you disallow control characters so that you can use them as delimiters, then CSV itself becomes a "binary" data format - or to put it another way, you lose the ability to nest CSV. It isn't good enough to say "but people don't/won't/shouldn't do that", because it will just happen regardless. I've seen nested CSV in real-life data. Compare to the zero-terminated strings used by C, one legacy of which is that PostgreSQL doesn't quite support UTF-8 properly, because it can't handle a 0 byte in a string, because 0 is "special" in C.
- hermitcrab 2y agoI've written a commercial, point and click, data wrangling tool (Easy Data Transform) that can deal with a lot of these issues: -different delimiters (comma, semi-colon, tab, pipe etc) -different encodings (UTF8, UTF16 etc) -different line ending (CR, LF, CR+LF) -ragged rows -splitting and merging columns And much more besides. However, if you have either: -line feeds and/or carriage returns in data values, but no quoting or -quoting, but quotes in data values aren't properly handled Then you are totally screwed and you have my sympathies!
- TRiG_Ireland 2y agoI worked in a web shop which had to produce spreadsheets which people wanted to look at in Excel. I gave them so many options, and told each client to experiment and choose the option which worked for them. In the end, we had (a) UTF-8 CSV, (b) UTF-8 CSV with BOM, (c) UTF-16 TSV, (d) UTF-8 HTML table with a .xlsx file extension and a lying Content-Type header which claimed it was an Excel spreadsheet. Option a worked fine so long as none of the names in the spreadsheet had any non-ASCII characters. Option d was by some measures the worst (and was definitely the largest file size), but it did seem to consistently work in Excel and Libre Office. In fact, they all worked without any issue in Libre Office.
- j45 2y agoCSV is a data-exchange format.
- thayne 2y agoBut it is terrible at that because there is no widely adhered to standard[1], the sender and receiver often disagree on the details of what exactly a CSV is. [1]: yes, I know about RFC 4180. But csvs in the wild often don't follow it.
- j45 2y agoAgreed that that CSV construction isn't consistent even with standards. That footprint seems to be dozens of variations to work with to find a library for? CSV are universal though, text kind of like markdown, and that is my intended main point.
- recursive 2y ago> I can't point to a strict spec and say "you are doing this wrong", because there is no strict spec. Have you tried RFC 4180? https://www.ietf.org/rfc/rfc4180.txt https://www.ietf.org/rfc/rfc4180.txt