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

Time to retire the CSV?

bitsondisk.com

Post reply on HN
Print view View original

221–230 of 594 posts

Re: Time to retire the CSV?

#221

Post by Macha »

Twisell wrote: post #150
dragonwriter wrote: post #139

Earlier quoted context omitted.

> If we manage to write a spec that meet this criteria we'll have a powerful standard with easy adoption. So, a binary format consisting of: (1) a text data segment (2) and end of file character (3) a second text data segment with structured metadata describing the layout of the first text data segment, which can be as simple (in terms of meaning; the structure should be more constrained for machine readability) as “…

You got my vibe Almost any current CSV parser, even the bad ones, tolerate a header line. So it should be possible to define a compact and standardized syntax that is appended before the real header of the first cell (separator,encoding,decimal separator (often disregarded by most parsers but crucial outside USA) ,quote character,escape character,etc...). Following headers would just use special notation to inform on…

So someone opens this CSV in Excel and there's garbage in A1?

Does this really count as compatible? You will get user bugs for this.

Re: Time to retire the CSV?

#222

Post by godshatter »

habitue wrote: post #7
izietto wrote: post #3

"In favour of what?", that is the matter. CSV is a format more for humans and less for machines, but that is the use case: a format that is good enough to be compiled by humans and read by machines. At the moment there aren't many alternatives.

Objectively, CSV is terrible for humans despite being a plaintext format. No one reads CSVs: they're incomprehensible since the columns are not aligned with the headings. (You might be drawing an analogy with JSON, which is often human readable because it puts the keys right there next to the values). The best that can be said for its simplicity is that it's easy to write code that can dump data out in CSV format (an…

I try to use tab-delimited files when writing out columnar text to be loaded into something else (assuming it can parse them). It's even easier to read since you don't have extraneous commas and double quotes everywhere and most viewers will line things up to some degree via the tabs. Tabs are less common in data fields (at least when compared to double quotes and commas), at least in my experience.

Re: Time to retire the CSV?

#223

Post by zepolen »

jslaby wrote: post #167
zepolen wrote: post #19

CSVs are plain text, and that's huge, plus they have a few other advantages: 1. You can read them without any software 2. Streamable row by row 3. Compress well To be honest most of the points in this article could be addressed by standardizing a method of defining a schema for the CSVs. It could even be backward compatible by appending the definition as metadata on the header column or as a separate file. One thing…

I think an ideal solution would be csv with json metadata elements as the header. If a program supports the header, then you get the metadata. If not, no big deal, you just get an ugly header.

Perhaps, though maybe something simpler and readable would be easier to get people to use

    name::string,number of legs::int,height in meters::float,date of birth::date(MM/DD/YYYY),email adress::email,website::url
    joe,2,1.76,12/12/1999,joe@joe.com,https://www.joe.com
    bob,1,1.84,12/12/1944,bob@vietnam.com,null

Re: Time to retire the CSV?

#224

Post by dragonwriter »

Macha wrote: post #221
Twisell wrote: post #150

Earlier quoted context omitted.

You got my vibe Almost any current CSV parser, even the bad ones, tolerate a header line. So it should be possible to define a compact and standardized syntax that is appended before the real header of the first cell (separator,encoding,decimal separator (often disregarded by most parsers but crucial outside USA) ,quote character,escape character,etc...). Following headers would just use special notation to inform on…

So someone opens this CSV in Excel and there's garbage in A1? Does this really count as compatible? You will get user bugs for this.

> So someone opens this CSV in Excel and there's garbage in A1?

Yeah, that's why I chose the “thing that looks like a text file—including optionally CSV—but has additional metadata after the EOF mark” approach instead of stuffing additional metadata in the CSV; there's no way to guarantee that existing implementations will safely ignore any added metadata the main CSV body. (My mechanism has some risk in that there are probably CSV readers that treat the file as a binary byte stream and use the file size rather than a text stream that ends at EOF, but I expect its far fewer than will do the wrong thing with additional metadata before the first header.

Re: Time to retire the CSV?

#225

Post by rjh29 »

There isn't enough love for TSV imo. Unlike commas, tabs rarely appear in input data, so you don't need complicated quoting or escaping rules. You just have tab and newline as special characters. Processing this data is extremely fast. Grepping for particular values is also very fast, as you can use the tabs as anchors when searching.

TSV allows you to do stuff on a single machine and GNU parallel that people would normally create a Hadoop cluster or 128GB database for.

Re: Time to retire the CSV?

#226

Post by da_chicken »

jerf wrote: post #194
derefr wrote: post #74

Earlier quoted context omitted.

You're comparing CSVs to other spreadsheet document formats. But a CSV is not a spreadsheet. A CSV is raw data. (It's data that is restricted to a shape that enables it to be easily imported into a spreadsheet—but data nevertheless.) As such, it should be compared to other data formats—e.g. YAML, JSON Lines, etc. These other data formats all win on your #2 against CSV, as CSV is actually horrible at parse-time vs. ot…

"Self-describing formats like JSON Lines are big... but when you compress them, they go back to being small." For CSV file replacements, I'd expect something like "one JSON array per line, all values must be JSON scalars". In that case, it's not much larger than a CSV, especially one using quotes already for string values. But this demonstrates the problem with JSON for CSV, I suppose. Is each line an object? Is it w…

> But this demonstrates the problem with JSON for CSV, I suppose. Is each line an object?

How is that not a problem with every data serialization format? It does me no real good if I have an XML schema and a corresponding file. If I don't know what those elements and attributes represent I'm not really any better off.

It's not like JSON or XML can meaningfully be marshaled back into objects for use generically without knowledge of what is represented. There are generic JSON and XML readers that allow you to parse the data elements sure, but so, too, do generic CSV readers like C#'s CsvHelper or Python's csv. In all cases you have to know what the object turns into in the application before the serialized data is useful.

And, yes, CSV has slightly differing formats, but so does JSON. Date formats are conventionally ISO 8601, but that's not in the spec. That's why Microsoft got away with proprietary date formats in System.Text.Json. XML isn't really any better.

Re: Time to retire the CSV?

#227

Post by btilly »

When I see someone complaining about the loss of metadata, I have to wonder if they've ever had to deal with a swamp of data from different organizations, all of which disagreed on when to use what piece of metadata in what way.

Bad CSV is a PITA, but usually you can make reasonable sense of it. Merging bad/inconsistent/conflicting metadata tends to be an open-ended nightmare with no good resolution at the end.

Re: Time to retire the CSV?

#228

Post by goatlover »

cogman10 wrote: post #140
Semaphor wrote: post #98

Earlier quoted context omitted.

Both are also not a good fit for columnar data at all.

I disagree. Json, in particular, can be nearly as compact as CSV by storing the data as an array of arrays. [[1,2,3], [4,5,6]] It's easy to make a structured data interchange format mimic and unstructured format. It's impossible to go the other way around without severe problems.

It can be, but what's ensuring that format when you read in a JSON file? JSON is one of the formats Pandas can read, but it has to be structured in a format the python library can read in as tabular data. Excel would have the same issue as would any program that is consuming tabular data. At least with CSVs, you know the data is tabular.

Re: Time to retire the CSV?

#229

Post by adgjlsfhk1 »

AtlasBarfed wrote: post #185
Sunspark wrote: post #23

I don't agree with giving up csvs until the following conditions are met: 1) A truly open format is available and accessible. Csvs are textfiles. There is no system around that cannot open a textfile. If the format is binary or requires patents or whatever, then it's a non-starter. 2) Applications have a speed increase from using csvs. To wit, I loved csvs because often they finish preparing much faster than a "forma…

