10 ms·

This article seems written by someone who never had to work with diverse data pipelines. I work with large volumes of data from many different sources. I’m luc

This article seems written by someone who never had to work with diverse data pipelines. I work with large volumes of data from many different sources. I’m lucky to get them to send csv. Of course there are better formats, but all these sources aren’t able to agree on some successful format. Csv that’s zipped is producible and readable by everyone. And that makes is more efficient. I’ve been reading these “everyone is stupid, why don’t they just do the simple, right thing and I don’t understand the real reason for success” articles for so long it just makes me think the author doesn’t have a mentor or an editor with deep experience. It’s like arguing how much mp3 sucks and how we should all just use flac. The author means well, I’m sure. Maybe his next article will be about how airlines should speak Esperanto because English is such a flawed language. That’s a clever and unique observation.

gerdesj 3y ago
... And what's more, you'll be an Engineer my son.
hn_throwaway_99 3y ago
Totally agree. His arguments are basically "performance!" (which is honestly not important to 99% of CSV export users) and "It's underderspecified!" And while I can agree with the second, at least partly, in the real world the spec is essentially "Can you import it to Excel?". I'm amazed at how much programmers can discount "It already works pretty much everywhere" for the sake of more esoteric improvements. All that said (and perhaps counter to what I said), I do hope "Unicode Separated Values" takes off. It's essentially just a slight tweak to CSV where the delimiters are special unicode characters, so you don't have to have complicated quoting/escaping logic, and it also supports multiple sheets (i.e. a workbook) in a single file.
- ryncewynd 3y ago
  Are there unicode characters specifically for delimiters? If Excel had a standardised "Save as USV" option it would solve so many issues for me. I get so many broken CSVs from third-parties
  - bhaney 3y ago
    Hell, there's ASCII characters specifically for delimiters. 0x1C to 0x1F are respectively defined as file, group, record, and unit separators. Unicode naturally inherits them all.
    - smcin 3y ago
      Except nobody uses them. Another previous discussion: https://news.ycombinator.com/item?id=33935140 https://news.ycombinator.com/item?id=33935140
  - hiccuphippo 3y ago
    ASCII has characters for unit, record, group and file separator. And a some days ago there was a story here about using the unicode printable representation of these for an editor friendly format. https://news.ycombinator.com/item?id=39679378 https://news.ycombinator.com/item?id=39679378
  - crq-yml 3y ago
    There are characters from ASCII for delimiting records, which are underused because they cause confusion about whether they should be represented as a state change like a backspace character, or as a glyph. See also: "nobody can agree on the line ending character sequence". The USV proposal uses additional codepoints introduced in Unicode for the representation of the record delimiters, so they will always look and edit like character glyphs, and nobody is using them for some other purpose. The standardized look of these glyphs is unappealing, and they aren't easy to type, but it's fixable with a font and some editing functions. Most of the issue hinges on Excel support.
    - samatman 3y ago
      > nobody is using them for some other purpose. There's a lot of tooling which uses them for their intended purpose, which is to represent the C0 control characters in a font, so they can be printed when they appear in a document. Your editor is probably one of those. Which is why I consider USV a terrible idea. If I see ␇ in a file, I don't want to be constantly wondering if it's "mention ␇" or "use ␇ to represent \x07". That's why the control pictures block exists: to provide pictographs of invisible control characters. Not to make a cutesy hack "look! it's a picture of a control character pretending to be what it isn't!!" format.
- diogocp 3y ago
  > in the real world the spec is essentially "Can you import it to Excel?" And the answer to that is always no. You will it think it's yes because it works for you, but when you send it to someone who has a different Excel version or simply different regional settings, it won't work. The recipient will first have to figure out what dialect you used to export.
  - chaps 3y ago
    "Always no"? What. Really not sure what you mean here when you agree that the possibility of it working exists. I get the sentiment -- when I request data through FOIA, I will almost always request it as "an excel format" because I know that I'll at least be able to import it. CSV is much less of a guarantee and will have issues -- missing quotes, wrong delimiters, inconsistent column counts, things like that. So requesting "an excel format" implies "make the best effort to give me something that will load in my excel, but without asking what version of excel I have". Removes a fair amount of hassle, especially when it took months to get the data. It also means that if they fuck up the columns by doing a conversion, you have some means of telling them that the data is simply wrong, rather than the data is hard to work with. It does mean dealing with [0-9]+GB sized excel files sometimes, though. That all said, I prefer to share CSV files. Haven't had much of a problem with it and I can guarantee some consistency from my side. Like, the people I share files with aren't going to know what the hell a parquet file is. A CSV though? Just double click it if you're less technical, or open it up in less if you can use a terminal. It usually compresses well, despite what the author wrote.
    - throwaway2037 3y ago
      > when I request data through FOIA Fascinating. Can you share any details? Did you ever think to share some of your interesting finds here on HN as a submission?
      - chaps 3y ago
        Every now and then, yep :) https://mchap.io/that-time-the-city-of-seattle-accidentally-gave-me-32m-emails-for-40-dollars4997.html https://mchap.io/that-time-the-city-of-seattle-accidentally-...
