Hacker News Reader

Comment by jll29 | Hacker News Reader

The post should at least mention in passing the major problem with CSV: it is a "no spec" family of de-facto formats, not a single thing (it is an example of "historically grown"). And omission of that meams I'm going to have to call this our for its bias (but then it is a love letter, and love makes blind...).

Unlike XML or JSON, there isn't a document defining the grammar of well-formed or valid CSV files, and there are many flavours that are incompatible with each other in the sense that a reader for one flavour would not be suitable for reading the other and vice versa. Quoting, escaping, UTF-8 support are particular problem areas, but also that you cannot tell programmatically whether line 1 contains column header names or already data (you will have to make an educated guess but there ambiguities in it that cannot be resolved by machine).

Having worked extensively with SGML for linguistic corpora, with XML for Web development and recently with JSON I would say programmatically, JSON is the most convenient to use regarding client code, but also its lack of types makes it useful less broadly than SGML, which is rightly used by e.g. airlines for technical documntation and digital humanities researchers to encode/annotate historic documents, for which it is very suitable, but programmatically puts more burden on developers. You can't have it all...

XML is simpler than SGML, has perhaps the broadest scope and good software support stack (mostly FOSS), but it has been abused a lot (nod to Java coders: Eclipse, Apache UIMA), but I guess a format is not responsible for how people use or abuse it. As usual, the best developers know the pros and cons and make good-taste judgments what to use each time, but some people go ideological.

(Waiting for someone to write a love letter to the infamous Windows INI file format...)

View on HN

In fairness there are also several ambiguities with JSON. How do you handle multiple copies of the same key? Does the order of keys have semantic meaning?

jq supports several pseudo-JSON formats that are quite useful like record separator separated JSON, newline separated JSON. These are obviously out of spec, but useful enough that I've used them and sometimes piped them into a .json file for storage.

Also, encoding things like IEEE NaN/Infinity, and raw byte arrays has to be in proprietary ways.

JSON lines is not JSON It is built on top of it. .jsonl extension can be used to make it clear https://jsonlines.org/

Back in my day it was called NDJSON.

The industry is so chaotic now we keep giving the same patterns different names, adding to the chaos.

> How do you handle multiple copies of the same key

That’s unambiguously allowed by the JSON spec, because it’s just a grammar. The semantics are up to the implementation.

interestingly other people are answering the opposite in this thread.

They're wrong.

From ECMA-404[1] in section 6:

> The JSON syntax does not impose any restrictions on the strings used as names, does not require that name strings be unique, and does not assign any significance to the ordering of name/value pairs.

That IS unambiguous.

And for more justification:

> Meaningful data interchange requires agreement between a producer and consumer on the semantics attached to a particular use of the JSON syntax. What JSON does provide is the syntactic framework to which such semantics can be attached

> JSON is agnostic about the semantics of numbers. In any programming language, there can be a variety of number types of various capacities and complements, fixed or floating, binary or decimal.

> It is expected that other standards will refer to this one, strictly adhering to the JSON syntax, while imposing semantics interpretation and restrictions on various encoding details. Such standards may require specific behaviours. JSON itself specifies no behaviour.

It all makes sense when you understand JSON is just a specification for a grammar, not for behaviours.

[1]: https://ecma-international.org/wp-content/uploads/ECMA-404_2...

> and does not assign any significance to the ordering of name/value pairs.

