Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

A love letter to the CSV format

github.com

Post reply on HN
Print view View original

671–680 of 711 posts

Re: A love letter to the CSV format

#671

Post by zzo38computer »

sramsay64 wrote: post #360
jll29 wrote: post #354

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 ther…

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…

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

Re: A love letter to the CSV format

#672

Post by zzo38computer »

darthwalsh wrote: post #366
qw wrote: post #255

Earlier quoted context omitted.

It's related to how older versions of Windows/Office handled Unicode in general. From what I have heard, it's still an issue with Excel, although I assume that Windows may handle plain text better these days (I haven't used it in a while) You need to write an UTF-8 BOM at the beginning (0xEF, 0xBB, 0xBF), if you want to make sure it's recognized as UTF-8.

Ugh, UTF-8 BOM. Many apps can handle UTF-8 but will try to return those bytes as content; maybe ours in 2015 too I was on the Power Query team when we were improving the encoding sniffing. An app can scan ahead i.e. 64kB, but ultimately the user needs to just say what the encoding is. All the Power Query data import dialogs should let you specify the encoding.

UTF-8 BOM is probably not a good idea for anything other than (maybe) plain text documents. For data, many (although not all) programs should not need to care about character encoding, and if they include something such as UTF-8 BOM then it will become necessary to consider the character encoding even though it shouldn't be necessary.

Re: A love letter to the CSV format

#673

Post by solidsnack9000 »

niccl wrote: post #280
dietr1ch wrote: post #251

Earlier quoted context omitted.

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

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.

Re: A love letter to the CSV format

#674

Post by solidsnack9000 »

pasc1878 wrote: post #502
dietr1ch wrote: post #251

Earlier quoted context omitted.

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.

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.

Re: A love letter to the CSV format

#675

Post by ttyprintk »

Yomguithereal wrote: post #439
HelloNurse wrote: post #436

Items #6 to #9 sound like genuine trolling to me; item #8, reversing bytes because of course no other text encodings than ASCII exist, is particularly horrible.

the reversing bytes part is encoding agnostic. you just feed the reversed bytes to the csv parser then re-reverse both the yielded rows and the cells bytes and get the original order of the bytes themselves.

Except for multibyte encodings.

Re: A love letter to the CSV format

#676

Post by yellowapple »

Hackbraten wrote: post #597
SoftTalker wrote: post #596

Earlier quoted context omitted.

Not for text data. Those values are not text characters like , or " are, and have only one meaning. It would be like arguing that 0x41 isn't always the letter "A". For binary files, yeah but you don't see CSV used there anyway.

There are several ways how a control character might inadvertently end up inside a text corpus. Given enough millions of lines, it’s bound to happen, and you absolutely don’t want it to trip up your whole export because of that one occurrence. So yes, you have to account for it in text data, too.

Sure, but that's orders of magnitude less likely to happen than a comma ending up inside a text corpus.

Re: A love letter to the CSV format

#677

Post by beautron »

dkarl wrote: post #602
beautron wrote: post #402

I also love CSV for its simplicity. A key part of that love is that it comes from the perspective of me as a programmer . Many of the criticisms of CSV I'm reading here boil down to something like: CSV has no authoritative standard, and everyone implements it differently, which makes it bad as a data interchange format. I agree with those criticisms when I imagine them from the perspective of a user who is not also a…

> I know I can quickly write a parser, not by reading some spec, but by looking at the actual CSV file This is fine if you can hand-check all the data, or if you are okay if two offsetting errors happen to corrupt a portion of the data without affecting all of it. Also I find it odd that you call it "easy" to write custom code to parse CSV files and translate between CSV formats. If somebody give you a JSON file that…

> If somebody give you a JSON file that isn't valid JSON, you tell them it isn't valid, and they say "oh, sorry" and give you a new one. That's the standard for "easy."

But it isn't that reliably easy with JSON. Sometimes I have clients give me data that I just have to work with, as-is. Maybe it was invalid JSON spat out by some programmer or tool long ago. Maybe it's just from a different department than my contact, which might delay things for days before the bureaucracy gets me a (hopefully) valid JSON.

I consider CSV's level of "easy" more reliable.

And even valid JSON can be less easy. I've had experiences where writing the high-level parsing for some JSON file, in terms of a JSON library, was less easy and more time-consuming than writing a custom CSV parser.

Subjectively, I think programming a CSV parser from basic programming primitives is just more fun and appealing than programming in terms of a JSON library or XML library. And I find the CSV code is often simpler and quicker to write.

Re: A love letter to the CSV format

#678

Post by ttyprintk »

Gormo wrote: post #629
SkyBelow wrote: post #623

Earlier quoted context omitted.

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 tim…

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-leve…

Hi,

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

Re: A love letter to the CSV format

#679

Post by thayne »

recursive wrote: post #645
IanCal wrote: post #403

Earlier quoted context omitted.

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, alm…

> 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 bazillio…

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

Re: A love letter to the CSV format

#680

Post by ttyprintk »

Someone1234 wrote: post #107
fragmede wrote: post #54

Earlier quoted context omitted.

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

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…

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.
Post reply on HN