Hacker News Reader

Comment by mjw_byrne | Hacker News Reader

CSV is ever so elegant but it has one fatal flaw - quoting has "non-local" effects, i.e. an extra or missing quote at byte 1 can change the meaning of a comma at byte 1000000. This has (at least) two annoying consequences:

1. It's tricky to parallelise processing of CSV. 2. A small amount of data corruption can have a big impact on the readability of a file (one missing or extra quote can bugger the whole thing up).

So these days for serialisation of simple tabular data I prefer plain escaping, e.g. comma, newline and \ are all \-escaped. It's as easy to serialise and deserialise as CSV but without the above drawbacks.

View on HN

JSON serialized without extra white space with one line per record is superior to CSV.

If you want CSV-ish, enforce an array of strings for each record. Or go further with actual objects and non-string types.

You can even jump to an arbitrary point and then seek till you see an actual new line as it’s always a record boundary.

It’s not that CSV is an invalid format. It’s that libraries and tools to parse CSV tend to suck. Whereas JSON is the lingua franca of data.

What happens when you need to encode the newline character in your data? That makes splitting _either_ CSV or LDJSON files difficult.

When you need to encode the newline character in your data, you say \n in the JSON. Unlike (the RFC dialect of) CSV, JSON has an escape sequence denoting a newline and in fact requires its use. The only reason to introduce newlines into JSON data is prettyprinting.

The new line character in a JSON string would always be \n. The new line in the record itself as whitespace would not be acceptable as that breaks the one line record contract.

Remember that this does not allow arbitrary representation of serialized JSON data. But it allows for any and all JSON data as you can always roundtrip valid JSON to a compact one line representation without extra whitespace.

It's tricky, but simple enough, RFC states that " must be used, inserting a " is done with "". This makes knowing what a record is difficult, since you must keep a variable that keeps the entire string.

How do you do this simply? you read each line, and if there's an uneven number of ", then you have an incomplete record and you will keep all lines until there is an odd number of ". after having the string, parsing the fields correctly is harder but you can do it in regex or PEGs or a disgusting state machine.

Actually even whitespace-separated json would be a valid format and if you forbid json documents to be a single integer or float then even just concatenating json gives a valid format as JSON is a prefix free language.

That is[0] if a string s is a valid JSON then there is no substring s[0..i] for i < n that is a valid json.

So you could just consume as many bytes you need to produce a json and then start a new one when that one is complete. To handle malformed data you just need to throw out the partial data on syntax error and start from the following byte (and likely throw away data a few more times if the error was in the middle of a document)

That is [][]""[][]""[] is unambiguos to parse[1]

[0] again assuming that we restrict ourselves to string, null, boolean, array and objects at the root

[1] still this is not a good format as a single missing " can destroy the entire document.

« a single missing " can destroy the entire document » This is basically true for any data format, so really worse argument ever...

In jsonl a modified chunk will lose you at most the removed lines and the two adjacent ones (unless the noise is randomly valid json), in particular a single byte edit can destry at most 2 lines.

utf-8 is also similarly self-correcting and so is html and many media formats.

My point was that in my made-up concatenated json format

[]"""[][][][][][][][][][][]"""[]

and

[]""[][][][][][][][][][][]""[]

are both valid but have differ only for 2 bytes but have entirely different structures.

Also it is a made-up format nobody uses (if somebody were to want this they would likely disallow strings at the root level).

JSON is a textual encoding no different than CSV.

It's just that people tend to use specialized tools for encoding and decoding it instead of like ",".join(row) and row.split(",")

I have seen people try to build up JSON strings like that too, and then you have all the same problems.

So there is no problem with CSV except that maybe it's too deceptively simple. We also see people trying to build things like URLs and query strings without using a proper library.

The problem with CSV is that there's no clear standard, so even if you do reach for a library to parse it, that doesn't ensure compatibility.

Same for JSON though. What Python considers a valid JSON might not be that if you ask a Java library.

JSON has a clearly-defined standards: ISO/IEC 21778:2017, IETF RFC 7159, and ECMA-404. Additionally, Crockford has had a spec available on json.org since it's creation in 2001.

Do you have any examples of Python, Java, or any of the other Tiobe top 40 languages breaking the JSON spec in their standard library?

In contrast, for the few of those that have CSV libraries, how many of those libraries will simply fail to parse a large number of the .csv variations out there?

Not to mention that stuff like Excel loves to export CSV files in "locale specific ways".

Sometimes commas to delimiter, sometimes semicolons, floating point values might have dots or commas to separate fraction digits.

Not to mention text encodings, Ascii, western european character sets, or maybe utf-8 or whatever...

It's a bloody mess.

That’s why we just email the sheets around like it’s 1999 :)

You need more than a standard; that standard has to be complete and unambiguous. What you're looking for is https://github.com/nst/JSONTestSuite

EDIT: The readme's results are from 2016, but there's more recent results (last updated 5 years ago). Of the 54 parsers /versions tested, 7 gave always the expected result per the spec (disregarding cases where the spec does not define a result).

It falls down under very large integers -- think large valid uint64_t values.

JSON doesn't fail for very large values because they are sent over the wire as strings. Only parsers may fail if they or their backing language doesn't account for BigInts or floats larger than f64, but these problems exist when parsing any string to a number.

And indeed applies to CSV as well: it's just strings at the end of the day, its up to the parser to make sense of it into the data types one wants. There is nothing inherently stopping you from parsing a JSON string into a uint64: I've done so plenty!

Example? I know there's some ambiguity over whether literals like false are valid JSON, but I can't think of anything else.

That _shouldn't_ be ambiguous, `false` is a valid JSON document according to specification, but not all parsers are compliant.

There's some interesting examples of ambiguities here: https://seriot.ch/projects/parsing_json.html

Trailing commas, comments, duplicate key names, for a few examples.

Trailing commas and comments are plainly not standard JSON under any definition. There are standards that include them which extend JSON, sure, but I'm not aware of any JSON library that emits this kind of stuff by default.

I'm not aware of any CSV library that doesn't follow RFC4180 by default, and yet... this whole thread.

There is a clear standard and it's usually written on an old Word '97 doc in a local file server. Using CSV means that you are the compatibility layer, and this is useful if you need firm control or understanding of your data.