- Dalewyn 3y ago
  >I do hope "Unicode Separated Values" takes off. It's essentially just a slight tweak to CSV where the delimiters are special unicode characters Commas can be typed by anyone on any keyboard and readable by anyone. Special Unicode Characters(tm) can't be typed by anyone on any keyboard and readable by noone. Convenience is a virtue.
  - hn_throwaway_99 3y ago
    I can't remember the last time I, or anyone I've ever worked with for that matter, ever typed up a CSV from scratch. The whole point of USV is that the delimiters can't normally be typed so you don't have to worry about escaping. USV supports displayable delimiters (see https://github.com/SixArm/usv https://github.com/SixArm/usv), so for the much more common case of editing an existing CSV in a text editor, you can just copy and paste.
    - Dalewyn 3y ago
      I've valued the virtue of CSVs being readable by any text editor known to man, and I've occasionally edited them by hand. The pure simplicity of reading and typing commas trumps any value provided by more esoteric configurations. As for escaping, that's for the subsequent programmers (which could also be me) to figure out. If it is me, I'll deal with it because it keeps things simple.
      - groestl 3y ago
        > I've occasionally edited them by hand. Yeah, usually when the quoting was f'up.
    - albert_e 3y ago
      Everyone of us was a beginner at some point. The first time we came across CSV format we likely typed it in notepad by hand. A lot of issues with CSVs are also sometimes troubleshooted by hand-- by manually fixing a quote or a comma. There is value is the ability to do this level of editing and troubleshooting.
- palmfacehn 3y ago
  JSON objects as a CSV field has been mostly agreeable for my usage. It would be nice if some of the spreadsheet apps displayed the object tree.
- djbusby 3y ago
  ASCII has had field and record separators since like, forever. Wish we had kept using those.
  - josefx 3y ago
    Does it have nesting operators? I want to embedd ASCII within my ASCII fields. So I can have a table within my table.
    - lolive 3y ago
      A manager: I wish I could have a CSV inside my CSV. Any sane person: NO!
      - josefx 3y ago
        base64 encoded fields it is.
        lolive 3y ago
        That was the right answer. You are hired!
      - bruce511 3y ago
        Well you might be wrong, but EDI in general and HL7 specifically allow 3 levels of "fields in fields in field". As long as your parser copes, and as long as you have appropriate structures to import into, its no big deal.
        iainmerrick 3y ago
        So now when I'm exporting data I need to know what nesting level it's going to live at so I can generate the correct separators? I really think that might be the worst idea I've heard for a while!
        3y ago
- paulddraper 3y ago
  USV has a mountain of problems. And really is in search of a problem to solve.
- chrisandchris 3y ago
  So true. Working with imports/exports in CSV from ERP software. One can't imagine how often "Oh, this import doesn't work. I'll just fix the CSV file" occurs. Try that with some compressed, "esoteric" file, or even CML and users will break it. Besides all the downsides CSV has, as soon as it's not only machine-machine communication and a human is involved, CSV is just simole enough.
- likis 3y ago
  It seems like you missed the conclusion in the article. If users want CSV exports, let them have it. If you have important data being shuffled around systems, pick something with a specification instead.
- xeromal 3y ago
  Check out Polars in python if you want some CSV performance lol. I recently got a 5 million row CSV from a 3rd party and I could manipulate columns (filtering, sorting, grouping) in actions that took less than a second. It's an incredible tool.
- medstrom 3y ago
  The IANA standard for TSV already disallows tabs inside fields, so you can skip writing any quoting logic (in principle). The MIME type is `text/tab-separated-values`. https://www.iana.org/assignments/media-types/text/tab-separated-values https://www.iana.org/assignments/media-types/text/tab-separa...
- puppymaster 3y ago
  ditto. if it fits in the ram, file types don't matter.
- deleted 3y ago
  [deleted]
msla 3y ago
> Csv that’s zipped is producible and readable by everyone. And that makes is more efficient. If only CSV were CSV, as opposed to some form that's 80-90% CSV by line count with enough oddities to really make the parser ugly and hard to read. See, the sweet spot isn't something completely unstructured, because then you feel justified in throwing up (your hands) and declaring defeat. The sweet spot is a file that's sufficiently close to being structured you can almost parse it nicely, but has enough bad lines you can't go in and fix them all by hand in a reasonable timeframe, and you can't tell upstream to get their shit in order because it's only a few lines.
- prepend 3y ago
  There’s definitely hair to deal with and it’s a little messy, but it’s never a blocker. But I’d say the error rate is actually very low, maybe .1-1% and nowhere near 10-20% of data being messed up.
  - nrdvana 3y ago
    Depends on your tools, I suppose. I'd just like to share this: https://metacpan.org/pod/Data::TableReader::Decoder::IdiotCSV https://metacpan.org/pod/Data::TableReader::Decoder::IdiotCS...
  - wolrah 3y ago
    > But I’d say the error rate is actually very low, maybe .1-1% and nowhere near 10-20% of data being messed up. The thing with CSV-related issues is it's usually not a fixed percentage but instead depends on the data. I work in the VoIP industry so I deal with the FreePBX Asterisk GUI quite often, and it uses CSV as its bulk import/export format. This mostly makes sense as the data is almost entirely (with one notable exception) simple tables that fit nicely in to rows and columns. The issue I run in to most commonly with this is that it doesn't quote numerical fields, and as a result the fields for extension numbers and voicemail PINs can be problematic when they contain one or more leading zeroes. All of the major spreadsheet software I've used defaults to dropping leading zeroes from columns they've decided contain numerical values, and this results in broken data in these cases. It's of course relatively rare for users to choose a voicemail PIN starting with zero and even more rare for extensions to be set up with a leading zero, but both happen regularly enough that I need to remember to manually configure those columns as "Text" when opening an extension export CSV. Either way, how often the problem occurs depends entirely on the data being sent through this pipeline. Most sites will never see the problem on the extension column, but one of my sites where the company liked a user's extension to be the last four of their DID when they were initially set up 20 years ago has a dozen of them in a row.
whakim 3y ago
To me this criticism feels excessive. It feels like the author is describing their frustrations with internal usage of CSVs - there's no mention of customers and non-technical stakeholders at all. I think it goes without saying that Parquet files and other non-human-readable formats are a nonstarter when working with external stakeholders and the last paragraph makes that clear - if the end-user wants CSV, give them CSV. I also think we shouldn't blindly dismiss the performance drawbacks of CSV when working with data pipelines. At even modest scales it becomes hard to work with very large CSVs because the data often doesn't fit into memory, a problem easily solved by Parquet and other formats assuming you only need a subset.
- chaps 3y ago
  Why is this getting downvoted? They're right that the criticism is pretty excessive: "Maybe his next article will be about how airlines should speak Esperanto because English is such a flawed language. That’s a clever and unique observation." Hm.
  - prepend 3y ago
    I got a little snarky but I think the analogy holds. Esperanto is a superior language to English. And English has many flaws. Theoretically it would be better to have all pilots and airports learn an efficient language. But it would be stupid and immature to seriously write a blog post about that, especially without talking about all the flaws in that plan.
    - lmm 3y ago
      > Esperanto is a superior language to English. Not really. You can say it's more regular but that's because it sees barely any actual use; if it ever gained popularity it wouldn't stay regular. (And given that pilots speak in set phrases anyway, irregularity isn't really an issue). It's not a great language by any stretch, it's an awkward mismash of four european languages; sure it sounds kind of nice in an Italianate way, but if that's what you want then why not just speak Italian?
