Hacker News Reader

Comment by barrkel | Hacker News Reader

CSV is not well-defined. Data in the wild doesn't even agree that it's comma separated.

String encoding? Dates? Formatted numbers? Booleans (T/F/Y/N/etc)? Nested quotes? Nested CSV!?

How about intermediate systems that muck things up. String encoding going through a pipeline with a misconfiguration in the middle. Data with US dates pasted into UK Excel and converted back into CSV, so that the data is a mix of m/d/yy and d/m/yy depending on the magnitude of the numbers. Hand-munging of data in Excel generally, so that sometimes the data is misaligned WRT rows and columns.

I've seen things in CSV. I once wrote an expression language to help configure custom CSV import pipelines, because you'd need to iterate a predicate over the data to figure out which columns are which (the misalignment problem above).

View on HN

If the use-case has these complexities, then other formats may be better. I'll go out on a limb and say that MOST data export to csv are simple column data where it works just fine - at least that's been my experience.

The problem is is that it is simple until it's not, then you have problems. You need to validate immediately after export, and work out what happens when that fails.

I agree about validating after export, which is a good practice. But if you know your use-case to be csv-friendly, then its a nice simple long-standing almost universal format. Lots of pros with that. Using a more complex format for simple data may (or may not) save you issues with a rare edge case but could cost you in other areas. Like a non-technical manager having no idea how to look at.

CSV is fine if you control both ends (in which case it's worth asking why not use something else, but CSV is a totally valid choice). The problem is typically you don't, and what you expected to be simple ends up with lots of hacks until you realise that you want a more defined format (what that is depends on your field), which would have been easier to do if CSV wasn't there already.

Intermediate systems like Excel will break anything, they aren’t constrained to CSV. Excel screws up at the level of a cell value, not at the file format.

On the topic of nested CSV, three approaches:

- treat it as a join, and unroll by duplicating non-nested CSV data in separate rows for every element in the nested CSV

- treat it as a projection, have an extraction operator to project the cells you want

- treat it as text substitution problem; I've seen CSV files where every line of CSV was quoted like it was a single cell in a larger CSV row

You get nested CSV because upstream systems are often master/detail or XML but need to use CSV because everybody understands CSV because it's such a simple file format. Good stuff.

Seems more like a problem of the exporting and importing software and not the format.

CSV gives you the freedom of choice how to write the data.

You need the french data format? You can write the french date format. You need # as separator? You can use # as separator.

Sure you can't read any file without knowing it's specification but that's not the usecase of CSV.

CSV is for data transfer between systems which know each other.

Indeed. It's like 'text file' - there are many ways to encode and decode these.

Add another munging the to list, ids that 'look like numbers' e.g. `0002345` will get converted to `2,345`. Better be sure to pre-pend ' i.e. `'0002345`

Most of the problems come from treating CSV fields as something other than US-ASCII text strings. They're not dates, they're not numbers, they're just freeform text. If you want fancy-pants features like "numbers" or the ability to represent all common words in use in the US (e.g. the California town Rancho Peñasquitos) and aren't a masochist, you don't want CSV.

And “do you have a header row”? And null/nil/nul/blank specification/expectation?

Yeah but we're not in the dark ages of computers anymore. Export to Sqlite database instead.

Sure, you tell the finance industry that. They have systems, the systems already produce CSV. They can sign a contract worth multiples of your salary if you can consume it. Do you want the money or not?

In case it wasn't clear, I want software engineers in the finance industry to implement sqlite import and export in their various pieces of software, not too give up on lucrative, existing contracts, obviously.

The inertia around CSV in the finance industry is incredibly strong, and let’s just say some of the biggest industry players aren’t exactly reknowned for their cutting-edge practices when it comes to data interchange formats.

For interchange between banks, XML is also very common, because it is usually accompanied with a schema doc.

Yes.

There’s certainly XML. There’s also some very strange hand-rolled formats that are a right pain to parse.

The person in the fintec industry rarely decies "oh, I'm going to add this format no one is asking for". They'll get a specification requiring csv and start implementing it. Get fired halfway though, then someone on the other side of the planet will implement it incorrectly. The first 3 revisions won't meet the customers needs, but the customer can't move away anyway.

Specifically, people in the FinTech industry should start asking for sqlite as an export format.

Reply on news.ycombinator.com