6 ms·

Have you had to work with csv files from the wild much? I'm not being snarky but what you're talking about is night and day to what I've experienced over the ye

by IanCal 2y ago

Have you had to work with csv files from the wild much? I'm not being snarky but what you're talking about is night and day to what I've experienced over the years. There aren't vast numbers of different JSON formats. There's practically one and realistically maybe two. Headers are in each line, utf8 has never been an issue for me and quoting and escaping are well defined and obeyed. This is because for datasets, almost exclusively, the file is machine written and rarely messed with. Csv files have all kinds of separators, quote characters, some parsers don't accept multi lines and some do, people sort files which mostly works until there's a multi line. All kinds of line endings, encodings and mixed encodings where people have combined files. I tried using ASCII record separators after dealing with so many issues with commas, semicolons, pipes, tabs etc and still data in the wild had these jammed into random fields. Lots of these things don't break when you hit the issue either, the parsers happily churn on with garbage data, leading to further broken datasets. Also they're broken for clients if the first character is a capital I.

lelanthran 2y ago
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?
gpvos 2y ago
What's the problem with capital I?
- n_plus_1_acc 2y ago
  Probably referring to the "turkish i problem"
  - gpvos 2y ago
    Not an unreasonable guess, but it turned out to be something different.
- IanCal 2y ago
  The byte for a capital I is the same as the start for an odd file format, slyk maybe? Excel has (or did if they finally fixed it) for years decided this was enough to assume the file (called .csv) cannot possibly be csv but must actually be slyk. It then parses it as such, and is shocked to find your slyk file is totally broken!
  - boogheta 2y ago
    It sounds to me like as often the problem here is Excel, not CSV
    - IanCal 2y ago
      Clients don't particularly make the distinction, and in a way nor should they - they can't open your file.
    - pasc1878 2y ago
      Yes but in practice CSV is defined by what Excel does. As there is no standard to which Excel conforms as it predates standards and there would be an outcry if Excel started rejecting files that had worked for years.
      - 2y ago
dspillett 2y ago
WRT JSON: > Headers are in each line This might be my old “space and network cost savings” reflex, which is a lot less necessary these days, kicking in, but the feels inefficient. It also gives rise to not knowing the whole schema until you read the whole dataset (which might be multiple files), unless some form of external schema definition is provided. Having said that, I accept that JSON has advantages over CSV, even if all that is done is translating a data-table into an array of objects representing one row each. > utf8 has never been an issue for me The main problem with UTF8 isn't with CSV generally, it is usually, much like the “first column is called ID” issue, due to Excel. Unfortunately a lot of people interact with CSVs primarily with Excel, so it gets tarred with that brush by association. Unless Excel sees the BOM sequence at the start of a CSV file, which the Unicode standards recommend against for UTF8, it assumes its characters are using the Win1252 encoding (almost, but not quite, ISO-8859-1). > Csv files have all kinds of separators I've taken to calling them Character Separated Value files, rather than Comma, for this reason.
- IanCal 2y ago
  Yes, it's not great. Space is annoying, though compression pretty much removes that as a concern (zstd is good for this, you can even have a custom dictionary). And yes, missing keys is annoying. JSONL is handy, JSON that's in the form {data: [...hundred megs of lines]} is annoying for various parsers. I'm quite a fan of parquet, but never expect to receive that from a client (alas).
  - cogman10 2y ago
    Parquet should get the praise. It's simply awesome. It's what I'd pick for tabular data exchange. A recent problem I solved with it and duckdb allowed me to query and share a 3M record dataset. The size? 50M. And my queries all ran subsecond. You just aren't going to get that sort of compression and query-ability with a csv.
  - Izkata 2y ago
    > JSON that's in the form {data: [...hundred megs of lines]} is annoying for various parsers. One reason this became common was a simple protection against json hijacking: https://haacked.com/archive/2009/06/25/json-hijacking.aspx/ https://haacked.com/archive/2009/06/25/json-hijacking.aspx/