I think this is outdated? I believe that the order is preserved when parsing into a JavaScript Object. (Yes, Objects have a well-defined key order. Please don't actually rely on this...)

In the JS spec, you'd be looking for 25.5.1

If I'm not mistaken, this is the primary point:

> Valid JSON text is a subset of the ECMAScript PrimaryExpression syntax. Step 2 verifies that jsonString conforms to that subset, and step 10 asserts that that parsing and evaluation returns a value of an appropriate type.

And in the algorithm

    c. Else,
      i. Let keys be ? EnumerableOwnProperties(val, KEY).
      ii. For each String P of keys, do
        1. Let newElement be ? InternalizeJSONProperty(val, P, reviver).
        2. If newElement is undefined, then
          a. Perform ? val.[[Delete]](P).
        3. Else,
          a. Perform ? CreateDataProperty(val, P, newElement).

If you theoretically (not practically) parse a JSON file into a normal JS AST then loop over it this way, because JS preserves key order, it seems like this would also wind up preserving key order. And because it would add those keys to the final JS object in that same order, the order would be preserved in the output.

> (Yes, Object's have a well-defined key order. Please don't actually rely on this...)

JS added this in 2009 (ES5) because browsers already did it and loads of code depended on it (accidentally or not).

There is theoretically a performance hit to using ordered hashtables. That doesn't seem like such a big deal with hidden classes except that `{a:1, b:2}` is a different inline cache entry than `{b:2, a:1}` which makes it easier to accidentally make your function polymorphic.

In any case, you are paying for it, you might as well use it if (IMO) it makes things easier. For example, `let copy = {...obj, updatedKey: 123}` is relying on the insertion order of `obj` to keep the same hidden class.

In JS maybe (I don't know tbh), but that's irrelevant to the JSON spec. Other implementations could make a different decision.

Ah, I thought the quote was from the JS spec. I didn't realize that ECMA published their own copy of the JSON spec.

Internet JSON (RRC 7493) forbids objects to have members with duplicate names.

As it says:

I-JSON (short for "Internet JSON") is a restricted profile of JSON designed to maximize interoperability and increase confidence that software can process it successfully with predictable results.

So it's not JSON, but a restricted version of it.

I wonder if use of these restrictions is popular. I had never heard of I-JSON.

I think it's rare for them to be explicilty stated, but common for them to be present in practice. I-JSON is just an explicit list of these common implicit limits. For any given tool/service that describes itself as accepting JSON I would expect I-JSON documents to be more likely to work as expected than non-I-JSON.

> How do you handle multiple copies of the same key? Does the order of keys have semantic meaning?

This is also an issue, due to the way that order of keys are working in JavaScript, too.

> record separator separated JSON, newline separated JSON.

There is also JSON with no separators, although that will not work very well if any of the top-level values are numbers.

> Also, encoding things like IEEE NaN/Infinity, and raw byte arrays has to be in proprietary ways.

Yes, as well as non-Unicode text (including (but not limited to) file names on some systems), and (depending on the implementation) 64-bit integers and big integers. Possibly also date/time.

I think DER avoids these problems. You can specify whether or not the order matters, you can store Unicode and non-Unicode text, NaN and Infinity, raw byte arrays, big integers, and date/time. (It avoids some other problems as well, including canonization (DER is already in canonical form) and other issues. Although, I have a variant of DER that avoids some of the excessive date/time types and adds a few additional types, but this does not affect the framing, which can still be parsed in the same way.)

A variant called "Multi-DER" could be made up, which is simply concatenating any number of DER files together. Converting Multi-DER to BER is easy just by adding a constant prefix and suffix. Converting Multi-DER to DER is almost as easy; you will need the length (in bytes) of the Multi-DER file and then add a prefix to specify the length. (In none of these cases does it require parsing or inspecting or modifying the data at all. However, converting the JSON variants into ordinary JSON does require inspecting the data in order to figure out where to add the commas.)

Plus the 64-bit integer problem, really 52-bit integers, due to JS not having integers.

JSON itself is not limited to neither 52 nor 64-bit integers.

    integer = -? (digit | onenine digit+)

https://json.org/

That’s a JavaScript problem, not JSON.

Most good parsers have an option to parse to integers or arbitrary precision decimals.

Agreed. Which means that Javascript does not have a good parser.

`JSON.parse` actually does give you that option via the `reviver` parameter, which gives you access to the original string of digits (to pass to `BigInt` or the number type of your choosing) – so per this conversation fits the "good parser" criteria.

To be specific (if anyone was curious), you can force BigInt with something like this:

    //MAX_SAFE_INTEGER is actually 9007199254740991 which is 16 digits
    //you can instead check if exactly 16 and compare size one string digit at a time if absolute precision is desired.
    const bigIntReviver = (key, value, context) => typeof value === 'number' && Math.floor(value) === value && context.source.length > 15 ? BigInt(context.source) : value
      

    const jsonWithBigInt = x => JSON.parse(x, bigIntReviver)

Generally, I'd rather throw if a number is unexpectedly too big otherwise you will mess up the types throughout the system (the field may not be monomorphic) and will outright fail if you try to use math functions not available to BigInts.

Huh, TIL!

https://caniuse.com/mdn-javascript_builtins_json_parse_reviv...

Absent in Safari though

Sadly the reviver parameter is a new invention only recently available in FF and Node, not at all in Safari.

Naturally not that hard to write a custom JSON parser but the need itself is a bad thing.

Just use the polyfill

https://github.com/zloirock/core-js#jsonparse-source-text-ac...

No it's been there for ages. Finalized as part of ecmascript 5

What you are probably thinking of is the context parameter of the reviver callback. That is relatively recent and mostly a qol improvement

Sorry yes, i was thinking of the context object with source parameter.

The issue it solves is a big one though, since without it the JSON.parse functionality cannot parse numbers that are larger than 64bit float numbers (f.ex. bigints).

bigint exists

They do specifically mention this:

“No one owns CSV. It has no real specification (yes, I know about the controversial ex-post RFC 4180), just a set of rules everyone kinda agrees to respect implicitly. It is, and will forever remain, an open and free collective idea.”

They even seem to think it is a good thing. But I don't see how not having a bunch of implementations that can't agree on the specifics of a file/interchange format is a good thing. And being free and open is completely orthogonal. There are many proprietary formats that don't have a spec, and many open formats that do have a spec (like, say, json).

That's true of the vast majority of protocols that people use in real life to exchange date. We're using one of them right now, in fact.

Waiting for someone to write a love letter to the infamous Windows INI file format

I actually miss that. It was nice when settings were stored right alongside your software, instead of being left behind all over a bloated registry. And the format was elegant, if crude.

I wrote my own library for encoding/writing/reading various datatypes and structure into ini's, in a couple different languages, and it served me well for years.

TOML is nice like that... elegant like INI, only with lists.

> instead of being left behind all over a bloated registry

Really? I think the idea of a central, generic, key-value pair database for all the setting on a system is probably the most elegant reasonable implementation there could be.

The initial implementation of Windows Registry wasn't good. It was overly simplistic and pretty slow. Though the "bloat" (what ever that means) of registry hasn't been an actual issue in over 20 years. The only people invested in convincing you "it's an issue" are CCleaner type software that promise to "speed up your computer" if you just pay $6.99.

How many rows do you need in a sqlite database for it to be "bloated"?

I feel like YAML is a spiritual successor to the .ini, since it shares a notable ideal of simple human readability/writability.

Whenever I ask myself "should I use YAML?" I answer myself "Norway".

To be fair to YAML that's been solved in 1.2.

https://yaml.org/spec/1.2.2/#10212-boolean

> I feel like YAML is a spiritual successor to the .ini, since it shares a notable ideal of simple human readability/writability.

It doesn't feel that way to me: it's neither simple to read nor to write. I suppose that that's a builtin problem due to tree representation, which is something that INI files were never expected to represent.

TBH, I actually prefer the various tree representation workarounds used by INI files: using whitespace to indicate child nodes stops being readable once you have more than a screenful of children in a node.

Given how YAML does magic and sometimes accidental type conversions of potentially nested objects, I think TOML is the well-defined sucessor to .ini

YAML is readable? No way as there are too many ways to do the same thing and nested structures are unclear to the non trained eye (what is a list? What is nested?), let alone indentation in large files is an issue especially with the default 2 space unreadable standard so many people adhere to.

YAML simple? It's sepc is larger than XML... Parsing of numbers and strings is ambiguous, leading zeros are not strings but octal (implicit conversion...). List as keys? Oh ffs, and you said readable. And do not get me started about "Yes" being a boolean, reminds me of the MS Access localizations which had other decimal values for true and [local variant of true] (1 vs -1).

Writable? Even worse. I think I have never been able to write a YAML file without errors. But that might just be me, XML is fine though while unreadable.

I agree that one could make wild YAML if you get into advanced stuff, but I make YAML files that look like this:

  things:
    - title: "A thing"
      item_id: "deadbeef-feb1-4e8c-b61c-dd9a7a9fffff"
      is_active: true
      favorite_formats:
        - yml
        - ini
    - title: "Another thing"
      item_id: "deadbeef-feb1-3333-4444-dd9a7a9fffff"
      is_active: false
      favorite_formats:
        - mp3
        - wav

Just because you can use it to create a monstrosity doesn't prevent it from being useful for simple configuration. Basically, it's just prettier JSON.

Say "no" to YAML. As a string, if you can.

You can. YAML 1.2 is only 16 years old. Just old enough to drive. Norway problem has been solved for only 16 years.

YAML 1.2 leaves data types ambiguous, merely making the "Norway problem" optional and at the mercy of the application rather than, in the words of https://yaml.org/type/ (which has not been marked as deprecated), "strongly recommended".

Those schemas aren't part of the core schema, and you may interpret them if you are aiming for full 1.1 compatibility. If you're aiming for 1.1 compatibility, then you accept the Norway problem.

I've been looking in the specs and I can't find the link to the https://yaml.org/type/

I think GRON[1] would fit the bill better

[1] https://github.com/tomnomnom/gron

The post does mention it, as a positive:

https://github.com/medialab/xan/blob/master/docs/LOVE_LETTER...

People who say that CSV is "simpler" are talking about whatever format Excel exports.

Also these people have only ever had to deal with the American Excel localization.

So yeah, with the caveat of "only ever use Excel and only ever the American edition" CSV is pretty nice.

As someone living in a country where , is used as the decimal separator, I cannot begin to describe the number of times CSV data has caused me grief. This becomes especially common in an office environment where Excel is the de facto only data handling tool that most people can and will use. Here the behavior of loading data becomes specific to the individual machine and changes over time (e.g. when IT suddenly forces a reset of MS Office application languages to the local one).

That said, I don't really know of any alternative that won't be handled even worse by my colleagues...

Also keeping in mind all the locales where comma is the decimal point…tsv for the world.

And all the 'simple' formats start failing when dealing with blocks of text.

To be honest, I'm wondering why you are rating JSON higher than CSV.

> Unlike XML or JSON, there isn't a document defining the grammar of well-formed or valid CSV files,

There is, actually, RFC 4180 IIRC.

> there are many flavours that are incompatible with each other in the sense that a reader for one flavour would not be suitable for reading the other and vice versa.

"There are many flavours that deviate from the spec" is a JSON problem too.

> you cannot tell programmatically whether line 1 contains column header names or already data (you will have to make an educated guess but there ambiguities in it that cannot be resolved by machine).

Also a problem in JSON

> Quoting, escaping, UTF-8 support are particular problem areas,

Sure, but they are no more nor no less a problem in JSON as well.

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.

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.

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).

> 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/

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.

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.)

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.

