3 ms·

Sure, I get your arguments and we're probably mostly in agreement, but in practice I see very few problems arising with using CSV. I mean, right now, the data

Sure, I get your arguments and we're probably mostly in agreement, but in practice I see very few problems arising with using CSV. I mean, right now, the data interchange format between multiple working systems is CSV; think payment systems, inter-bank data interchange, ERP systems, CRM systems, billing systems ... the list goes on. I just recently had a coffee with a buddy who's a salesman for some enterprise system: of the most common enterprise systems we recently worked with (SAP type things, but on smaller scales), every single one of them had CSV as the standard way to get data between themselves and other systems. And yet, they work. The number of people uploading excel files to be processed or downloading excel files for local visualistation and processing would floor you. It's done multiple times a day, on multiple systems, in multiple companies. And yet, they work. I get your argument though - a JSON array of arrays can represent everything that CSV can, and is preferable to CSV, and is what I would choose when given the choice, but the issues with using that are not going to be fewer than issues with CSV using RFC1480.

flanked-evergl 2y ago
In practice, I have never ever received CSV to process that complied with RFC 4180, and in most cases it was completely incoherent and needed incredibly special handling to handle all the various problems like lack of escaping. SAP has been by far the worst. I never managed to get data out of it that were not completely garbage and needed hand crafted parsers.
- consp 2y ago
  SAP only has to be SAP and MS Excel compatible. The rest is not needed so in their eyes it is probably to spec.
  - flanked-evergl 2y ago
    European quality™.
fauigerzigerk 2y ago
>but in practice I see very few problems arising with using CSV That is not my experience at all. I've been processing CSV files from financial institutions for many years. The likelihood of brokenness must be around 40%. It's unbelievable. The main reason for this is not necessarily the CSV format as such. I believe the reason is that it is often the least experienced developers who are tasked with writing export code. And many inexperienced developers seem to think that they can generate CSV without using a library because the format is supposedly so simple. JSON is better but it doesn't help with things like getting dates right. XML can help with that but it has complexities that people get wrong all the time (such as entities), so I think JSON is the best compromise.
- kragen 2y ago
  > And many inexperienced developers seem to think that they can generate CSV without using a library because the format is supposedly so simple. Can't they? def excel_csv_of(rows): for row in rows: for i, field in enumerate(row): if i: yield ',' yield '"' for c in field: yield '""' if c == '"' else c yield '"' yield '\n' I haven't tested this, even to see if the code parses. What did I screw up?
  - gthompson512 2y ago
    This forces each field to be quoted, and it assumes that each row has the same fields in the same order. A library can handle the quoting issues and fields more reliably. Not sure why you went with a generator for this either. Most people expect something like `12,,213,3` instead of `"12","213","3"` which yours might give. https://en.wikipedia.org/wiki/Comma-separated_values#Basic_rules https://en.wikipedia.org/wiki/Comma-separated_values#Basic_r...
    - kragen 2y ago
      Forcing each field to be quoted is always correct, isn't it? How could something be "more reliable" than something that is always correct? With respect to "the same fields in the same order", no, although you may or may not feed the CSV to an application that has such an expectation. But if you apply it to data like [("Points",),(),("x","y"),("3","4"),("6","8","10")] it will successfully preserve that wonky structure in a file Excel can ingest reliably. (As reliably as Excel can ingest anything, anyway, since Excel has its own Norway problem.) It's true that it's possible to produce more optimized output, but I didn't claim that the output was optimal, just correct. Using generators is necessary to be able to correctly output individual fields that are many times larger than physical memory.