If that sounds like a lot of edge-case work keep in mind that people have been doing this for more than half a century. Lots of examples and notes you can steal.

https://datatracker.ietf.org/doc/html/rfc4180 exists

And does Excel fully comply and more imprtantly tell you when the CSV file is wrong

No. Excel's fault. Not CSV. There are plenty of busted CSV parsers (and serializers) too.

> It's just that people tend to use specialized tools for encoding and decoding it instead of like ",".join(row) and row.split(",")

You really super can't just split on commas for csv. You need to handle the string encodings since records can have commas occur in a string, and you need to handle quoting since you need to know when a string ends and that string may have internal quote characters. For either format unless you know your data super well you need to use a library.

Right, obviously.

Yes but people don't

Eh, it really isn't. The format does not lend itself to tabular data, instead the most natural way of representing data involves duplicating the keys N times for each record.

You can easily represent it as an array:

    [“foo”,”bar”,123]

That’s as tabular as CSV but you now have optional types. You can even have lists of lists. Lists of objects. Lists of lists of objects…

You're missing my point: basically nothing spits out data in that format because it's not ergonomic to do so. JSON is designed to represent object hierarchies, not tabular data.

JSON was designed to represent any data. There's plenty of systems that spit out data in exact that format because it's the natural way to represent tabular data using JSON serialization. And clearly if you're the one building the system you can choose to use it.

CSV is lists of lists of fixed length.

JSON is lists of lists of any length and groups of key/value pairs (basically lisp S-expressions with lots of unnecessary syntax). This makes it a superset of CSV's capabilities.

JSON fundamentally IS made to represent tabular data, but it's made to represent key-value groups too.

Why make it able to represent tabular data if that's not an intended use?

> CSV is lists of lists of fixed length.

I'd definitely put that in my list of falsehoods programmers believe about CSV files.

CSV is a text file that might have commas in it

It seems to be indicated by RCF-4180 which says

> This header will contain names corresponding to the fields in the file and should contain the same number of fields as the records in the rest of the file

But of course, CSV is the wild west and there's no guarantee that any two encoders will do the same thing (sometimes, there's not even a guarantee that the same encoder will do the same thing with two different inputs).

[0] https://www.ietf.org/rfc/rfc4180.txt

You should know that "should" isn't very binding.

Headers should have as many rows as possible that contain data items for their column and data items in a row should have a header for the respective columns, but real CSV files should be assumed to have incomplete or variable length lines.

NOTHING is very binding about the CSV spec and that's the biggest problem with CSV.

yeah if I had a cookie for every time I've had to deal with this I'd have maybe 10 cookies - it's not a lot but it's more than it should be.

The fact that json can represent a superset of tabular data structures that csv is specifically designed to represent can be rephrased into that csv is more specialised than json in representing tabular data. The fact that json can also represent tabular data does not mean it is a better or more efficient way to represent that data instead of a format like csv.

In the same way, there are hierarchically structured datasets that can be represented by both json in hierarchical form and csv in tabular form by repeating certain variables, but if using csv would require repeating them too many times, it would be a bad idea to choose that instead of json. The fact that you can do sth does not always make it a good idea to do it. The question imo is about which way would be more natural, easy or efficient.

It's hardly a bad idea to do a list of lists in JSON...

The big advantage of JSON is that it's standardized and you can reuse the JSON infrastructure for more than just tabular data.

> The fact that json can represent a superset of tabular data structures that csv is specifically designed to represent can be rephrased into that csv is more specialised than json in representing tabular data. The fact that json can also represent tabular data does not mean it is a better or more efficient way to represent that data instead of a format like csv.

The reverse is true as well: being more specialized is a description of goals, not advantages.

> JSON is lists of lists of any length and groups of key/value pairs

The "top-level" structure of JSON is usually an object, but it can be a list.

> JSON fundamentally IS made to represent tabular data

No, it's really not. It's made to represent objects consisting of a few primitive types and exactly two aggregate types: lists and objects. It's a textual representation of the JavaScript data model and even has "Object" in the name.

> Why make it able to represent tabular data if that's not an intended use?

It's mostly a question of specialization and ergonomics, which was my original point. You can represent tabular data using JSON (as you can in JavaScript), but it was not made for it. Anything that can represent """data""" and at least 2 nesting levels of arbitrary-length sequences can represent tabular data, which is basically every data format ever regardless of how awkward actually working with it may be.

JSON is designed to represent JavaScript objects with literal notation. Guess what, an array of strings or an array of numbers or even an array of mixed strings and numbers is a commonly encountered format in JavaScript.

A format consisting of newline-terminated records, each containing comma-separated JSON strings would be superior to CSV.

It could use backslash escapes to denote control characters and Unicode points.

Everyone would agree exactly on what the format is, in contrast to the zoo of CSV variants.

It wouldn't have pitfalls in it, like spaces that defeat quotes

  RFC CSV         JSON strings
  a,"b c"         "a", "b c"
  a, "b c"        "a", " \" b c\""

oops; add an innocuous-looking space, and the quotes are now literal.

Right - the JSON-newline equivalent of CSV can look like this:

    ["id", "species", "nickname"]
    [1, "Chicken", "Chunky cheesecakes"]
    [2, "Dog", "Wagging wonders"]
    [3, "Bunny", "Hopping heroes"]
    [4, "Bat", "Soaring shadows"]

Remove the [] characters and you've invented CSV with excel style quoting.

Almost, except the way Excel-style quoting works with newlines sucks - you end up with rows that span multiple lines, so you can't split on newline to get individual rows.

With JSON those new lines are \n characters which are much easier to work with.

I ended up parsing the XML format instead of the CSV format when handling paste from Excel due to the newlines issue.

CSV seemed so simple but after numerous issues, a cell with both newline and " made me realize I should keep the little hair I had left and put in the work to parse the XML.

It's not great either, with all its weird tags, but at least it's possible to parse reliably.

This misses the point of standardization imo because it’s not possible to know a priori that the first line represents the variable names, that all the rows are supposed to have the same number of elements and in general that this is supposed to represent a table. An arbitrary parser or person wouldn’t know to guess since it's not standard or expected. Of course it would be parsed fine but the default result would be a kind of structure or multi-array rather than tabular.

application/jsonl+table