gxs 3y ago
Agree. Not saying csv doesn’t have its issues, but I don’t think the author made a convincing argument. A lot of the issues the author brought up didn’t sound that bad and/or it sounds like he never looked at the source data first. If you’re doing work with large datasets, I think it’s a good practice to at least go and look at the source data briefly to see what to expect. This will give you a good idea of the format it outputs, data types, some domain context, etc. or some combination thereof and I don’t think it even takes that long. Also, it reminds me of the arguments against excel in a way. Most people know what a csv is, more or less how to open it, and don’t need too much context when discussing the file. Someone will quickly understand if you tell them the file isn’t delimited properly right away. These are pros that shouldn’t be taken for granted. Again, I’m not saying csv doesnt have issues or that there aren’t better alternatives, simply that I didn’t find this particular argument convincing.
- mrgoldenbrown 3y ago
  IME most people don't know that using Excel to open and save a csv will silently mangle data. In our application leading zeros are significant, so we constantly get screwed by people trying to do quick manual edits and breaking the data. If we're lucky it breaks so badly the import fails. It's worse when the mangling results in structurally valid but wrong data.
  - gxs 3y ago
    I think what you’re saying is accurate, but it’s also important to be practical about stuff. These are pretty well know excel limitations by now. And really, anyone using excel who is somehow not aware of that limitation is probably not someone yet experienced enough to be working on a larger and/or mission critical dataset to begin with. Are there exceptions? Sure. You might be tempted to cite the example of the incident where this happened to some biologists not too long ago, but mistakes happen. I’ve seen people make mistakes building android or iPhone using the right (TM) tools. What is the exact number of mistakes where you make the decision to jump to a new format? I’m not sure. This does happen eventually, but the author didn’t make a strong case here imo.