SkyBelow 2y ago
I wonder if CSV is the trivial format, so you have many people picking it because they want the easiest, and still getting it wrong. JSON is harder, so very few people are going to roll their own serializer/deserializer, and those who do are more likely to focus on getting it right (or at least catching the really obvious bugs). I've dealt with incorrect CSVs numerous times, never with incorrect JSON, but, of the times I know what was happening on the other system, each time the CSV was from some in house (or similar) implementation of dumping a SQL output (or similar) into a text file as an MVP. JSON was always using some library. If so, that's all the more reason to love CSV as it stands guard for JSON. If CSV didn't exist, we would instead have broken JSON implementations. (JSON and XML would likely then share a similar relationship.)
- Gormo 2y ago
  Sometimes people interpret the term too generically and actually implement a high degree of non-trivial, very idiosyncratic complexity, while still calling it "CSV". One project I worked on involved a vendor promising to send us data dumps in "CSV format". When we finally received their "CSV" we had to figure out how to deal with (a) global fields being defined in special rows above the header row, and (b) a two-level hierarchy of semicolon-delimited values nested within comma-delimited columns. We had to write a custom parser to complete the import.
  - ttyprintk 2y ago
    Hi, Yes, we chose ARFF format, which is idiosyncratic yet well-defined back in the old data mining days.
recursive 2y ago
> There aren't vast numbers of different JSON formats. Independent variations I have seen: * Trailing commas allowed or not * Comments allowed or not * Multiple kinds of date serialization conventions * Divergent conventions about distinguishing floating point types from integers * Duplicated key names tolerated or not * Different string escaping policies, such as, but not limited to "\n" vs "\x0a" There are bazillions of JSON variations.
- thayne 2y ago
  > Trailing commas allowed or not The json spec does not allow commas. Although there are jsom supersets that do. > Comments allowed or not The json spec does not allow comments. Although there are jsom supersets that do. > Multiple kinds of date serialization conventions Json spec doesn't say anything about dates. That is dependent on your application schema. > Divergent conventions about distinguishing floating point types from integers This is largely due to divergent ways different programming languages handle numbers. I won't say jsom handles this the best, but any file format used across multiple languages will run into problems with differences in how numbers are represented. At least there is a well defined difference between a number and a string, unlike csv. > Duplicated key names tolerated or not According to the spec, they are tolerated, although the semantics of such keys is implementation defined. > Different string escaping policies, such as, but not limited to "\n" vs "\x0a" Both of those are interpreted as the same thing, at least according to the spec. That is an implementation detail of the serializer, not a different language.
  - recursive 2y ago
    And CSV parsers and serializers compliant with RFC 4180 are similarly reliable.
    - thayne 2y ago
      But many, perhaps most, parsers and serializers for CSV are not compliant with RFC 4180. RFC 4180 is not an official standard. The text of the RFC itself states: > This memo provides information for the Internet community. It does > not specify an Internet standard of any kind. CSVs existed long before that RFC was written, and it is more a description of CSVs that are somewhat portable, not a definitive specification.

by IanCal 2y ago

lelanthran 2y ago

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?

gpvos 2y ago

What's the problem with capital I?

n_plus_1_acc 2y ago
Probably referring to the "turkish i problem"
- gpvos 2y ago
  Not an unreasonable guess, but it turned out to be something different.
IanCal 2y ago
The byte for a capital I is the same as the start for an odd file format, slyk maybe? Excel has (or did if they finally fixed it) for years decided this was enough to assume the file (called .csv) cannot possibly be csv but must actually be slyk. It then parses it as such, and is shocked to find your slyk file is totally broken!
- boogheta 2y ago
  It sounds to me like as often the problem here is Excel, not CSV
  - IanCal 2y ago
    Clients don't particularly make the distinction, and in a way nor should they - they can't open your file.
  - pasc1878 2y ago
    Yes but in practice CSV is defined by what Excel does. As there is no standard to which Excel conforms as it predates standards and there would be an outcry if Excel started rejecting files that had worked for years.
    - 2y ago

dspillett 2y ago

WRT JSON: > Headers are in each line This might be my old “space and network cost savings” reflex, which is a lot less necessary these days, kicking in, but the feels inefficient. It also gives rise to not knowing the whole schema until you read the whole dataset (which might be multiple files), unless some form of external schema definition is provided. Having said that, I accept that JSON has advantages over CSV, even if all that is done is translating a data-table into an array of objects representing one row each. > utf8 has never been an issue for me The main problem with UTF8 isn't with CSV generally, it is usually, much like the “first column is called ID” issue, due to Excel. Unfortunately a lot of people interact with CSVs primarily with Excel, so it gets tarred with that brush by association. Unless Excel sees the BOM sequence at the start of a CSV file, which the Unicode standards recommend against for UTF8, it assumes its characters are using the Win1252 encoding (almost, but not quite, ISO-8859-1). > Csv files have all kinds of separators I've taken to calling them Character Separated Value files, rather than Comma, for this reason.