Hi,

Yes, we chose ARFF format, which is idiosyncratic yet well-defined back in the old data mining days.

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.

>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.

> 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?

>Can't they?

If my experience reflects a relevant sample then the answer is that most can but a very significant minority fails at the job (under the given working conditions).

Whether or not _you_ can is a separate question. I don't see anything wrong with your code. It does of course assume that whatever is contained in rows is correct. It also assumes that the result is correctly written to a file without making any encoding mistakes or forgetting to flush the stream.

Not using name value pairs makes CSV more prone to mistakes such as incorrect ordering or number of values in some rows, a header row that doesn't correspond with the data rows, etc. Some export files are merged from multiple sources or go through many iterations over many years, which makes such mistakes far more likely.

I have also seen files that end abruptly somewhere in the middle. This isn't specific to CSV but it is specific to not using libraries and not using libraries appears to be more prevalent when people generate CSV.

You'd be surprised how many CSV files are out there where the developer tried to guess incorrectly whether or not a column would ever have to be escaped. Maybe they were right initially and it didn't have to be escaped but then years later something causes a change in number formats (internationalisation) and bang, silent data corruption.

Prioritising correctness and robustness over efficiency as you have done is the best choice in most situations. Using a well tested library is another option to get the same result.

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_r...

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.

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.