- 3y ago
hackerlight 3y ago
It's a premature optimization issue. If you don't have special requirements like IO throughput, or mission critical data accuracy guarantees, be biased towards picking the format that anyone can easily open in a spreadsheet.
- eviks 3y ago
  You can open out easily, but just as easily it can be wrong. So with this bias you'd still not export csv, you'd use xls
eviks 3y ago
> all these sources aren’t able to agree on some successful format. But the same is true for csv, and they are not readable by everyone since you don't always know how to read them, there is not enough info for that Also it's not a good reflection on "deep experience" if it leads to reflexive defense of common stupid things people do with wrong analogies (e.g, flac is less efficient, so more like csv)
- prepend 3y ago
  In my experience csv has the fewest problems. Not that it has zero problems.
lobochrome 3y ago
For me, the giveaway was: "You give up human readable files,..." I was genuinely interested in some alternative suggestions - but the human readableness of csv is what makes it so sticky imo.
tdudhhu 3y ago
"Of course there are better formats, but all these sources aren’t able to agree on some successful format." It's the same with csv. They come in all kinds of formats because nobody agreed on the standard. Comma separated, semicolon separated, pipe separated, escaped, not escaped. Everytime I have to deal with csv I first have to figure out how to parse it in code. So I think the author is right, we must agree on a better format because that is what friends do. You are also right because it's an illusion to think that this is going to change anythime soon. But who knows..
likis 3y ago
Did you actually read the conclusion at the end of the article? "Of course, we can't conclude that you should never export to CSV. If your users are just going to try to find the quickest way to turn your data into CSV anyway, there's no reason why you shouldn't deliver that. But it's a super fragile file format to use for anything serious like data integration between systems, so stick with something that at the very least has a schema and is more efficient to work with."
iainmerrick 3y ago
This article seems written by someone who never had to work with diverse data pipelines I think that's a little unfair, it sounds like the author does have a decent amount of experience working with real-world CSV files: I remember spending hours trying to identify an issue that caused columns to "shift" around 80% into a 40GB CSV file, and let me tell you, that just isn't fun.
iamflimflam1 3y ago
Every integration I’ve ever worked on has started off with high ideas of APIs and nice data standards. And has eventually devolved into “can we just put a CSV file on an FTP site…”. With the inevitable, “it’s not really CSV…”
Oioioioiio 3y ago
I worked plenty enough with 'diverse data pipelines' and most of them were shit due to other companies just not knowing how to work properly. CSV created tons of issues regarding encoding, value separation etc. I started talking to our customers and were able to define interfaces with better and aligned format. json made my life easier.
netcan 3y ago
So... In some senses, I think internet culture (maybe modern intellectual culture generally) gets stuck in these repetitive conversations. Reprosecuting without seemingly knowing about all the previous times the conversation has been had.
DontchaKnowit 3y ago
My entire exoerience with software development has been me bellyaching about how stupidly things are setup, why dont we do it this way instead etc... only to actually set about working on fixing these things and reqlizing its either way harder than I thought, it makes more sense than I thought, or it just plumb isnt worth the effort. The evervescent suggestions of a brighter more logical, even obvious, solutions, is often a clear indicator of domain inexperience or ignorance.
mannyv 3y ago
And it's surprisingly hard for etl departments to export to csv correctly. I mean, if they can't do csv they can't do anything more complicated for sure.
fennecbutt 3y ago
Yup, csv is always the best fallback, imo. It's: easily generated, easily parsed, human readable/editable, compact, portable, list goes on.