IanCal 2y ago
Yes, it's not great. Space is annoying, though compression pretty much removes that as a concern (zstd is good for this, you can even have a custom dictionary). And yes, missing keys is annoying. JSONL is handy, JSON that's in the form {data: [...hundred megs of lines]} is annoying for various parsers. I'm quite a fan of parquet, but never expect to receive that from a client (alas).
- cogman10 2y ago
  Parquet should get the praise. It's simply awesome. It's what I'd pick for tabular data exchange. A recent problem I solved with it and duckdb allowed me to query and share a 3M record dataset. The size? 50M. And my queries all ran subsecond. You just aren't going to get that sort of compression and query-ability with a csv.
- Izkata 2y ago
  > JSON that's in the form {data: [...hundred megs of lines]} is annoying for various parsers. One reason this became common was a simple protection against json hijacking: https://haacked.com/archive/2009/06/25/json-hijacking.aspx/ https://haacked.com/archive/2009/06/25/json-hijacking.aspx/

SkyBelow 2y ago

I wonder if CSV is the trivial format, so you have many people picking it because they want the easiest, and still getting it wrong. JSON is harder, so very few people are going to roll their own serializer/deserializer, and those who do are more likely to focus on getting it right (or at least catching the really obvious bugs). I've dealt with incorrect CSVs numerous times, never with incorrect JSON, but, of the times I know what was happening on the other system, each time the CSV was from some in house (or similar) implementation of dumping a SQL output (or similar) into a text file as an MVP. JSON was always using some library. If so, that's all the more reason to love CSV as it stands guard for JSON. If CSV didn't exist, we would instead have broken JSON implementations. (JSON and XML would likely then share a similar relationship.)

Gormo 2y ago
Sometimes people interpret the term too generically and actually implement a high degree of non-trivial, very idiosyncratic complexity, while still calling it "CSV". One project I worked on involved a vendor promising to send us data dumps in "CSV format". When we finally received their "CSV" we had to figure out how to deal with (a) global fields being defined in special rows above the header row, and (b) a two-level hierarchy of semicolon-delimited values nested within comma-delimited columns. We had to write a custom parser to complete the import.
- ttyprintk 2y ago
  Hi, Yes, we chose ARFF format, which is idiosyncratic yet well-defined back in the old data mining days.

recursive 2y ago

> There aren't vast numbers of different JSON formats. Independent variations I have seen: * Trailing commas allowed or not * Comments allowed or not * Multiple kinds of date serialization conventions * Divergent conventions about distinguishing floating point types from integers * Duplicated key names tolerated or not * Different string escaping policies, such as, but not limited to "\n" vs "\x0a" There are bazillions of JSON variations.

thayne 2y ago
> Trailing commas allowed or not The json spec does not allow commas. Although there are jsom supersets that do. > Comments allowed or not The json spec does not allow comments. Although there are jsom supersets that do. > Multiple kinds of date serialization conventions Json spec doesn't say anything about dates. That is dependent on your application schema. > Divergent conventions about distinguishing floating point types from integers This is largely due to divergent ways different programming languages handle numbers. I won't say jsom handles this the best, but any file format used across multiple languages will run into problems with differences in how numbers are represented. At least there is a well defined difference between a number and a string, unlike csv. > Duplicated key names tolerated or not According to the spec, they are tolerated, although the semantics of such keys is implementation defined. > Different string escaping policies, such as, but not limited to "\n" vs "\x0a" Both of those are interpreted as the same thing, at least according to the spec. That is an implementation detail of the serializer, not a different language.
- recursive 2y ago
  And CSV parsers and serializers compliant with RFC 4180 are similarly reliable.
  - thayne 2y ago
    But many, perhaps most, parsers and serializers for CSV are not compliant with RFC 4180. RFC 4180 is not an official standard. The text of the RFC itself states: > This memo provides information for the Internet community. It does > not specify an Internet standard of any kind. CSVs existed long before that RFC was written, and it is more a description of CSVs that are somewhat portable, not a definitive specification.