This is the way. jsonl where each row is a json list. It has well-defined standard quoting.

Just like csv you don't actually need the header row either, as long as there's convention about field ordering. Similar to proto bufs, where the field names are not included in the file itself.

Typing isn't optional in JSON, every value has a concrete type, always.

  [
   [“header1”,”header2”],
   [“1.1”, “”],
   [7.4, “2022-01-04”]
  ]

...and?

I see an array of arrays. The first and second arrays have two strings each, the last one has a float and a string. All those types are concrete.

Let's say those "1.1" and 7.4 values are supposed to be version strings. If your code is only sometimes putting quotes around the version string, the bug is in your code. You're outputting a float sometimes, but a string in others. Fix your shit. It's not your serialization format that's the problem.

If you have "7.4" as a string, and your serialization library is saying "Huh, that looks like a float, I'm going to make it a float", then get a new library, because it has a bug.

Types at the type layer are not the same as types at the semantic layer. Sure every type in the JSON level has a "strong type" but the semantic meaning of the contents of e.g. a string are usually not expressable in pure JSON. So it is with CSV; you can think of every cell in CSV as containing a string (series of bytes) with it being up to you to enforce the semantics atop those bytes. JSON gives you a couple extra types, and if you can fit things into those types well, then that's great, but for most data concrete semantically meaningful data you won't be able to do that and you'll end up in a similar world to CSVs.

> It’s that libraries and tools to parse CSV tend to suck. Whereas JSON is the lingua franca of data.

This isn't the case. An incredible amount of effort and ingenuity has gone into CSV parsing because of its ubiquity. Despite the lack of any sort of specification, it's easily the most widely supported data format in existence in terms of tools and language support.

Can you point me to a language with any significant number of users that does NOT have a JSON library?

I went looking at some of the more niche languages like Prolog, COBOL, RPG, APL, Eiffel, Maple, MATLAB, tcl, and a few others. All of these and more had JSON libraries (most had one baked into the standard library).

The exceptions I found (though I didn't look too far) were: Bash (use jq with it), J (an APL variant), Scratch (not exposed to users, but scratch code itself is encoded in JSON), and Forth (I could find implementations, but it's very hard to pin down forth dialects).

I made no claim about JSON libraries. I contested the claim that "CSV libraries and tools suck". They do not.

You claimed that CSV is "easily the most widely supported data format in existence in terms of tools and language support", which is a claim that CSV is better supported than JSON, which is a claim that JSON support is lacking.

Can you import .jsonl files into Google sheets or excel natively?

Importing csvs in excel can be a huge pain due to how excel handles localisation. It can basically alter your data if you are not mindful about that, and I have seen it happening too many times.

For example:

Scientists rename genes because Microsoft Excel reads them as dates (2020)

https://www.reddit.com/r/programming/comments/i57czq/scienti...

I was so glad of that story. It gave me something to point to to get my boss off my back.

But it handles it better than Json.

Depends on what you mean by "better". I would rather software not handle a piece of data at all, than handle it erroneously and changing the data without me realising and thus causing all sorts of issues after.

In practice, web browsers accept the tag soup that is sometimes called html and strict xml-based formats failed.

The browser are not a database (unlike excel). Modifying data before showing it is reversible, modifying it before storing it is not.

Excel dropping leading zeros (as in ZIP codes) was a crazy design decision that has certainly cost many lifetimes of person-hours.

And forcing 16+ digits to be floats, destroying information.

Yeah have had similar struggles with social security numbers.

CSV tooling has had to invest enormous amounts of effort to make a fragile, under-specified format half-useful. I would call it ubiquitous, I would call the tooling that we’ve built around it “impressive” but I would by no means call any of it “good”.

I do not miss dealing with csv files in the slightest.

Microsoft Windows has had to invest enormous amounts...

Apple macOS has had to invest enormous amounts...

Pick your distro of Linux has had to invest enormous amounts...

None of them a perfect and any number of valid complaints can be said about any of them. None of the complaints make any of the things useless. Everyone has workarounds.

Hell, JSON has had to invest enormous amounts of effort...

I guess the point is that I can take a generic json parser and point it at just about any JSON I get my hands on, and have close to no issues parsing it.

Want to do the same with csv? Good luck. Delimiter? Configurable. Encoding? Configurable. Misplaced comma? No parse in JSON, in csv: might still parse, but is now semantically incorrect and you possibly won’t know until it’s too late, depending on your parser. The list goes on.

Here is a quick test

The table of contents points to a single Json object that is 20ish gb compressed

https://www.anthem.com/machine-readable-file/search/

All stock libs will fail

> CSV tooling has had [...] to make a fragile, under-specified format half-useful

You get this backwards. Tabular structured data to store are ubiquitous. Text as a file format is also ubiquitous because it is accessible. The only actual decisions are about whether to encode your variables as rows or columns, what is the delimiter, and other rules such as escaping etc. Vars as columns makes sense because it makes appending easier. There is a bunch of stuff that can be used for delimeters, commas being the most common, none is perfect. But from this point onwards, decisions do not really matter, and "CSV" basically covers everything from now on. "CSV" is basically what comes naturally when you have tabular datasets and want to store them in text. CSV tooling is developed because there is a need for this way of formatting data. Whether CSV is "good" or "ugly" or whatever is irrelevant, handling data is complicated as much as the world itself is. The alternatives are either not structuring/storing the data in a tabular manner, or non-text (eg binary) formats. These alternative exist and are useful in their own right, but don't solve the same problems.

I think the issue is that CSV parsing is really easy to screw up. You mentioned delimiter choice and escaping, and I’d add header presence/absence to that list.

There are at least 3 knobs to turn every time you want to parse a CSV file. There’s reasonably good tooling around this (for example, Python’s CSV module has 8 parser parameters that let you select stuff), but the fact that you have to worry about these details is itself a problem.

You said “handling data is complicated as much as the world itself is”, and I 100% agree. But the really hard part is understanding what the data means, what it describes. Every second spent on figuring out which CSV parsing option I have to change could be better spent actually thinking about the data.

I am kind of amazed how people nag about having to parse practically a random file.

Having header or not should be specified up front and one should not parse some unknown file because that will always end up with failure.

If you have your own serialization and your own parsing working yeah this will simply work.

