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

521–530 of 711 posts

Re: A love letter to the CSV format

#521

Post by thiht »

kevincox wrote: post #515
thiht wrote: post #475

Earlier quoted context omitted.

They're wrong. From ECMA-404[1] in section 6: > The JSON syntax does not impose any restrictions on the strings used as names, does not require that name strings be unique, and does not assign any significance to the ordering of name/value pairs. That IS unambiguous. And for more justification: > Meaningful data interchange requires agreement between a producer and consumer on the semantics attached to a particular u…

> and does not assign any significance to the ordering of name/value pairs. I think this is outdated? I believe that the order is preserved when parsing into a JavaScript Object. (Yes, Objects have a well-defined key order. Please don't actually rely on this...)

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

Re: A love letter to the CSV format

#522

Post by jwr »

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 there are the TSV and semicolon-Separated V variants.

Did I mention that field quoting was optional?

And then there are banks, which take this to another level. My bank (mBank), which is known for levels of programmer incompetence never seen before (just try the mobile app) generates CSVs that are supposed to "look" like paper documents. So, the first 10 or so rows will be a "letterhead", with addresses and stuff in various random columns. Then there will be your data, but they will format currency values as prettified strings, for example "34 593,12 USD", instead of producing one column with a number and another with currency.

Re: A love letter to the CSV format

#523

Post by 0xbadcafebee »

I love CSV when it's only me creating/using the CSV. It's a very useful spreadsheet/table interchange format.

But god help you if you have to accept CSVs from random people/places, or there's even minor corruption. Now you need an ELT pipeline and manual fix-ups. A real standard is way better for working with disparate groups.

Re: A love letter to the CSV format

#524

Post by cogman10 »

IanCal wrote: post #505
dspillett wrote: post #497

Earlier quoted context omitted.

WRT JSON: > Headers are in each line This might be my old “space and network cost savings” reflex, which is a lot less necessary these days, kicking in, but the feels inefficient. It also gives rise to not knowing the whole schema until you read the whole dataset (which might be multiple files), unless some form of external schema definition is provided. Having said that, I accept that JSON has advantages over CSV, e…

Yes, it's not great . Space is annoying, though compression pretty much removes that as a concern (zstd is good for this, you can even have a custom dictionary). And yes, missing keys is annoying. JSONL is handy, JSON that's in the form {data: [...hundred megs of lines]} is annoying for various parsers. I'm quite a fan of parquet, but never expect to receive that from a client (alas).

Parquet should get the praise. It's simply awesome.

It's what I'd pick for tabular data exchange.

A recent problem I solved with it and duckdb allowed me to query and share a 3M record dataset. The size? 50M. And my queries all ran subsecond. You just aren't going to get that sort of compression and query-ability with a csv.

Re: A love letter to the CSV format

#525

Post by 0xbadcafebee »

jellyfishbeaver wrote: post #495

I work as a data engineer in the financial services industry, and I am still amazed that CSV remains the preferred delivery format for many of our customers. We're talking datasets that cost hundreds of thousands of dollar to subscribe to. "You have a REST API? Parquet format available? Delivery via S3? Databricks, you say? No thanks, please send us daily files in zipped CSV format on FTP."

> REST API

Requires a programmer

> Parquet format

Requires a data engineer

> S3

Requires AWS credentials (api access token and secret key? iam user console login? sso?), AWS SDK, manual text file configuration, custom tooling, etc. I guess with Cyberduck it's easier, but still...

> Databricks

I've never used it but I'm gonna say it's just as proprietary as AWS/S3 but worse.

Anybody with Windows XP can download, extract, and view a zipped CSV file over FTP, with just what comes with Windows. It's familiar, user-friendly, simple to use, portable to any system, compatible with any program. As an almost-normal human being, this is what I want out of computers. Yes the data you have is valuable; why does that mean it should be a pain in the ass?

Re: A love letter to the CSV format

#526

Post by PaulHoule »

jcattle wrote: post #429
PaulHoule wrote: post #309

I wish this was a joke. I'm always trying to convince data scientists with a foot in the open source world that their life will be so much better if they use parquet or Stata or Excel or any other kind of file but CSV. On top of all the problems people mention here involving the precise definition of the format and quoting, it's outright shocking how long it takes to parse ASCII numbers into floating point. One thing…

What advantages does excel give you over CSV?

Accurate data typing (never confuse a string with a number)

Maybe be circular but: always loads correctly into Excel, if you want to load into a spreadsheet you can add text formatting and even formulas, checkboxes and stuff which can be a lot of fun.

Re: A love letter to the CSV format

#527

Post by kec »

koolba wrote: post #63
mjw_byrne wrote: post #14

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

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…

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.

Re: A love letter to the CSV format

#528

Post by 0xbadcafebee »

munchler wrote: post #203
relistan wrote: post #198

CSV is bad. Furthermore it’s unnecessary. ASCII has field and record separator characters that were for this purpose.

That would be great if keyboards had keys for those characters and there was a common way to display them on a screen, but they don't and there isn't.

That's a feature, not a bug

Re: A love letter to the CSV format

#529

Post by mikepurvis »

immibis wrote: post #485
mikepurvis wrote: post #47

Earlier quoted context omitted.

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…

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, }"}

Re: A love letter to the CSV format

#530

Post by kevincox »

thiht wrote: post #521
kevincox wrote: post #515

Earlier quoted context omitted.

> and does not assign any significance to the ordering of name/value pairs. I think this is outdated? I believe that the order is preserved when parsing into a JavaScript Object. (Yes, Objects have a well-defined key order. Please don't actually rely on this...)

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

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