gerdesj 3y ago

... And what's more, you'll be an Engineer my son.

hn_throwaway_99 3y ago

Totally agree. His arguments are basically "performance!" (which is honestly not important to 99% of CSV export users) and "It's underderspecified!" And while I can agree with the second, at least partly, in the real world the spec is essentially "Can you import it to Excel?". I'm amazed at how much programmers can discount "It already works pretty much everywhere" for the sake of more esoteric improvements. All that said (and perhaps counter to what I said), I do hope "Unicode Separated Values" takes off. It's essentially just a slight tweak to CSV where the delimiters are special unicode characters, so you don't have to have complicated quoting/escaping logic, and it also supports multiple sheets (i.e. a workbook) in a single file.

ryncewynd 3y ago
Are there unicode characters specifically for delimiters? If Excel had a standardised "Save as USV" option it would solve so many issues for me. I get so many broken CSVs from third-parties
- bhaney 3y ago
  Hell, there's ASCII characters specifically for delimiters. 0x1C to 0x1F are respectively defined as file, group, record, and unit separators. Unicode naturally inherits them all.
  - smcin 3y ago
    Except nobody uses them. Another previous discussion: https://news.ycombinator.com/item?id=33935140 https://news.ycombinator.com/item?id=33935140
- hiccuphippo 3y ago
  ASCII has characters for unit, record, group and file separator. And a some days ago there was a story here about using the unicode printable representation of these for an editor friendly format. https://news.ycombinator.com/item?id=39679378 https://news.ycombinator.com/item?id=39679378
- crq-yml 3y ago
  There are characters from ASCII for delimiting records, which are underused because they cause confusion about whether they should be represented as a state change like a backspace character, or as a glyph. See also: "nobody can agree on the line ending character sequence". The USV proposal uses additional codepoints introduced in Unicode for the representation of the record delimiters, so they will always look and edit like character glyphs, and nobody is using them for some other purpose. The standardized look of these glyphs is unappealing, and they aren't easy to type, but it's fixable with a font and some editing functions. Most of the issue hinges on Excel support.
  - samatman 3y ago
    > nobody is using them for some other purpose. There's a lot of tooling which uses them for their intended purpose, which is to represent the C0 control characters in a font, so they can be printed when they appear in a document. Your editor is probably one of those. Which is why I consider USV a terrible idea. If I see ␇ in a file, I don't want to be constantly wondering if it's "mention ␇" or "use ␇ to represent \x07". That's why the control pictures block exists: to provide pictographs of invisible control characters. Not to make a cutesy hack "look! it's a picture of a control character pretending to be what it isn't!!" format.
diogocp 3y ago
> in the real world the spec is essentially "Can you import it to Excel?" And the answer to that is always no. You will it think it's yes because it works for you, but when you send it to someone who has a different Excel version or simply different regional settings, it won't work. The recipient will first have to figure out what dialect you used to export.
- chaps 3y ago
  "Always no"? What. Really not sure what you mean here when you agree that the possibility of it working exists. I get the sentiment -- when I request data through FOIA, I will almost always request it as "an excel format" because I know that I'll at least be able to import it. CSV is much less of a guarantee and will have issues -- missing quotes, wrong delimiters, inconsistent column counts, things like that. So requesting "an excel format" implies "make the best effort to give me something that will load in my excel, but without asking what version of excel I have". Removes a fair amount of hassle, especially when it took months to get the data. It also means that if they fuck up the columns by doing a conversion, you have some means of telling them that the data is simply wrong, rather than the data is hard to work with. It does mean dealing with [0-9]+GB sized excel files sometimes, though. That all said, I prefer to share CSV files. Haven't had much of a problem with it and I can guarantee some consistency from my side. Like, the people I share files with aren't going to know what the hell a parquet file is. A CSV though? Just double click it if you're less technical, or open it up in less if you can use a terminal. It usually compresses well, despite what the author wrote.
  - throwaway2037 3y ago
    > when I request data through FOIA Fascinating. Can you share any details? Did you ever think to share some of your interesting finds here on HN as a submission?
    - chaps 3y ago
      Every now and then, yep :) https://mchap.io/that-time-the-city-of-seattle-accidentally-gave-me-32m-emails-for-40-dollars4997.html https://mchap.io/that-time-the-city-of-seattle-accidentally-...
