4 ms·

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

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

wruza 3y ago
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.
joncrocks 3y ago
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`
barrkel 3y ago
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.
fragmede 3y ago
Yeah but we're not in the dark ages of computers anymore. Export to Sqlite database instead.
- barrkel 3y ago
  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?
  - fragmede 3y ago
    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.
    - rgmerk 3y ago
      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.
      - throwaway2037 3y ago
        For interchange between banks, XML is also very common, because it is usually accompanied with a schema doc.
        rgmerk 3y ago
        Yes. There’s certainly XML. There’s also some very strange hand-rolled formats that are a right pain to parse.
    - pixl97 3y ago
      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.
deleted 3y ago
[deleted]
Eddy_Viscosity2 3y ago
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.
- aragilar 3y ago
  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.
  - Eddy_Viscosity2 3y ago
    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.
    - aragilar 3y ago
      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.
gregw2 3y ago
And “do you have a header row”? And null/nil/nul/blank specification/expectation?
croes 3y ago
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.
SAI_Peregrinus 3y ago
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.

wruza 3y ago

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.

joncrocks 3y ago

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`

barrkel 3y ago

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.

fragmede 3y ago

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

barrkel 3y ago
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?
- fragmede 3y ago
  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.
  - rgmerk 3y ago
    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.
    - throwaway2037 3y ago
      For interchange between banks, XML is also very common, because it is usually accompanied with a schema doc.
      - rgmerk 3y ago
        Yes. There’s certainly XML. There’s also some very strange hand-rolled formats that are a right pain to parse.
  - pixl97 3y ago
    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.

deleted 3y ago

[deleted]

Eddy_Viscosity2 3y ago

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.

aragilar 3y ago
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.
- Eddy_Viscosity2 3y ago
  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.
  - aragilar 3y ago
    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.

gregw2 3y ago

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

croes 3y ago

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.

SAI_Peregrinus 3y ago

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.