But then not pushing back to the user some errors and trying to deal with everything is going to be frustrating because amount of edge cases is almost infinite.

Handling random data is hard, saying it is a CSV and trying to support everything that comes with it is hard.

Excel.

Before you dismiss it as 'not a language, people have argued that it is. And you can definitely program stuff in it, and so that surely makes it a language

Excel can import and parse JSON, it's under the "Get Data" header. It doesn't have a direct GUI way to export to JSON, but it takes just a few lines in Office Scripts. You can even use embedded TypeScript to call JSON.stringify.

Meanwhile, Excel exports to CSV as “semicolon separated values” depending on your OS locale

Albeit for fairly justifiable reasons

Justifiable how?

Decimal separators being commas in some locales?

Commas are commonly used in text, too.

Clearly they should have gone with BEL as the delimiter.

  printf "alice\007london\007uk\nbob\007paris\007france\n" > data.bsv

I'm hoping no reasonable person would ever use BEL as punctuation or decimal separator.

On the off chance you're not being facetious, why not ASCII 0 as a delimiter? (This is a rhetorical question.)

There are some decent arguments for BEL over NUL, however given you posed that as a rhetorical question I feel I can say little other than

ding! ding! ding! winner winner, chicken dinner!

Although BEL would drive me up the wall if I broke out any of my old TTY hardware.

ASCII has characters more or less designed for this

0x1C - File Separator

0x1D - Group Separator

0x1E - Record Separator

0x1F - Unit Separator

So I guess 1F would be the "comma" and 1E would be the "newline."

https://stackoverflow.com/questions/8695118/what-are-the-fil...

I am pretty sure you shifted the meaning, the decimal separator is part of the atomic data it does not need a control character.

You would use 1F instead of the comma/semicolon/tab and 1E to split lines (record means line just like in SQL).

You could then use 1D to store multiple CSV tables in a single file.

Yes but then the text is not human readable or editable in a plain text editor.

This would confuse most users of csvs they are not programmers they at most use text editors and Excel.

I am not proposing to do this, but if you were to use ascii separators you would do it this way

If one was going to use a non-printable character as a delimiter, why wouldn't they use the literal record separator "\030"?

Every time you cat a BSV file, your terminal beeps like it's throwing a tantrum. A record separator (RS) based file would be missing this feature! In other words, my previous comment was just a joke! :)

By the way, RS is decimal 30 (not octal '\030'). In octal, RS is '\036'. For example:

  $ printf '\036' | xxd -p
  1e
  $ printf '\x1e' | xxd -p
  1e

See also https://en.cppreference.com/w/cpp/language/ascii for confirmation.

...and excel macros

Sure, let's put quotation marks around all number values.

Oh wait.

lol

They could have just ignored the locale altogether though. Put dots on the numbers when using csv, and assume it has dots when importing

This exactly. Numbers in XLS(X) are (hopefully) not locale-specific – why should they be in CSV?

CSV -> text/csv

Microsoft Excel -> application/vnd.ms-excel

CSV is a text format, xls[x], json, and (mostly) xml are not.

Well, Excel has a lot of common use-cases around processing numeric (and particularly financial) data. Since some locales use commas as decimal separators, using a character that's frequently present as a piece of data as a delimiter is a bit silly; it would be hard to think of a _worse_ character to use.

So, that means that Excel in those locales uses semicolons as separators rather than the more-frequently-used-in-data commas. Probably not the decision I'd make in retrospect, but not completely stupid.

I’ve found that the number of parsers that don’t handle multiline records is pretty high though.

It's widely, but inconsistently, supported. The behavior of importers varies a lot, which is generally not the case for JSON.

> it's easily the most widely supported data format in existence in terms of tools and language support.

Even better, the majority of the time I write/read CSV these days I don't need to use a library or tools at all. It'd be overkill. CSV libraries are best saved for when you're dealing with random CSV files (especially from multiple sources) since the library will handle the minor differences/issues that can pop up in the wild.

> An incredible amount of effort and ingenuity has gone into CSV parsing because of its ubiquity.

Yea and it's still a partially-parseable shit show with guessed values. But we can and could have and should have done better by simply defining a format to use.

> Despite the lack of any sort of specification

People keep saying this but RFC 4180 exists.

You serialize the keys on every row which is a bit inefficient but it’s a text format anyway

Space-wise, as long as you compress it, it's not going to make any difference. I suspect a JSON parser is a bit slower than a CSV parser, but the slight extra CPU usage is probably worth the benefits that come with JSON.

This is simply not true, parsing json v csv is a difference of thousands of lines.

> JSON [...] with one line per record

Couple of standards that I know of that does this, primarily intended for logging:

https://jsonlines.org/

https://clef-json.org/

Really easy to work with in my experience.

Sure some space is usually wasted on keys but compression takes care of that.

Until you have a large amount of data & need either random access or to work on multiple full columns at once. Duplicated keys names mean it's very easy for data in jsonlines format to be orders of magnitude larger than the same data as CSV, which is incredibly annoying if your processing for it isn't amenable to streaming.

That would be solved by using the ASCII control chars Record Separator / Unit Separator! I don't get how this is not widely used as standard.

The _entire_ point of a CSV file is that it's fully human readable and write-able.

The characters you mention could be used in a custom delimiter variant of the format, but at that point it's back to a binary machine format.

What do you mean? I just push the Record Separator key on my keyboard.

/s in case :)

I’m pretty sure those used to exist.

But when looking for a picture to back up my (likely flawed) memory, Google helpfully told me that you can get a record separator character by hitting Ctrl-^ (caret). Who knew?

The entire argument against ASCII Delimited Text boils down to "No one bothered to support it in popular editors back in 1984. Because I grew up without it, it is impossible to imagine supporting it today."

You need 4 new keyboard shortcuts. Use ctrl+, ctrl+. ctrl+[ ctrl+] You need 4 new character symbols. You need a bit of new formatting rules. Pretty much page breaks decorated with the new symbols. It's really not that hard.

But, like many problems in tech, the popular advice is "Everyone recognizes the problem and the solution. But, the problematic way is already widely used and the solution is not. Therefore everyone doing anything new should invest in continuing to support the problem forever."