> 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.

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.

SAP only has to be SAP and MS Excel compatible. The rest is not needed so in their eyes it is probably to spec.

European quality™.

> 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.

> 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.

> 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.

And CSV parsers and serializers compliant with RFC 4180 are similarly reliable.

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.

That RFC doesn't even support utf8.

It is, and accepts it is, codifing best practices rather than defining an authoritative standard.

There are always many, but in comparison to csv I've received almost no differences. Json issues were rare but csv issues it was common to have a brand new issue per client.

Typically the big difference is there are different parsers that are less tolerant of in spec values. Clickhouse had a more restrictive parser, and recently I've dealt with matrix.

Maybe I've been lucky for json and unlucky for csv.

What's the problem with capital I?

https://superuser.com/questions/210027/why-does-excel-think-... says itks not capital I but “ID”.

Basically, Excel uses the equivalent of ‘file’ (https://man7.org/linux/man-pages/man1/file.1.html), sees the magic “ID”, and decides a SYLK file, even though .csv files starting with “ID” have outnumbered .SYLK files by millions for decades.

Thanks. So I guess the easy compatible solution is to always quote the first item on the first line when writing CSV. Good to know. (Checking if the item starts with ID is more work. Possibly quote all items on the first line for simplicity.) (Reading SYLK is obviously irrelevant, so accepting unquoted ID when reading is the smarter way to go and will actually improve compatibility with writers that are not Excel. Also it takes no work.)

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!

It sounds to me like as often the problem here is Excel, not CSV

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.

There is a common misconception here. You can import CSV files into an excel sheet. You cannot open a CSV file with excel. That is a nonsense operation.

Excel do not ask the user whether they want to import file, and tell user their file was broken.

Clients don't particularly make the distinction, and in a way nor should they - they can't open your file.

Probably referring to the "turkish i problem"

Not an unreasonable guess, but it turned out to be something different.

> There is, actually, RFC 4180 IIRC.

Does any software fully follow that spec (https://www.rfc-editor.org/rfc/rfc4180)? Some requirements that I doubt are commonly followed:

- “Each record is located on a separate line, delimited by a line break (CRLF)” ⇒ editing .csv files using your the typical Unix text editor is complicated.

- “Spaces are considered part of a field and should not be ignored”

- “Fields containing line breaks (CRLF), double quotes, and commas should be enclosed in double-quotes” ⇒ fields containing lone carriage returns or new lines need not be enclosed in double quotes.

> Unlike XML or JSON, there isn't a document defining the grammar of well-formed or valid CSV files

There is such a document: RFC 4180. It may not be a good document, but it does exist.

INI was for a long time a seemingly preferable format in the Python community for configuration for a long time, as I recall it.

Haven’t been a full time Python dev in sometime though, it seems TOML has supplanted that, but I remember thinking how interesting it was that Python had a built in INI parser and serializer

I lived through SOAP/WSDL horror with their numerous standards and the lack of compatibility between stacks in different programming languages. Having seen abused XML, CSV formats. CSV is preferable over XML. Human-readability matters. Relative simplicity matters.

Despite JSON may also be interpreted differently by different tools, it is a good default choice for communicating between programs

> lack of compatibility between stacks in different programming languages

Well, that sure beats OpenAPI lack of compatibility between stacks in the same programming language.

I think the fact one can't randomly concatenate strings and call it "valid XML" a huge bonus over the very common "join strings with comma and \r\n", non-rfc4180 compliant (therefore mostly unparseable without human/LLM interaction) garbage people often pretend is CSV.

While CSV isn't exactly grammared or standardised like XML I think if it as more schema:d than JSON. There might be data corruption or consistency issues, but there is implicitly a schema: every line is exactly n fields, and the first line might contain field names.

When a JSON API turns out to have optional fields it usually shows through trial and error, and unlike CSV it's typically not considered a bug you can expect the API owner to fix. In CSV 'missing data' is an empty string rather than nulls or their cousins because missing fields aren't allowed, which is nice.

I also like that I can write my own ad hoc CSV encoder in most programming languages that can do string concatenation, and probably also a suitable decoder. It helps a lot in some ETL tasks and debugging. Decent CSV also maps straight to RDBMS tables, if the database for some reason fails at immediate import (e.g. too strict expectations) into a newly created table it's almost trivial to write an importer that does it.

JSON is not schema’d per se and intentionally so. There’s jsonschema which has better expressiveness than inference of a tabular schema, as it can reflect relationships.

Sure. I have yet to come across a data source with JSON Schema, I'll develop an opinion of it when I do.

There is no file format that works out of box under all extreme corner cases.

You would think that ie XML-defined WSDL with XSD schema is well battle proven. I've encountered 2 years ago (and still dealing with that) WSDL from a major banking vendor that is technically valid, but no open source library in Java (from all languages) was able to parse it successfully or generate binding classes out of box.

Heck, flat files can end up with extreme cases, just work enough with legacy banking or regulatory systems and you will see some proper shit.

The thing is, any sort of critical integration needs to be battle tested and continuously maintained, otherwise it will eventually go bad, even a decade after implementation and regular use without issues.

What about S-expressions? Where do they break?

XML is a pretty good markup language. Using XML to store structured data is an abuse of it. All the features that are useful for markup are not useful for structured data and only add to the confusion.

> Waiting for someone to write a love letter to the infamous Windows INI file format...

Honestly, it’s fine. TOML is better if you can use it, but otherwise for simple applications, it’s fine. PgBouncer still uses INI, though that in particular makes me twitch a bit, due to discovering that if it fails to parse its config, it logs the failed line (reasonable), which can include passwords if it’s a DSN string.

Well, once you get over the fact that information on a TOML file can be out of order in any place, denominated by any mix of 3 different key encodings, and broken down in any random way... then yes, the rest of TOML is good.

I should write a love letter to JSON.

For as good as JSON is or is not, it's definitely not under-rated.

It does mention this. Point 2.

why you hate csv, not the program that is not able to properly create csv?

Reply on news.ycombinator.com