There are mature CSV libraries for most major languages that handle 99% of the problems of CSV. CSV should be better standardized, but ... whatever, what should be done to "fix" CSV is to advertise the proper use of the libraries and the nontrivial aspects of a superficially trivial format. A format that is trivially useful in 99% of cases is far better than many other "worse is better" things in computing.

The problem is that even the fastest CSV readers are 10-100x slower than readers of better defined binary formats (like Arrow)

Re: Time to retire the CSV?

#230

Post by qayxc »

brushfoot wrote: post #213
qayxc wrote: post #192

Earlier quoted context omitted.

Ah yes, and then there's https://www.theverge.com/2020/8/6/21355674/human-genes-renam... Not to mention the mess that is exchanging documents between different locales. It's all sunshine and roses until you get your CSVs from an office in a different country (which happens a lot in Europe). CSV gets the job done until it doesn't.

That's not a problem inherent to CSV the format, though, any more than using Word to edit JSON means smart quotes are a problem with JSON. It's about knowing or not knowing the tools you're using. Unexpected behavior is a potential problem with any tool or format, certainly no less so with the kinds of solutions the article is proposing.

But the behaviour isn't unexpected at all, e.g. the user wanted to store a number in column 3 and that's exactly what's the tool did. The behaviour is both expected and reproducible, the issue is that it's not communicated by the format.

The problem arises from the fact that CSV doesn't contain any metadata so while the same tool reads and writes the same data, the results will be different because the format doesn't specify what a number look like.

The formats proposed in the article don't have this problem because they specify both the type and the formatting of the file content so there's no confusion.

That's the difference. JSON in particular simply cannot have this problem (though it's not a suggested solution): a number has a well-defined format and that's that. So do strings. This is not the case with CSV:

  Number,Also a number,A String
  1.0,1e15,"Hello there!"
  "1,0",1e15,"Bienvenue á la maison!"
The first row is what you'd expect from a customer in Ireland and the second row might be sent from someone in Switzerland.

Both parties agreed to use comma-separated files for data exchange and quoted strings, yet importing the first row using a French locale would fail to parse the first number and same goes for the second row and an English locale.

Neither row contains unexpected or malformed data from the respective user's POV. And that's the problem with CSV.

Post reply on HN