The keys would be something other than those, though. They would be: CTRL+\ for file separator, CTRL+] for group separator, CTRL+^ for record separator, CTRL+_ for unit separator. Other than that, it would work like you described, I think.

> But, like many problems in tech, the popular advice is "Everyone recognizes the problem and the solution. But, the problematic way is already widely used and the solution is not

This is unfortunately common. However, what else happens too, is disagreement about what is the problem and the solution.

In Windows (and DOS EDIT.COM and a few other similarly ancient tools) there have existed Alt+028, Alt+029, Alt+030, and Alt+031 for a long time. I vaguely recall some file format I was working with in QBASIC used some or all of them and I was editing those files for some reason. That was not quite as far back as 1984, but sometime in the early 1990s for sure. I believe EDIT.COM had basic glyphs for them too, but I don't recall what they were, might have been random Wingdings like the playing card suits.

Having keyboard shortcuts doesn't necessarily solve why people don't want to use that format, either.

> I believe EDIT.COM had basic glyphs for them too, but I don't recall what they were, might have been random Wingdings like the playing card suits.

That is not specific to EDIT.COM; they are the PC characters with the same codes as the corresponding control characters, so they appear as graphic characters. (They can be used in any program that can use PC character set.)

However, in EDIT.COM and QBASIC you can also prefix a control character with CTRL+P in order to enter it directly into the file (and they appear as graphic characters, since I think the only control characters they will handle as control characters are tabs and line breaks).

Suits are PC characters 3 to 6; these are PC characters 28 to 31 which are other shapes.

> The entire argument against ASCII Delimited Text boils down to "No one bothered to support it in popular editors back in 1984. Because I grew up without it, it is impossible to imagine supporting it today."

There's also the argument of "Now you have two byte values that cannot be allowed to appear in a record under any circumstances. (E.g., incoming data from uncontrolled sources MUST be sanitized to reject or replace those bytes.)" Unless you add an escaping mechanism, in which case the argument shifts to "Why switch from CSV/TSV if the alternative still needs an escaping mechanism?"

One benefit of binary formats is not needing the escaping.

Length-delimited binary formats do not need escaping. But the usual "ASCII Delimited Text" proposal just uses two unprintable bytes as record and line separators, and the signalling is all in-band.

This means that records must not contain either of those two bytes, or else the format of the table will be corrupted. And unless you're producing the data yourself, this means you have to sanitize the data before adding it, and have a policy for how to respond to invalid data. But maintaining a proper sanitization layer has historically been finicky: just look at all the XSS vulnerabilities out there.

If you're creating a binary format, you can easily design it to hold arbitrary data without escaping. But just taking a text format and swapping out the delimiters does not achieve this goal.

At least you don't need these values in your data, unlike the comma, which shows up in human-written text.

If you do need these values in your data, then don't use them as delimiters.

Something the industry has stopped doing, but maybe should do again, is restricting characters that can appear in data. "The first name must not contain a record separator" is a quite reasonable restriction. Even Elon Musk's next kid won't be able to violate that restriction.

I did mean length-delimited binary formats (rather than ASCII formats).

Hear hear! Why is all editing done with text-based editors where humans can make syntax errors. Is it about job security?

It's more like, "because the industry grew up without it, other approaches gained critical mass."

Path dependence is a thing. Things that experience network effects don't get changed unless the alternative is far superior, and ASCII Delimited Text is not that superior.

Ignoring that and pushing for it anyway will at most achieve an xkcd 927.

As soon as you give those characters magic meanings then suddenly people will have reason to want to use them— it'll be a CSV containing localization strings for tooltips that contain that character and bam, we'll be back to escaping.

Except the usages of that character will be rare and so potentially way more scary. At least with quotes and commas, the breakages are everywhere so you confront them sooner rather than later.

Best to add deliberate breakage to the spec, then.

Graphical representations of the control characters begin at U+2400 in the "Control Pictures" Unicode block. Instead of the actual U+001E Record Separator, you put the U+241E Symbol for Record Separator in the help text.

.... with a note underneath urging readers not to copy and paste the character because it's only the graphical representation of it, not the thing itself.

Perhaps a more salient example might be CSV nested in CSV. This happens all the time with XML (hello junit) and even JSON— when you plug a USB drive into my LG TV, it creates a metadata file on it that contains {"INFO":"{ \"thing\": true, <etc> }"}

You wouldn't use this format for that. It's not a universal format, but an application-specific one. It works in some applications, not in others.

Can't there be some magic sequence (like two unescaped newlines) to start a new record?

You’ll still find that sequence in data; it’ll just be rare enough that it won’t rear its ugly head until your solution has been in production for a while.

And that’s why I tend to use tab delimited files more… when viewed with invisible characters shown, it’s pretty clear to read/write separate fields and have an easier to parse format.

This, of course, assumes that your input doesn’t include tabs or newlines… because then you’re still stuck with the same problem, just with a different delimiter.

Perhaps the popularity,or lack thereof? More often than not, the bad standard wins the long term market

Can't type them on a keyboard I guess, or generally work with them in the usual text-oriented tools? Part of the appeal of CSV is you can just open it up in Notepad or something if you need to. Maybe that's more a critique of text tools than it is of ASCII record separator characters.

If there were visible well known characters that could be printed for those and keys on a keyboard for inputting them we would probably have RSV files. Because they are buried down in the nonprintable section of the ASCII chart they are a pain for people to deal with. All it would have taken is one more key on the keyboard, maybe splitting the tab key in half.

> If there were visible well known characters that could be printed...

...There would be datasets that include those characters, and so they wouldn't be as useful for record separators. Look into your heart and know it to be true.

I wouldn't feel too bad about blindly scrubbing those characters out of inputs unlike commas, tabs, and quotes.

I remembered seeing a comment like this before, and...

comment: https://news.ycombinator.com/item?id=26305052

comment: https://news.ycombinator.com/item?id=39679662

"ASCII Delimited Text – Not CSV or Tab Delimited Text" post [2014]: https://news.ycombinator.com/item?id=7474600

same post [2024]: https://news.ycombinator.com/item?id=42100499

comment: https://news.ycombinator.com/item?id=15440801

(...and many more.) "This comes up every single time someone mentions CSV. Without fail." - top reply from burntsushi in that last link, and it remains as true today as in 2017 :D