Dalewyn 3y ago
>I do hope "Unicode Separated Values" takes off. It's essentially just a slight tweak to CSV where the delimiters are special unicode characters Commas can be typed by anyone on any keyboard and readable by anyone. Special Unicode Characters(tm) can't be typed by anyone on any keyboard and readable by noone. Convenience is a virtue.
- hn_throwaway_99 3y ago
  I can't remember the last time I, or anyone I've ever worked with for that matter, ever typed up a CSV from scratch. The whole point of USV is that the delimiters can't normally be typed so you don't have to worry about escaping. USV supports displayable delimiters (see https://github.com/SixArm/usv https://github.com/SixArm/usv), so for the much more common case of editing an existing CSV in a text editor, you can just copy and paste.
  - Dalewyn 3y ago
    I've valued the virtue of CSVs being readable by any text editor known to man, and I've occasionally edited them by hand. The pure simplicity of reading and typing commas trumps any value provided by more esoteric configurations. As for escaping, that's for the subsequent programmers (which could also be me) to figure out. If it is me, I'll deal with it because it keeps things simple.
    - groestl 3y ago
      > I've occasionally edited them by hand. Yeah, usually when the quoting was f'up.
  - albert_e 3y ago
    Everyone of us was a beginner at some point. The first time we came across CSV format we likely typed it in notepad by hand. A lot of issues with CSVs are also sometimes troubleshooted by hand-- by manually fixing a quote or a comma. There is value is the ability to do this level of editing and troubleshooting.
palmfacehn 3y ago
JSON objects as a CSV field has been mostly agreeable for my usage. It would be nice if some of the spreadsheet apps displayed the object tree.
djbusby 3y ago
ASCII has had field and record separators since like, forever. Wish we had kept using those.
- josefx 3y ago
  Does it have nesting operators? I want to embedd ASCII within my ASCII fields. So I can have a table within my table.
  - lolive 3y ago
    A manager: I wish I could have a CSV inside my CSV. Any sane person: NO!
    - josefx 3y ago
      base64 encoded fields it is.
      - lolive 3y ago
        That was the right answer. You are hired!
    - bruce511 3y ago
      Well you might be wrong, but EDI in general and HL7 specifically allow 3 levels of "fields in fields in field". As long as your parser copes, and as long as you have appropriate structures to import into, its no big deal.
      - iainmerrick 3y ago
        So now when I'm exporting data I need to know what nesting level it's going to live at so I can generate the correct separators? I really think that might be the worst idea I've heard for a while!
        3y ago
paulddraper 3y ago
USV has a mountain of problems. And really is in search of a problem to solve.
chrisandchris 3y ago
So true. Working with imports/exports in CSV from ERP software. One can't imagine how often "Oh, this import doesn't work. I'll just fix the CSV file" occurs. Try that with some compressed, "esoteric" file, or even CML and users will break it. Besides all the downsides CSV has, as soon as it's not only machine-machine communication and a human is involved, CSV is just simole enough.
likis 3y ago
It seems like you missed the conclusion in the article. If users want CSV exports, let them have it. If you have important data being shuffled around systems, pick something with a specification instead.
xeromal 3y ago
Check out Polars in python if you want some CSV performance lol. I recently got a 5 million row CSV from a 3rd party and I could manipulate columns (filtering, sorting, grouping) in actions that took less than a second. It's an incredible tool.
medstrom 3y ago
The IANA standard for TSV already disallows tabs inside fields, so you can skip writing any quoting logic (in principle). The MIME type is `text/tab-separated-values`. https://www.iana.org/assignments/media-types/text/tab-separated-values https://www.iana.org/assignments/media-types/text/tab-separa...
puppymaster 3y ago
ditto. if it fits in the ram, file types don't matter.
deleted 3y ago
[deleted]

msla 3y ago

> Csv that’s zipped is producible and readable by everyone. And that makes is more efficient. If only CSV were CSV, as opposed to some form that's 80-90% CSV by line count with enough oddities to really make the parser ugly and hard to read. See, the sweet spot isn't something completely unstructured, because then you feel justified in throwing up (your hands) and declaring defeat. The sweet spot is a file that's sufficiently close to being structured you can almost parse it nicely, but has enough bad lines you can't go in and fix them all by hand in a reasonable timeframe, and you can't tell upstream to get their shit in order because it's only a few lines.

prepend 3y ago
There’s definitely hair to deal with and it’s a little messy, but it’s never a blocker. But I’d say the error rate is actually very low, maybe .1-1% and nowhere near 10-20% of data being messed up.
- nrdvana 3y ago
  Depends on your tools, I suppose. I'd just like to share this: https://metacpan.org/pod/Data::TableReader::Decoder::IdiotCSV https://metacpan.org/pod/Data::TableReader::Decoder::IdiotCS...
- wolrah 3y ago
  > But I’d say the error rate is actually very low, maybe .1-1% and nowhere near 10-20% of data being messed up. The thing with CSV-related issues is it's usually not a fixed percentage but instead depends on the data. I work in the VoIP industry so I deal with the FreePBX Asterisk GUI quite often, and it uses CSV as its bulk import/export format. This mostly makes sense as the data is almost entirely (with one notable exception) simple tables that fit nicely in to rows and columns. The issue I run in to most commonly with this is that it doesn't quote numerical fields, and as a result the fields for extension numbers and voicemail PINs can be problematic when they contain one or more leading zeroes. All of the major spreadsheet software I've used defaults to dropping leading zeroes from columns they've decided contain numerical values, and this results in broken data in these cases. It's of course relatively rare for users to choose a voicemail PIN starting with zero and even more rare for extensions to be set up with a leading zero, but both happen regularly enough that I need to remember to manually configure those columns as "Text" when opening an extension export CSV. Either way, how often the problem occurs depends entirely on the data being sent through this pipeline. Most sites will never see the problem on the extension column, but one of my sites where the company liked a user's extension to be the last four of their DID when they were initially set up 20 years ago has a dozen of them in a row.

whakim 3y ago

To me this criticism feels excessive. It feels like the author is describing their frustrations with internal usage of CSVs - there's no mention of customers and non-technical stakeholders at all. I think it goes without saying that Parquet files and other non-human-readable formats are a nonstarter when working with external stakeholders and the last paragraph makes that clear - if the end-user wants CSV, give them CSV. I also think we shouldn't blindly dismiss the performance drawbacks of CSV when working with data pipelines. At even modest scales it becomes hard to work with very large CSVs because the data often doesn't fit into memory, a problem easily solved by Parquet and other formats assuming you only need a subset.

chaps 3y ago
Why is this getting downvoted? They're right that the criticism is pretty excessive: "Maybe his next article will be about how airlines should speak Esperanto because English is such a flawed language. That’s a clever and unique observation." Hm.
- prepend 3y ago
  I got a little snarky but I think the analogy holds. Esperanto is a superior language to English. And English has many flaws. Theoretically it would be better to have all pilots and airports learn an efficient language. But it would be stupid and immature to seriously write a blog post about that, especially without talking about all the flaws in that plan.
  - lmm 3y ago
    > Esperanto is a superior language to English. Not really. You can say it's more regular but that's because it sees barely any actual use; if it ever gained popularity it wouldn't stay regular. (And given that pilots speak in set phrases anyway, irregularity isn't really an issue). It's not a great language by any stretch, it's an awkward mismash of four european languages; sure it sounds kind of nice in an Italianate way, but if that's what you want then why not just speak Italian?