IanCal 2y ago
I'll preface this that I think we are mostly in agreement, so that's the friendly tone of reply, part of this is just having flashbacks. It's massively used, but the lack of adherence to a proper spec causes huge issues. If you have two systems that happen to talk properly to each other, great, but if you are as I was an entrypoint for all kinds of user generated files it's a nightmare. CSV is the standard, sure, but it's easy to write code that produces it that looks right at first glance but breaks with some edge case. Or someone has just chosen a different separator, or quote, so you need to try and detect those before parsing (I had a list that I'd go through, then look for the most commonly appearing non-letter character). The big problem is that the resulting semantically broken csv files often look pretty OK to someone scanning them and permissive parsers. So one system reads it in, splits something on lines and assumes missing columns are blank and suddenly you have the wrong number of rows, then it exports it. Worse if it's been sorted before the export. Of course then there's also the issues around a lack of types, so numbers and strings are not distinguishable automatically leading to broken issues where you do want leading zeros. Again often not identified until later. Or auto type detection in a system breaking because it sees a lot of number-like things and assumes it's a number column. Without types there's no verification either. So even properly formatted CSV files need a second place for metadata about what types there are in the file. JSON has some of these problems too, it lacks dates, but far fewer. > but the issues with using that are not going to be fewer than issues with CSV using RFC1480. My only disagreement here is that I've had to deal with many ingest endpoints that don't properly support that. Fundamentally I think nobody uses CSV files because they're a good format. They've big, slow to parse, lack proper typing, lack columnar reading, lack fast jumping to a particular place, etc. They are ubiquitous, just not good, and they're very easy to screw up in hard to identify or fix ways. Finally, lots of this comes up because RFC4180 is only from *2005*. Oh, and if I'm reading the spec correctly, RFC4180 doesn't support UTF8. There was a proposed update maybe in 2022 but I can't see it being accepted as an RFC.
tikhonj 2y ago
> And yet, they work. Through a lot of often-painful manual intervention. I've seen it first-hand. If an organization really needs something to work, it's going to work somehow—or the organization wouldn't be around any more—but that is a low bar. In a past role, I switched some internal systems from using CSV/TSV to using Parquet and the difference was amazing both in performance and stability. But hey, the CSV version worked too! It just wasted a ton of people's time and attention. The Parquet version was far better operationally, even given the fact that you had to use parquet-tools instead of just opening files in a text editor.
watwut 2y ago
> I mean, right now, the data interchange format between multiple working systems is CSV; think payment systems, inter-bank data interchange, ERP systems, CRM systems, billing systems ... the list goes on. And there are constant issues arising from that. You basically need a small team to deal with them in every institution that is processing them. > I just recently had a coffee with a buddy who's a salesman for some enterprise system: of the most common enterprise systems we recently worked with (SAP type things, but on smaller scales), every single one of them had CSV as the standard way to get data between themselves and other systems. Salesman of enterprise system do not care about issues programmers and clients have. They care about what they can sell to other businessmen. That teams on both sides then waste time and money on troubleshooting is no concern to the salesman. And I am saying that as someone who worked on the enterprise system that consumed a lot of csv. It does not work and process of handling them literally sometimes involved phone calls to admins of other systems. More often then would be sane. > The number of people uploading excel files to be processed or downloading excel files for local visualistation and processing would floor you. That is perfectly fine as long as it is a manager downloading data so that he can manually analyze them. It is pretty horrible when those files are then uploaded to other systems.

flanked-evergl 2y ago

In practice, I have never ever received CSV to process that complied with RFC 4180, and in most cases it was completely incoherent and needed incredibly special handling to handle all the various problems like lack of escaping. SAP has been by far the worst. I never managed to get data out of it that were not completely garbage and needed hand crafted parsers.

consp 2y ago
SAP only has to be SAP and MS Excel compatible. The rest is not needed so in their eyes it is probably to spec.
- flanked-evergl 2y ago
  European quality™.

fauigerzigerk 2y ago

>but in practice I see very few problems arising with using CSV That is not my experience at all. I've been processing CSV files from financial institutions for many years. The likelihood of brokenness must be around 40%. It's unbelievable. The main reason for this is not necessarily the CSV format as such. I believe the reason is that it is often the least experienced developers who are tasked with writing export code. And many inexperienced developers seem to think that they can generate CSV without using a library because the format is supposedly so simple. JSON is better but it doesn't help with things like getting dates right. XML can help with that but it has complexities that people get wrong all the time (such as entities), so I think JSON is the best compromise.