You're not wrong though, we just need some major text editor to get the ball rolling and start making some attempts to understand these characters, and the rest will follow suit. We're kinda stuck at a local optimum which is clearly not ideal but also not troublesome enough to easily drum up wide support for ADSV (ASCII Delimiter Separated Values).

Hahah, I came here to make the comment about ASCII's control characters, so I'm glad someone else beat me to it, and also that someone further pointed out that this topic comes up every time someone mentions CSV!

>we just need some major text editor to get the ball rolling and start making some attempts to understand these characters

Many text editors offer extensions APIs, including Vim, Emacs, Notepad++. But the ideal behavior would be to auto-align record separators and treat unit separators as a special kind of newline. That would allow the file to actually look like a table within the text editor. Input record separator as shift+space and unit separator as shift+enter.

I think it would be enough for:

1. the field separator to be shown as a special character

2. the row separator to be (optionally) be interpreted as a linefeed

IIRC 1) is true for Notepad++, but not 2).

Excel needs to default its export to this. Unfortunately excel is proprietary software and therefore fucked.

But what if one of your columns contains arbitrary binary data?

You'd likely need to uuencode it or similar as CSV isn't designed for binary data.

> I don't get how this is not widely used as standard.

It requires bespoke tools for edition, and while CSV is absolute garbage it can be ingested and produced by most spreadsheet software, as well as databases.

I was really excited when I learned of these characters, but ultimately if it’s in ASCII then it’s in-band and will eventually require escaping leading to the same problem.

Reminds me of a fatal flaw of yaml. Turns out truncating a yaml file doesn't make it invalid. Which can lead to some rather non-obvious failures.

What is the failure mode where a yaml file gets truncated? They are normally config files in Git. Or uploaded to S3 or Kubernetes etc.

CSV has the same failure mode. As does HTML. (But not XML)

Bad merges.

I couldn't find the story on it, but there was an instance of a config for some major service getting truncated, but since it was yaml it was more difficult to figure out that that was what happened. I think in AWS, but I can't find the story, so can't really remember.

And fully fair that you can have similar issues in other formats. I think the complaint here was that it was a bit harder, specifically because it did not trip up any of the loading code. With a big lesson learned that configs should probably either go pascal string style, where they have an expected number of items as the first part of the data, or xml style, where they have a closing tag.

Really, it is always amusing to find how many of the annoying parts of XML turned out to be somewhat more well thought out than people want to admit.

Same is true of CSV/TSV.

I think you are a bit more likely to notice in a CSV/TSV, as it is unlikely to truncate at a newline?

Still, fair point. And is part of why I said it is a flaw, not the flaw. Plenty of other reasons to not like YAML, to me. :D

Not if it is split at a line e.g. if the source or target can only deal with a fixed number of lines.

Right, that is what I meant about that being unlikely? Most instances of truncated files that I have seen were because of size, not lines.

Still, a fair point.

Really depends on how the CSV is generated/transferred. If the output of the faulting software is line-buffered then it's quite likely that a failure would terminate the file at a line break.

I want to push Sqlite as a data interchange format! it has the benefit of being well defined, and can store binary data, like images for product pictures inside the database. not a good idea if you're trying to serve users behind a web app, but as interchange, better than a zip file with filenames that have to be "relinked".

One very minor problem is that you max out storing blobs of 2GB(? I think, maybe 4GB). Granted few will hit this, but this limit did kill one previous data transfer idea of mine.

For context: I have a LOT of experience of interchange formats, like "full time job, every day, all day, hundreds of formats, for 20-years" experience.

Based on that experience I have come to one key, but maybe, counter-intuitive truth about interchange formats:

- Too much freedom is bad.

Why? Generating interchange data is cheaper than consuming it, because the creator only needs to consider the stuff they want to include, whereas the consumer needs to consider every single possible edge case and or scenario the format itself can support.

This is why XML is WAY more costly to ingest than CSV, because in XML someone is going to use: attributes, CDATA, namespaces, comments, different declaration, includes, et al. In CVS they're going to use rows, a format separator, and quotes (with or without escaping). That's it. That's all it supports.

Sqlite as an interchange format is a HORRIFYING suggestion, because every single feature Sqlite supports may need to be supported by consumers. Even if you curtailed Sqlite's vast feature set, you've still created something vastly more expensive to consume than XML, which itself is obnoxious.

My favorite interchange formats are, in order:

- CVS, JSON (inc. NDJSON), YAML, XML, BSON (due to type system), MessagePack, Protobuf, [Giant Gap] Sqlite, Excel (xlsx, et al)

More features mean more cost, more edge cases, more failures, more complex consumers. Keep in mind, this is ONLY about interchange formats between two parties, I have wildly different opinions about what I would use for my own application where I am only ever the creator/consumer, I actually love Sqlite for THAT.

Interesting! I've dealt with file interchange between closed source (and a couple open source) programs, but that was a while ago. I've also had to deal with csvs and xslts between SaaS vendors for import export of customer's data. I've done a bunch of reverse engineering of proprietary formats so we could import the vendor's files, which had more information than they were willing to export in an interchange format. Sometimes they're encrypted and you have to break it.

What you say is fair. Csv is underspecified though, there's no company called csv that's gonna sue for trademark enforcement, there's no official csv standard library that everyone uses. (They exist are some but there are so many naive importations because from first principles, because how hard could it be? output records and use a comma and newline (of which there are three possible options)).

How often do you deal with multiple Csv files to represent multiple tables that are actually what's used by vendors internally, vs one giant flattened Csv with hundreds of columns and lots of empty cells? I don't have your level of experience with csvs, but I've dealt with a them being a mess, where the other side implement whatever they think is reasonable given the name "comma separated values".

With sqlite, we're in the Internet age and so I presume this hypothetical developer would use the sqlite library and not implement their own library from scratch for funsies. This then leads to types, database normalization, multiple tables. I hear you that too many choices can bad, and xml is a great example of this, but sqlite isn't xml and isn't Csv.

It's hard to have this discussion in the abstract so I'll be forthcoming about where I'm coming from, which is Csv import export between vendors for stores, think like Doordash to UberEATS. the biggest problem we have is images of the items, and how to deal with that. It's an ongoing issue how to get them, but the failure mode, which does happen, is that when moving vendor, they just have to redo a lot of work that they shouldn't have to.