gxs 3y ago

Agree. Not saying csv doesn’t have its issues, but I don’t think the author made a convincing argument. A lot of the issues the author brought up didn’t sound that bad and/or it sounds like he never looked at the source data first. If you’re doing work with large datasets, I think it’s a good practice to at least go and look at the source data briefly to see what to expect. This will give you a good idea of the format it outputs, data types, some domain context, etc. or some combination thereof and I don’t think it even takes that long. Also, it reminds me of the arguments against excel in a way. Most people know what a csv is, more or less how to open it, and don’t need too much context when discussing the file. Someone will quickly understand if you tell them the file isn’t delimited properly right away. These are pros that shouldn’t be taken for granted. Again, I’m not saying csv doesnt have issues or that there aren’t better alternatives, simply that I didn’t find this particular argument convincing.

mrgoldenbrown 3y ago
IME most people don't know that using Excel to open and save a csv will silently mangle data. In our application leading zeros are significant, so we constantly get screwed by people trying to do quick manual edits and breaking the data. If we're lucky it breaks so badly the import fails. It's worse when the mangling results in structurally valid but wrong data.
- gxs 3y ago
  I think what you’re saying is accurate, but it’s also important to be practical about stuff. These are pretty well know excel limitations by now. And really, anyone using excel who is somehow not aware of that limitation is probably not someone yet experienced enough to be working on a larger and/or mission critical dataset to begin with. Are there exceptions? Sure. You might be tempted to cite the example of the incident where this happened to some biologists not too long ago, but mistakes happen. I’ve seen people make mistakes building android or iPhone using the right (TM) tools. What is the exact number of mistakes where you make the decision to jump to a new format? I’m not sure. This does happen eventually, but the author didn’t make a strong case here imo.
