A love letter to the CSV format • Hacker News Board

Re: A love letter to the CSV format

#631

Post by eternauta3k » Thu, Mar 27, 2025, 6:35 PM UTC

Gormo wrote: post #625

imtringued wrote: post #569

Earlier quoted context omitted.

I agree and as a result I have completely abandoned CSV. I use the industry standard that everyone understands: ECMA-376, ISO/IEC 29500 aka .xlsx. Nobody has any problems producing or ingesting .xlsx files. The only real problem is the confusion between numbers and numeric text that happens when people use excel manually. For machine to machine communication .xlsx has never failed me.

Parsing Excel files in simple data interchange use cases that don't involve anyone manually using spreadsheets is an instance of unnecessary complexity. There are plenty of alternatives to CSV that remain plaintext, have much broader support, and are more rigorous than Excel in ensuring data consistency. You can use JSON, XML, ProtoBuf, among many other options.

But everyone already has a GUI installed for editing xlsx files...

Re: A love letter to the CSV format

#632

Post by mjw_byrne » Thu, Mar 27, 2025, 6:40 PM UTC

saulpw wrote: post #611

mjw_byrne wrote: post #594

Earlier quoted context omitted.

There's just no such thing as a delimiter which won't find its way into the data. Quoting and escaping really are the only robust way.

You can disallow all control characters (ASCII < 32) other than CR/LF/TAB, which is reasonable. I don't know of any data besides binary blobs which uses those. I've never heard of anyone inlining a binary file (like an image) into a "CSV" anyway.

If you disallow control characters so that you can use them as delimiters, then CSV itself becomes a "binary" data format - or to put it another way, you lose the ability to nest CSV.

It isn't good enough to say "but people don't/won't/shouldn't do that", because it will just happen regardless. I've seen nested CSV in real-life data.

Compare to the zero-terminated strings used by C, one legacy of which is that PostgreSQL doesn't quite support UTF-8 properly, because it can't handle a 0 byte in a string, because 0 is "special" in C.

Re: A love letter to the CSV format

#633

Post by arnorhs » Thu, Mar 27, 2025, 7:05 PM UTC

whizzter wrote: post #537

exogen wrote: post #480

Earlier quoted context omitted.

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

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.

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

Re: A love letter to the CSV format

#634

Post by NikkiA » Thu, Mar 27, 2025, 7:17 PM UTC

pwenzel wrote: post #557

jwr wrote: post #522

I so hate CSV. I am on the receiving end: I have to parse CSV generated by various (very expensive, very complicated) eCAD software packages. And it's often garbage. Those expensive software packages trip on things like escaping quotes. There is no way to recover a CSV line that has an unescaped double quote. I can't point to a strict spec and say "you are doing this wrong", because there is no strict spec. Then ther…

I hate CSV too. If I have to use it, I'll live with TSV or some other special-charter delimited format.

I'd much rather it be something that is neither used in normal* text/numbers, nor whitespace, thus non-printable delimiters wins for me.

* Don't mind me extending 'normal' here to include human-written numbers with thousand seperators.

Re: A love letter to the CSV format

#635

Post by saulpw » Thu, Mar 27, 2025, 7:19 PM UTC

mjw_byrne wrote: post #632

saulpw wrote: post #611

Earlier quoted context omitted.

You can disallow all control characters (ASCII < 32) other than CR/LF/TAB, which is reasonable. I don't know of any data besides binary blobs which uses those. I've never heard of anyone inlining a binary file (like an image) into a "CSV" anyway.

If you disallow control characters so that you can use them as delimiters, then CSV itself becomes a "binary" data format - or to put it another way, you lose the ability to nest CSV. It isn't good enough to say "but people don't/won't/shouldn't do that", because it will just happen regardless. I've seen nested CSV in real-life data. Compare to the zero-terminated strings used by C, one legacy of which is that Postgr…

Nested CSVs as you've seen in real-life data are a good counterexample, thanks for providing it.

Re: A love letter to the CSV format

#636

Post by SJC_Hacker » Thu, Mar 27, 2025, 7:19 PM UTC

nmz wrote: post #376

SJC_Hacker wrote: post #334

Earlier quoted context omitted.

Yeah CSV is easy to export, because its not really a file format, but more an idea. I'm not even sure there is such a thing as "invalid" CSV The following are all valid CSV, and they should all mean the same thing, depending on your point of view: 1) foo, bar, foobar 2) "foo", "bar", "foobar" 3) "foo", bar, foobar 4) foo; bar; "foobar" 5) foo bar "foobar" 5) foo bar Have fun writing that parser!

Using makes it not csv but tsv. Honestly if there is no comma to separate the values, then its not csv maybe Csv for character separate values or asv for anything separates values but you're right, this makes it hard how everyone is doing whatever. IMV supporting "" makes supporting anything else redundant.

Tell it to Microsoft

Re: A love letter to the CSV format

#637

Post by [deleted] » Thu, Mar 27, 2025, 7:21 PM UTC

PaulHoule wrote: post #565

kec wrote: post #532

Earlier quoted context omitted.

That is very much not true, Excel does type coercion, especially around things that happen to look like dates: https://www.theverge.com/2020/8/6/21355674/human-genes-renam...

Excel does that type coercion if you import from CSV. If you export pandas data to XLSX it adds proper type information and then it imports properly into Excel and you avoid those problems.

[deleted]

Re: A love letter to the CSV format

#638

Post by gthompson512 » Thu, Mar 27, 2025, 7:23 PM UTC

kragen wrote: post #610

fauigerzigerk wrote: post #474

Earlier quoted context omitted.

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

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

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

Re: A love letter to the CSV format

#639

Post by ringofchaos » Thu, Mar 27, 2025, 7:30 PM UTC

I have been just splitting my head to parse data from from a erp database to csv and then from csv to erp database again using the programming language user by erp system.

The first part of converting data to csv works fine with help of ai coding assistant.

The reverse part of csv to database is getting challenging and even claude sonnet 3.7 is not able to escape newline correctly.

I am now implementation the data format in json which is much simpler.

Re: A love letter to the CSV format

#640

Post by lelandbatey » Thu, Mar 27, 2025, 7:30 PM UTC

lgas wrote: post #182

koolba wrote: post #131

Earlier quoted context omitted.

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…

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

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.