Ultimately the North Star I want to push towards is moving beyond csvs, because it'll let a people who currently have to hand edit the Csv so every row imports properly, not have to do that. They'd still exist, but instead have to deal with, well, what you see with XML files. which has its shortcomings, as you mention, but at least once how a vendor is using it is understood, individual records are generally understandable.

I was moved so I don't deal with import export currently, but it's because sqlite is so nice to work with on personal projects where it's appropriate that I want to push the notion of moving to sqlite over csvs.

Oh, this is interesting. Are you tying different systems together? If so, do you use some preferred intermediate format? Do you have a giant library of * -> intermediate -> * converters that you sprinkle between everything? Or maybe the intermediate format is in memory?

What about Parquet and the like?

Not the person you were replying to, but from my experience CSV is a good place to define data types coming into a system and a safe way to dump data as long as I write everything down.

So I might do things like have every step in a pipeline begin development by reading from and writing to CSV. This helps with parallel dev work and debugging, and is easy to load into any intermediate format.

> do you use some preferred intermediate format?

This is usually dictated by speed vs money calculations, weird context issues, and familiarity. I think it's useful to look at both "why isn't this a file" and "why isn't this all in memory" perspectives.

For tabular/time-series greater than 100k rows I personally feel like parquet cannot be beat. It's self-describing, strongly-typed, relatively compact, supports a bunch of io/decode skipping, and is quite fast.

Orc also looks good, but isn't well supported. I think parquet is optimal for now for most analytical use-cases that don't require human readability.

It is an interchange format, so it is inter-system by virtue of that. If I am a self-creator/consumer the format I use can be literally anything even binary memory dumps.

I love the wisdom in this comment!

I’m not sure you need to support every SQLite feature. I’m unconvinced of binary formats, but the .dump output is text and simple SQL.

> not a good idea if you're trying to serve users behind a web app

I use Sqlite for a static site! Generating those static pages out to individual pages would involve millions of individual files. So instead I serve up a sqlite database over http, and use a sqlite wasm driver [0] to load (database) pages as needed. Good indexing cuts down on the number of pages it grabs, and I can even get full text search!

Only feature I'm missing is compression, which is complicated because for popular extensions like sqlite-zstd written in Rust.

[0] https://github.com/mmomtchev/sqlite-wasm-http

I don't understand why CSV became a thing when TSV, or a format using the nowadays weird ASCII control characters like start/end of text, start of heading, horizontal/vertical tab, file/group/record/unit separator.

It seems many possible designs would've avoided the quoting chaos and made parsing sort of trivial.

Any time you have a character with a special meaning you have to handle that character turning up in the data you're encoding. It's inevitable. No matter what obscure character you choose, you'll have to deal with it

The difference is that the coma and newline characters are much more common in text than 0x1F and 0x1E, which if you restrict your data to alphanumeric characters (which you really should) will never appear anywhere else.

The characters would likely be unique, maybe even by the spec.

Even if you wanted them, we use backslashes to escape strings in most common programming languages just fine, the problem CSV is that commas aren't easy to recognize because they might be within a single or double quote string, or might just be a separator.

Can strings in CSV have newlines? I bet parsers disagree since there's no spec really.

Except we have all these low ASCII characters specifically for this purpose that don't turn up in the data at all. But there is, of course, also an escape character specifically for escaping them if necessary.

You can't type any of those on a typewriter, or see them in old or simple simple editors, or no editor like just catting to a tty.

If you say those are contrived examples that don't matter any more then you have missed the point and will probably never acknowledge the point and there is no purpose in continuing to try to communicate.

One can only ever remember and type out just so many examples, and one can always contrive some response to any single or finite number of examples, but they are actually infinite, open-ended.

Having a least common denominator that is extremely low that works in all the infinite situations you never even thought of, vs just pretty low and pretty easy to meet in most common situations, is all the difference in the world.

Even if you find a character that really is never in the data - your encoded data will contain it. And it's inevitable that someone encodes the encoded data again. Like putting CSV in a CSV value.

It's evitable by stating the number of bytes in a field and then the field. No escaping needed and faster parsing.

But not human editable/readable

I understand this argument in general. But basically everyone has some sort of spreatsheet application that can read CSV installed.

In some alternate worked where this "binary" format caught on it would be a very minor issue that it isn't human readable because everyone has a tool that is better at reading it than humans are. (See the above mentioned non-local property of quotes where you may think you are reading rows but are actually inside a single cell.)

Makes me also wonder if something like CBOR caught on early enough we would just be used to using something like `jq` to read it.

https://github.com/wader/fq is "jq for binary formats."

Exactly. "Use a delimiter that's not in the data" is not real serialisation, it's fingers-crossed-hope-for-the-best stuff.

I have in the past does data extractions from systems which really can't serialise properly, where the only option is to concat all the fields with some "unlikely" string like @#~!$ as a separator, then pick it apart later. Ugh.

> Exactly. "Use a delimiter that's not in the data" is not real serialisation, it's fingers-crossed-hope-for-the-best stuff.

It's not doing just this, you pick something that's likely not in the data, and then escape things properly. When writing strings you can write a double quote within double quotes with \", and if you mean to type the designated escape character you just write it twice, \\.

The only reason you go for something likely not in the data is to keep things short and readable, but it's not impossible to deal with.

I agree that it's best to pick "unlikely" delimiters so that you don't have to pepper your data with escape chars.

But some people (plenty in this thread) really do think "pick a delimiter that won't be in the data" - and then forget quoting and/or escaping - is a viable solution.

In TSV as commonly implemented (for example, the default output format of Postgres and MySQL), tab and newline are escaped, not quoted. This makes processing the data much easier. For example, you can skip to a certain record or field just by skipping literal newlines or tabs.

It's a lot easier to type comma than control characters and it's a lot easier to view comma than a tab (which might look like a space).

For automated serialization, plain text formats won out, because they are easy to implement a minimal working solution (both import and export) and more importantly, almost all systems agree on what plain text is.

We don't really have Apple formatted text, that will show up as binary on windows. Especially if you are just transferring id's and numbers, those will fall within ascii and that will work even if you are expecting unicode.