3y ago

hackerlight 3y ago

It's a premature optimization issue. If you don't have special requirements like IO throughput, or mission critical data accuracy guarantees, be biased towards picking the format that anyone can easily open in a spreadsheet.

eviks 3y ago
You can open out easily, but just as easily it can be wrong. So with this bias you'd still not export csv, you'd use xls

eviks 3y ago

> all these sources aren’t able to agree on some successful format. But the same is true for csv, and they are not readable by everyone since you don't always know how to read them, there is not enough info for that Also it's not a good reflection on "deep experience" if it leads to reflexive defense of common stupid things people do with wrong analogies (e.g, flac is less efficient, so more like csv)

prepend 3y ago
In my experience csv has the fewest problems. Not that it has zero problems.

lobochrome 3y ago

For me, the giveaway was: "You give up human readable files,..." I was genuinely interested in some alternative suggestions - but the human readableness of csv is what makes it so sticky imo.

tdudhhu 3y ago

"Of course there are better formats, but all these sources aren’t able to agree on some successful format." It's the same with csv. They come in all kinds of formats because nobody agreed on the standard. Comma separated, semicolon separated, pipe separated, escaped, not escaped. Everytime I have to deal with csv I first have to figure out how to parse it in code. So I think the author is right, we must agree on a better format because that is what friends do. You are also right because it's an illusion to think that this is going to change anythime soon. But who knows..

likis 3y ago

Did you actually read the conclusion at the end of the article? "Of course, we can't conclude that you should never export to CSV. If your users are just going to try to find the quickest way to turn your data into CSV anyway, there's no reason why you shouldn't deliver that. But it's a super fragile file format to use for anything serious like data integration between systems, so stick with something that at the very least has a schema and is more efficient to work with."

iainmerrick 3y ago

This article seems written by someone who never had to work with diverse data pipelines I think that's a little unfair, it sounds like the author does have a decent amount of experience working with real-world CSV files: I remember spending hours trying to identify an issue that caused columns to "shift" around 80% into a 40GB CSV file, and let me tell you, that just isn't fun.

iamflimflam1 3y ago

Every integration I’ve ever worked on has started off with high ideas of APIs and nice data standards. And has eventually devolved into “can we just put a CSV file on an FTP site…”. With the inevitable, “it’s not really CSV…”

Oioioioiio 3y ago

I worked plenty enough with 'diverse data pipelines' and most of them were shit due to other companies just not knowing how to work properly. CSV created tons of issues regarding encoding, value separation etc. I started talking to our customers and were able to define interfaces with better and aligned format. json made my life easier.

netcan 3y ago

So... In some senses, I think internet culture (maybe modern intellectual culture generally) gets stuck in these repetitive conversations. Reprosecuting without seemingly knowing about all the previous times the conversation has been had.

DontchaKnowit 3y ago

My entire exoerience with software development has been me bellyaching about how stupidly things are setup, why dont we do it this way instead etc... only to actually set about working on fixing these things and reqlizing its either way harder than I thought, it makes more sense than I thought, or it just plumb isnt worth the effort. The evervescent suggestions of a brighter more logical, even obvious, solutions, is often a clear indicator of domain inexperience or ignorance.

mannyv 3y ago

And it's surprisingly hard for etl departments to export to csv correctly. I mean, if they can't do csv they can't do anything more complicated for sure.

fennecbutt 3y ago

Yup, csv is always the best fallback, imo. It's: easily generated, easily parsed, human readable/editable, compact, portable, list goes on.