kragen 2y ago
> And many inexperienced developers seem to think that they can generate CSV without using a library because the format is supposedly so simple. Can't they? def excel_csv_of(rows): for row in rows: for i, field in enumerate(row): if i: yield ',' yield '"' for c in field: yield '""' if c == '"' else c yield '"' yield '\n' I haven't tested this, even to see if the code parses. What did I screw up?
- gthompson512 2y ago
  This forces each field to be quoted, and it assumes that each row has the same fields in the same order. A library can handle the quoting issues and fields more reliably. Not sure why you went with a generator for this either. Most people expect something like `12,,213,3` instead of `"12","213","3"` which yours might give. https://en.wikipedia.org/wiki/Comma-separated_values#Basic_rules https://en.wikipedia.org/wiki/Comma-separated_values#Basic_r...
  - kragen 2y ago
    Forcing each field to be quoted is always correct, isn't it? How could something be "more reliable" than something that is always correct? With respect to "the same fields in the same order", no, although you may or may not feed the CSV to an application that has such an expectation. But if you apply it to data like [("Points",),(),("x","y"),("3","4"),("6","8","10")] it will successfully preserve that wonky structure in a file Excel can ingest reliably. (As reliably as Excel can ingest anything, anyway, since Excel has its own Norway problem.) It's true that it's possible to produce more optimized output, but I didn't claim that the output was optimal, just correct. Using generators is necessary to be able to correctly output individual fields that are many times larger than physical memory.

IanCal 2y ago

I'll preface this that I think we are mostly in agreement, so that's the friendly tone of reply, part of this is just having flashbacks. It's massively used, but the lack of adherence to a proper spec causes huge issues. If you have two systems that happen to talk properly to each other, great, but if you are as I was an entrypoint for all kinds of user generated files it's a nightmare. CSV is the standard, sure, but it's easy to write code that produces it that looks right at first glance but breaks with some edge case. Or someone has just chosen a different separator, or quote, so you need to try and detect those before parsing (I had a list that I'd go through, then look for the most commonly appearing non-letter character). The big problem is that the resulting semantically broken csv files often look pretty OK to someone scanning them and permissive parsers. So one system reads it in, splits something on lines and assumes missing columns are blank and suddenly you have the wrong number of rows, then it exports it. Worse if it's been sorted before the export. Of course then there's also the issues around a lack of types, so numbers and strings are not distinguishable automatically leading to broken issues where you do want leading zeros. Again often not identified until later. Or auto type detection in a system breaking because it sees a lot of number-like things and assumes it's a number column. Without types there's no verification either. So even properly formatted CSV files need a second place for metadata about what types there are in the file. JSON has some of these problems too, it lacks dates, but far fewer. > but the issues with using that are not going to be fewer than issues with CSV using RFC1480. My only disagreement here is that I've had to deal with many ingest endpoints that don't properly support that. Fundamentally I think nobody uses CSV files because they're a good format. They've big, slow to parse, lack proper typing, lack columnar reading, lack fast jumping to a particular place, etc. They are ubiquitous, just not good, and they're very easy to screw up in hard to identify or fix ways. Finally, lots of this comes up because RFC4180 is only from *2005*. Oh, and if I'm reading the spec correctly, RFC4180 doesn't support UTF8. There was a proposed update maybe in 2022 but I can't see it being accepted as an RFC.

tikhonj 2y ago

> And yet, they work. Through a lot of often-painful manual intervention. I've seen it first-hand. If an organization really needs something to work, it's going to work somehow—or the organization wouldn't be around any more—but that is a low bar. In a past role, I switched some internal systems from using CSV/TSV to using Parquet and the difference was amazing both in performance and stability. But hey, the CSV version worked too! It just wasted a ton of people's time and attention. The Parquet version was far better operationally, even given the fact that you had to use parquet-tools instead of just opening files in a text editor.

watwut 2y ago

> I mean, right now, the data interchange format between multiple working systems is CSV; think payment systems, inter-bank data interchange, ERP systems, CRM systems, billing systems ... the list goes on. And there are constant issues arising from that. You basically need a small team to deal with them in every institution that is processing them. > I just recently had a coffee with a buddy who's a salesman for some enterprise system: of the most common enterprise systems we recently worked with (SAP type things, but on smaller scales), every single one of them had CSV as the standard way to get data between themselves and other systems. Salesman of enterprise system do not care about issues programmers and clients have. They care about what they can sell to other businessmen. That teams on both sides then waste time and money on troubleshooting is no concern to the salesman. And I am saying that as someone who worked on the enterprise system that consumed a lot of csv. It does not work and process of handling them literally sometimes involved phone calls to admins of other systems. More often then would be sane. > The number of people uploading excel files to be processed or downloading excel files for local visualistation and processing would floor you. That is perfectly fine as long as it is a manager downloading data so that he can manually analyze them. It is pretty horrible when those files are then uploaded to other systems.