The ASCII control characters do not appear well or are editable in a plain text editor.

I did always use TSV and I think the original use of CSV could have used that.

But TSV would still have many issues.

What issues would TSV have? As commonly implemented (for example, the default output format of Postgres and MySQL), in TSV, tab and newline are escaped, not quoted.

I don't understand why CSV became a thing in the 70s when S-expressions existed since at least the 50s and are better in practically every way.

CSV's actual problem is that there's no single CSV, and you don't know what type you have (or even if you have single consistent type through the whole file) without trying to parse the whole file and seeing what breaks. Is there quoting? Is that quoting used consistently? Do you have five-digit ZIP codes, or have the East Coast ones been truncated to four digits because they began with zero? Spin the wheel!

https://www.ietf.org/rfc/rfc4180.txt

> So these days for serialisation of simple tabular data I prefer plain escaping, e.g. comma, newline and \ are all \-escaped. It's as easy to serialise and deserialise as CSV but without the above drawbacks.

For my own parser, I made everything `\` escaped: outside of a quote or double-quote delimited string, any character prefixed with a `\` is read verbatim. There are no special exceptions resulting in `\,` producing a comma while `\a` produces `\a`. This makes it a good rule, because it is only one rule with no exceptions.

I considered this but then went the other way - a \ before anything other than a \, newline or comma is treated as an error. This leaves room for adding features, e.g. \N to signify a SQL NULL.

Regarding quoting and escaping, there are two options that make sense to me - either use quoting, in which case quotes are self-escaped and that's that; or use escaping, in which case quotes aren't necessary at all.

A good way to parallelize CSV processing is to split datasets into multiple files, kinda like manual sharding. xan has a parallel command able to perform a wide variety of map-reduce tasks on splitted files.

https://github.com/medialab/xan

nice .. xsv is also very handy for wrangling csv files generally

xan is a maintained fork of xsv

I always treat CSVs as comma separated values with new line delimiters. If it’s a new line, it’s a new row.

Do you ever have CSV data that has newlines within a string?

I don't. If I ever have a dataset that requires newlines in a string, I use another method to store it.

I don't know why so many people think every solution needs to to be a perfect fit for every problem in order to be viable. CSV is good at certain things, so use it for those things! And for anything it's not good at, use something else!

> use something else

You don't always get to pick the format in which data is provided to you.

True, but in that case I'm not the one choosing how to store it, until I ingest the data, and then I will store it in whatever format makes sense to me.

I don't think we do? It's more that a bunch of companies already have their data in CSV format and aren't willing to invest any effort in moving to a new format. Doesn't matter how much one extolls all the benefits, they know right? They're paying someone else to deal with it.

No - that’s what I’m trying to say. If I have newlines I use something else.

Wouldn't work if csv is used as a exchange format with external companies.

Of course. I’m not saying I roll my own parser for every project that uses a CSV file, I’m just describing my criteria for using CSV vs some other format when I have the option.

Eh, all you're really saying is "I'm not using CSV. Instead I'm using my CSV." Except that's all that anybody does.

CSV can just as easily support escaping as any other format, but there is no agreement for a CSV format.

After all, a missed escape can just as easily destroy a JSON or XML structure. And parallel processing of text is already a little sketchy simply because UTF-8 exists.

How is this not true for every format that includes quote marks?

It is true for everything that uses quoting, I didn't mean to imply otherwise.

I'm not clear why quotes prevent parallel processing?

I mean, you don't usually parallelize reading a file in the first place, only processing what you've already read and parsed. So read each record in one process and then add it to a multiprocessing queue for multiple processes to handle.

And data corruption is data corruption. If a movie I'm watching has a corrupted bit I don't mind a visual glitch and I want it to keep playing. But with a CSV I want to fix the problem, not ignore a record.

Do you really have a use case where reading itself is the performance bottleneck and you need to parallelize reading by starting at different file offsets? I know that multiple processes can read faster from certain high-end SSD's than just one process, but that's a level of performance optimization that is pretty extraordinary. I'm kind of curious what it is!

Doing sequential reading into a queue for workers to read is a lot more complicated than having a file format that supports parallel reading.

And the fix to allow parallel reading is pretty trivial: escape new lines so that you can just keep reading until the first unescaped new line and start at that record.

It is particularly helpful if you are distributing work across machines, but even in the single machine case, it's simpler to tell a bunch of workers their offset/limit in a file.

The practical solution is to generate several CSV files and distribute work at the granularity of files

Sure, now you need to do this statically ahead of time.

It's not unsolvable, but now you have a more complicated system.

A better file format would not have this problem.

The fix is also trivial (escape new lines into \n or similar) would also make the files easier to view with a text editor.

But in practice, you’ll receive a bag of similar-format CSVs.

> I'm not clear why quotes prevent parallel processing?

Because of the "non-local" effect of quotes, you can't just jump into the middle of a file and start reading it, because you can't tell whether you're inside a quoted section or not. If (big if) you know something about the structure of the data, you might be able to guess. So that's why I said "tricky" instead of "impossible".

Contrast to my escaping-only strategy, where you can jump into the middle of a file and fully understand your context by looking one char on either side.

> Do you really have a use case where reading itself is the performance bottleneck and you need to parallelize reading by starting at different file offsets? I know that multiple processes can read faster from certain high-end SSD's than just one process, but that's a level of performance optimization that is pretty extraordinary. I'm kind of curious what it is!

I used to be a data analyst at a management consultancy. A very common scenario would be that I'm handed a multi-gigabyte CSV and told to "import the data". No spec, no schema, no nothing. Data loss or corruption is totally unacceptable, because we were highly risk-sensitive. So step 1 is to go through the whole thing trying to determine field types by testing them. Does column 3 always parse as a timestamp? Great, we'll call it a timestamp. That kind of thing. In that case, it's great to be able to parallelise reading.

> And data corruption is data corruption

Agreed, but I prefer data corruption which messes up one field, not data corruption which makes my importer sit there for 5 minutes thinking the whole file is a 10GB string value and then throw "EOF in quoted field".

Tab-Separated Value, as implemented by many databases, solves these problems, because tab, newline and other control characters are escaped. For example, the default text serialization format of Postgres (`COPY <table> TO '<file>'` without any options) is this way.

Reply on news.ycombinator.com