30 ms·

A love letter to the CSV format

• Qem 2y ago
  9. Excel hates CSV It clearly means CSV must be doing something right. This is one area where LibreOffice Calc shines in comparison to Excel. Importing CSVs is much more convenient.
  • trzeci 2y ago
    I don't get it - why the world, Excel can't just open the CSV, assume from the extension it's COMMA separated value and do the rest. It does work slightly better when importing, just a little.
    • TuringTest 2y ago
      It could, but it doesn't want to. The whole MS Office dominance came into being by making sure other tools can't properly open documents created by MS tools; plus being able to open standard formats but creating small incompatibilities all around, so that you share the document in MS format instead.
    • Qem 2y ago
      Probably Microsoft treats a pure-text, simply specified, human-readable and editable spreadsheet format that fosters interoperability with competing software as an existential threat.
    • boricj 2y ago
      Your comma isn't my comma. French systems use the comma as a decimal point for numbers and we use semicolons to separate fields in CSV files.
      • mort96 2y ago
        No, french systems also use comma to separate fields in CSV files. Excel uses semicolon to separate fields in France, meaning it generates semicolon-separated files rather than comma-separated files. It's not the fault of CSV that Excel changes which file format it uses based on locale.
        • boricj 2y ago
          It's even worse than that. Office on my work computer is set to the English language, but my locale is French and so is my Windows language. It's saving semicolon-separated CSV files with the comma as a decimal point. I need to uncheck File > Option Advanced > Use system separators and set the decimal separator to a dot to get Excel to generate English-style CSV files with semicolon-separated values. I can't be bothered to find out where Microsoft moved the CSV export dialog again in the latest version of Office to get it to spit out comma-separated fields. Point is, CSV is a term for a bunch of loosely-related formats that depends among other things on the locale. In other words, it's a mess. Any sane file format either mandates a canonical textual representation for numbers independent of locale (like JSON) or uses binary (like BSON).
          • mort96 2y ago
            > It's saving semicolon-separated CSV files with the comma as a decimal point. It's not though, is what I'm saying. It's saving semicolon-separated files, not CSV files. CSV files have commas separating the values. Saying that Excel saves "semicolon-separated CSV files" is nonsensical. I can save binary data in a .txt file, that doesn't make it a "text file with binary data"; it's a binary file with a stupid name.
            • oezi 2y ago
              Sorry, but what Excel does is save to a file with a CSV extension. This format is well defined and includes ways to specify encoding and separator to be readable under different locales. This format is not comma separated values. But Excel calls it CSV. The headaches comes if people assume that a csv file must be comma separated.
              • mort96 2y ago
                I don't care what Excel calls it. As I said, if I name a file .txt but stuff it with binary data, it's not a text file.
        • orwin 2y ago
      • criddell 2y ago
        Most of the people most of the time aren't importing data from a different locale. A good assumption for defaults could be that the CSV file honors the current Windows regional settings.
        • anilakar 2y ago
          If it only was that easy. Experience has shown that the only reliable way is to run heuristics against the first few lines of the file. There are office programs that save CSV with the proper comma delimiter regardless of the locale. There are people who run non-local locales for various good reasons. There are technically savvy people who have to deal with CSV shenanigans and can and will send it with the proper comma delimiter.
  • gibibit 2y ago
    Excel won't import ISO 8601 timestamps either, which is crazy these days where it's the universal standard, and there's no excuse to use anything else. You have to replace the "T" separator with a space and also any trailing "Z" UTC suffix (and I think any other timezone/offset as well?) for Excel to be able to parse as a time/date.
    • macintux 2y ago
      Honestly I’m happier when Excel doesn’t try to convert anything. too much bugginess.
      • inglor_cz 2y ago
        Especially gene names. It was so bad that the scientific community renamed the genes in question rather than suffering from the same horror endlessly. [0] https://www.theverge.com/2020/8/6/21355674/human-genes-rename-microsoft-excel-misreading-dates https://www.theverge.com/2020/8/6/21355674/human-genes-renam...
        • gibibit 2y ago
          Just wrong!!
    • TrackerFF 2y ago
      Have you tried using the "from text/csv" importer under the data tab? Where it will import your data into a table. Because that one will import ISO 8601 timestamps just fine.
      • Suppafly 2y ago
        This, it's dumb but Excel handles csv way better if you 'import' it vs just opening it. I use excel to quickly preview csv files, but never to edit them unless I'm OK only ever using it in Excel afterwards.
        • 2y ago
  • Night_Thastus 2y ago
    I just wish Excel was a little less bad about copy-pasting CSVs as well. Every single time, without fail, it dumps them into a single column. Every single time I use "text to columns" it has insane defaults where it's fixed-width instead of delimited by, you know, commas. So I change that and finally it's fixed. Then I go do it somewhere else and have to set it up all over again. Drives me nuts. How the default behavior isn't to just put them in the way you'd expect is mind-boggling.
  • nly 2y ago
    Search and replace + text to columns after the fact works fine.
  • jaza 2y ago
    This. I got burnt by the encoding and other issues with CSV in Excel back in the day, I've only used LibreOffice Calc (on Linux) for viewing / editing CSVs for many years now, it's almost always a trouble-free experience. Fortunately I don't deal much with CSVs that Excel-wielding non-devs also need to open these days - I assume that, for most folks, that's the source of most of their CSV woes.
• Der_Einzige 2y ago
  I'm in on the "shit on microsoft for hard to use formats train" but as someone who did a LOT of .docx parsing - it turned into zen when I realized that I can just convert my docs into the easily parsed .html5 using something like pandoc. This is a good blog post and Xan is a really neat terminal tool.
  • kbouck 2y ago
    Xan looks great. Miller is another great cli tool for transforming data among csv, tsv, json and other formats. https://miller.readthedocs/ https://miller.readthedocs/
    • emmelaich 2y ago
      https://miller.readthedocs.io/ https://miller.readthedocs.io/
      • kbouck 2y ago
        trimmed the version path off and went a bit too far! thanks.
• tengwar2 2y ago
  I'm not really sure why "Excel hates CSV". I import into Excel all the time. I'm sure the functionality could be expanded, but it seems to work fine. The bit of the process I would like improved is nothing to do with CSV - it's that the exporting programs sometimes rearrange the order of fields, and you have to accommodate that in Excel after the import. But since you can have named columns in Excel (make the data in to a table), it's not a big deal.
  • recursive 2y ago
    It used to silently transform data on import. It used to silently drop columns. That's it, but it's really bad.
    • Suppafly 2y ago
      It's really bad if your header row has less columns than the data rows. You really need to do the import vs just opening the file because it's not even obvious that it's dropping data unless you know what to expect from your file.
  • roelschroeven 2y ago
    One problem is that Excel uses locale settings for parsing CSV files (and, to be fair, other text files). So if you're in e.g. Europe and you've configured Excel to use commas as decimal separators, Excel imports numbers with decimals (with points as decimal separator) as text. Or it thinks the point is a thousands separator. I forgot exactly which one of those incorrect options it chooses. I don't know what they were thinking, using a UI setting for parsing an interchange format. There's a way around, IIRC, with the "From text / csv" command, but that looses a lot of the convenience of double-clicking a CSV file in Explorer or whatever to open it in Excel.
  • Suppafly 2y ago
    Excel is halfway decent if you do the 'import' but not if you just doubleclick on them. It seems to have been programmed to intentionally do stupid stuff with them if you just doubleclick on them.
• nayuki 2y ago
  I greatly prefer TSV over CSV. https://en.wikipedia.org/wiki/Tab-separated_values https://en.wikipedia.org/wiki/Tab-separated_values
  • recursive 2y ago
    Thanks for your input.
  • adzm 2y ago
    Agreed, much easier to work with, especially if you can guarantee no embedded tabs or newlines. Otherwise you end up with backslash escaping, but that's still usually easier than quotes.
    • recursive 2y ago
      It's not really easier than CSV if you can guarantee no commas or newlines.
      • jefftk 2y ago
        Much easier to require fields not have tabs than not have commas, though.
      • solidsnack9000 2y ago
        Parsing escapes is easier than parsing quoted text with field and record separators embedded in it. Every literal newline or literal tab is a separator. One can jump to the thousandth record, for example, just by skipping lines, without looking into the contents.
  • alkh 2y ago
    The problem with using TSV is different user configuration. For ex. if I use vim then Tab might indeed be a '\t' character but in TextEdit on Mac it might be something different, so editing the file in different programs can yield different formatting. While ',' is a universal char present on all keyboards and formatted in a single way
• mjw_byrne 2y ago
  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). So these days for serialisation of simple tabular data I prefer plain escaping, e.g. comma, newline and \ are all \-escaped. It's as easy to serialise and deserialise as CSV but without the above drawbacks.
  • LPisGood 2y ago
    I always treat CSVs as comma separated values with new line delimiters. If it’s a new line, it’s a new row.
    • criddell 2y ago
      Do you ever have CSV data that has newlines within a string?
      • thesuitonym 2y ago
        I don't. If I ever have a dataset that requires newlines in a string, I use another method to store it. I don't know why so many people think every solution needs to to be a perfect fit for every problem in order to be viable. CSV is good at certain things, so use it for those things! And for anything it's not good at, use something else!
        • criddell 2y ago
          > use something else You don't always get to pick the format in which data is provided to you.
          • thesuitonym 2y ago
            True, but in that case I'm not the one choosing how to store it, until I ingest the data, and then I will store it in whatever format makes sense to me.
• polyrand 2y ago
  As someone who likes modern formats like parquet, when in doubt, I end up using CSV or JSONL (newline-delimited JSON). Mainly because they are plain-text (fast to find things with just `grep`) and can be streamed. Most features listed in the document are also shared by JSONL, which is my favourite format. It compresses really well with gzip or zstd. Compression removes some plain-text advantages, but ripgrep can search compressed files too. Otherwise, you can: zcat data.jsonl.gz | grep ... Another advantage of JSONL is that it's easier to chunk into smaller files.
  • sitkack 2y ago
    I switched to JSONL over a decade ago and I would recommend everyone else to also have switched then. This whole thread is an uninformed rehash of bad ideas.
    • theLiminator 2y ago
      I think that might make sense ingest side, but that's very expensive to deal with if you're doing anything remotely large. I think sinking into something like delta-lake or iceberg probably makes sense at scale. But yeah, I definitely agree that CSV is not great.
      • sitkack 2y ago
        JSONL as a replacement for CSV, you shouldn't be using CSV as format for long term storage or querying, it has so many downsides and nearly zero upsides. JSONL when compressed with zstd, most of "expensive if large" disappears as well. Generating and consuming JSONL can easily be in the GB/s range.
        • theLiminator 2y ago
          I mean on the querying side. Parquet's ability to skip rowgroups and even pages, and paired with iceberg or delta can make the difference between being able to run your queries at all versus needing to scale up dramatically.
• jszymborski 2y ago
  > 4. CSV is streamable This is what keeps me coming back.
  • deathanatos 2y ago
    …ndjson is streamable, too…
    • jszymborski 2y ago
      I like ndjson and jsonl just fine, but unless I need a more complicated structure, it's not worth the extra hassle of parsing JSON.
      • deathanatos 2y ago
        … what concrete language are we talking about, here? In literally any language I can think of, hassle(json) < hassle(CSV), esp. since CSV received is usually "CSV, but I've screwed it up in a specific, annoying way"
        • jszymborski 2y ago
          I'm thinking mostly of the computational complexity. But even ergonomically, in python, can read a csv like: import csv [row for row in csv.DictReader(f)] which imo is not less ergonomic than import json [json.loads(line) for line in f]
• lxe 2y ago
  TSV > CSV Way easier to parse
  • emmelaich 2y ago
    pipe (|) separated or gtfo! sqlite3 gets it right.
    • BeFlatXIII 2y ago
      What do you like so much about the pipe?
      • emmelaich 2y ago
        Visually similar to column separator in a spreadsheet. Less likely to appear in normal data. Of course you have to escape it but at the very least the data looks less noisy.
      • hajile 2y ago
        I think pipe is better too. Typical latin fonts divide characters into three heights: short like "e" or "m", tall like "l" or "P" and deep like "j" or "y". As you may notice, letters only use one or two of these three sections. Pipe is unique in that it uses all three at the same time from the very top to the very bottom. No matter what latin character you put next to it, it remains distinct. This makes the separators relatively easy to spot. Pipe is a particularly uncommon character in normal text while commas, spaces, semicolons, etc are quite common. This means you don't need to escape it very often. With an escapable pipe, an escapable newline, and unicode escapes ("\|", "\n", and "\uXXXX") you can handle pretty much everything tabular with minimal extra characters or parsing difficulty. This in turn means that you can theoretically differentiate between different basic types of data stored within each entry without too much difficulty. You could even embed JSON inside it as long as you escape pipes and newlines. "string"|123|128i8|12.3f64|false|[1,2,3,4]|{key: "val"}|2025-03-26T11:45:46−12:00 Maybe someone should type this up into a .psv file format (maybe it already exists).
        • 2y ago
• brazzy 2y ago
  Funny how the "specification holds in a tweet" yet manages to miss at least three things: 1) character encoding, 2) BOM or not, 3) header or no header.
  • nly 2y ago
    Always UTF-8. Never a BOM. Always a header
    • brazzy 2y ago
      Great if you're the one producing the CSV yourself. But if you're ingesting data from other organizations, they will, at one time or another, fuck up every single one of those (as well as the ones mentioned in TFA), no matter how clearly you specify them.
• circadian 2y ago
  Kudos for writing this, it's always worth flagging up the utility of a format that just is what it is, for the benefit of all. Commas can also create fun ambiguity, as that last sentence demonstrates. :P CSV is lovely. It isn't trying to be cool or legendary. It works for the reasons the author proposes, but isn't trying to go further. I work in a work of VERY low power devices and CSV sometimes is all you need for a good time. If it doesn't need to be complicated, it shouldn't be. There are always times when I think to myself CSV fits and that is what makes it a legend. Are those times when I want to parallelise or deal with gigs of data in one sitting. Nope. There are more complex formats for that. CSV has a place in my heart too. Thanks for reminding me of the beauty of this legendary format... :)
  • lyu07282 2y ago
    Because if there is anything we love in data exchange formats its ambiguity.
• mccanne 2y ago
  Relevant discussion from a few years back https://news.ycombinator.com/item?id=28221654 https://news.ycombinator.com/item?id=28221654
• Maro 2y ago
  I hate CSV (but not as much as XML). Most reasonably large CSV files will have issues parsing on another system.
  • lyu07282 2y ago
    It makes me a bit worried to read this thread, I would've thought its pretty common knowledge why CSV is horrible and widely agreed upon. I also have hard time taking anybody seriously who uses "specification" and "CSV" in the same sentence unironically. I suspect its 1) people who worked with legacy systems AND LIKED IT, or 2) people who never worked with legacy systems before and need to rediscover painful old lessons for themselves. It feels like trying to convince someone, why its a bad idea to store the year as a CHAR(2) in 1999, unsuccessfully.
    • boogheta 2y ago
      Maybe just read the love letter?
    • nmz 2y ago
      The thread makes me worried for a different reason, especially how many avoid reading the RFC and even saying that the RFC doesn't matter, that there's no standard for csv. maybe there should be an extension for rfc-csv.
• TrackerFF 2y ago
  Excel hates CSV only if you don't use the "From text / csv" function (under the data tab). For whatever reason, it flawlessly manages to import most CSV data using that functionality. It is the only way I can reliably import data to excel with datestamps / formats. Just drag/dropping a CSV file onto a spreadsheet, or "open with excel" sucks.
  • tacker2000 2y ago
    Yea they seem to have added this about a year ago and it works pretty well, to be fair. Now if they would just also allow pasting CSV data as “source” it would be great.
    • tgtweak 2y ago
      It's a carry over from powerbi actually, separate function entirely.
  • nh2 2y ago
    Even "From Text / CSV" sucks: It inserts an extra row at the top for its pivot table, with entries "Column1, Column2, ...". So if you export to CSV again, you now have 2 header rows. So Excel can't roundtrip CSVs, and the more often you roundtrip, the more header rows you get. You need to remember to manually delete the added header row each time, otherwise software you export back to can't read it.
    • michaelanckaert 2y ago
      That's strange, I've never seen this behaviour. Loading a CSV this way (Data -> From Text/CSV) always parses the first record as the header for me.
      • nh2 2y ago
        It does parse the first row as the header, but it inserts it as the second row, and the first row becomes another header. It creates a green/white coloured pivot table -- do you also observe that?
• mitchpatin 2y ago
  CSV still quietly powers the majority of the world’s "data plumbing." At any medium+ sized company, you’ll find huge amounts of CSVs being passed around, either stitched into ETL pipelines or sent manually between teams/departments. It’s just so damn adaptable and easy to understand.
  • deathanatos 2y ago
    > It's just so damn adaptable Like a rapidly mutating virus, yes. > and easy to understand. Gotta disagree there. For example, one of the CSVs my company shovels around is our Azure billing data. There are several columns that I just have absolutely no idea what the data in them is. There are several columns we discovered are essentially nullable¹ The Hard Way when we got a bill for which, e.g., included a charge that I guess Azure doesn't know what day that charge occurred on? (Or almost anything else about it.) (If this format is documented anywhere, well, I haven't found the docs.) Values like "1/1/25" in a "date" column. I mean, I did say it was an Azure-generated CSV, so obviously the bar wasn't exactly high, but then it never is, because anyone wanting to build something with some modicum of reliability, or discoverability, is sending data in some higher-level format, like JSON or Protobuf or almost literally anything but CSV. If I can never see the format "JSON-in-CSV-(but-we-fucked-up-the-CSV)" ever again, that would spark joy. (¹after parsing, as CSV obviously lacks "null"; usually, "" is a serialized null.)
  • testudovictoria 2y ago
    Insurance. One of the core pillars of insurance tech is the CSV format. You'll never escape it.
    • hermitcrab 2y ago
      >You'll never escape it. I see what you did there.
• primitivesuave 2y ago
  One thing that has changed the game with how I work with CSVs is ClickHouse. It is trivially easy to run a local database, import CSV files into a table, and run blazing-fast queries on it. If you leave the data there, ClickHouse will gradually optimize the compression. It's pretty magical stuff if you work in data science.
  • tgtweak 2y ago
    I feel the same way about elastic. That being said I noticed .parquet as an export format option on Shopify recently and an hopeful more providers offer the choice.
  • emmelaich 2y ago
    Simon W's https://datasette.io/ https://datasette.io/ is also excellent.
    • primitivesuave 2y ago
      Datasette is a wonderful tool that I've used before, and I have the highest admiration for its creator, but the underlying Sqlite3 database doesn't handle large datasets (i.e. hundreds of millions of rows) nearly as well as ClickHouse does. It's worth noting that I only ran into this limitation when working with huge federal campaign finance datasets [1] and trying to do some compute-intensive querying. For 99% of use cases, datasette is a similarly magical piece of software for quickly exploring some CSV files. 1. https://www.fec.gov/data/browse-data/?tab=bulk-data https://www.fec.gov/data/browse-data/?tab=bulk-data
• inglor_cz 2y ago
  "the controversial ex-post RFC 4180" I looked at the RFC. What is controversial about it?
  • tgtweak 2y ago
    You mean aside from the fact it's ex-post ...
    • inglor_cz 2y ago
      Doesn't it make sense to have a common document in the usual format (RFC) which every newbie can consult when in doubt? I much prefer that to any sort of "common institutional memory" that is nevertheless only talked about on random forums. People die, other people enter the field... hello subtle incompatibilities.
  • sakjur 2y ago
    Look at how it handles escaping of special characters and particularly new lines (RFC 4180 doesn’t guarantee that a new line is a new record) and how it’s written in 2005 yet still doesn’t handle unicode other than via a comment about ”other character sets”.
    • inglor_cz 2y ago
      "how it’s written in 2005 yet still doesn’t handle unicode other than via a throwaway comment about ”other character sets”" Yeah, you are spot on with this one (cries in Czech, which used to be encoded in several various ways).
    • Someone1234 2y ago
      > RFC 4180 doesn’t guarantee that a new line is a new record Correctly. A good parser should step through the line one column at a time, and shouldn't even consider newlines that are quoted. If you're naively splitting the entire file via newline, that isn't 4180's fault, that is your fault for not following the standard or industry norms. I'll happily concede the UNICODE point however; but I don't know if that makes it controversial.
• slg 2y ago
  >This is so simple you might even invent it yourself without knowing it already exists while learning how to program. This is a double-edged sword. The "you might even event it yourself" simplicity means that in practice lots of different people do end up just inventing their own version rather than standardizing to RFC-4180 or whatever when it comes to "quote values containing commas", values containing quotes, values containing newlines, etc. And the simplicity means these type of non-standard implementations can go completely undetectable until a problematic value happens to be used. Sometimes added complexity that forces paying more attention to standards and quickly surfaces a diversion from those standards is helpful.
• owlstuffing 2y ago
  CSV is everywhere. I use manifold-csv[1] it’s amazing. 1. https://github.com/manifold-systems/manifold/tree/master/manifold-deps-parent/manifold-csv https://github.com/manifold-systems/manifold/tree/master/man...
• hajile 2y ago
  The argument against JSON isn't very compelling. Adding a name to every field as they do in their strawman example isn't necessary. Compare this CSV field1,field2,fieldN "value (0,0)","value (0,1)","value (0,n)" "value (1,0)","value (1,1)","value (1,n)" "value (2,0)","value (2,1)","value (2,n)" To the directly-equivalent JSON [["field1","field2","fieldN"], ["value (0,0)","value (0,1)","value (0,n)"], ["value (1,0)","value (1,1)","value (1,n)"], ["value (2,0)","value (2,1)","value (2,n)"]] The JSON version is only marginally bigger (just a few brackets), but those brackets represent the ability to be either simple or complex. This matters because you wind up with terrible ad-hoc nesting in CSV ranging from entries using query string syntax to some entirely custom arrangement. person,val2,val3,valN fname=john&lname=doe&age=55&children=[jill|jim|joey],v2,v3,vN And in these cases, JSON's objects are WAY better. Because CSV is so simple, it's common for them to avoid using a parsing/encoding library. Over the years, I've run into this particular kind of issue a bunch. //outputs `val1,val2,unexpected,comma,valN` which has one too many items ["val1", "val2", "unexpected,comma", "valN"].join(',') JSON parsers will not only output the expected values every time, but your language likely uses one of the super-efficient SIMD-based parsers under the surface (probably faster than what you are doing with your custom CSV parser). Another point is standardization. Does that .csv file use commas, spaces, semicolons, pipes, etc? Does it use CR,LF, or CRLF? Does it allow escaping quotations? Does it allow quotations to escape commas? Is it utf-8, UCS-2, or something different? JSON doesn't have these issues because these are all laid out in the spec. JSON is typed. Sure, it's not a LOT of types, but 6 types is better than none. While JSON isn't perfect (I'd love to see an official updated spec with some additional features), it's generally better than CSV in my experience.
  • croes 2y ago
    > Because CSV is so simple, it's common for them to avoid using a parsing/encoding library. A but unfair to compare CSV without parser library to JSON with library.
    • hajile 2y ago
      Essentially nobody uses JSON without a library, but tons of people (maybe even most people) use CSV without a library. Part of the problem here is standards. There's a TON of encoding variations all using the same .csv extension. Making a library that can accurately detect exactly which one is correct is a big problem once you leave the handful of most common variants. If you are doing subfield encoding, you are almost certainly on your own with decoding at least part of your system. JSON has just one standard and everyone adheres to that standard which makes fast libraries possible.
• KingLancelot 2y ago
  [dead]
• boricj 2y ago
  I've recently written a library at work to run visitors on data models bound to data sets. One of these visitors is a CSV serializer that dumps a collection as a CSV document. I've just checked and strings are escaped using the same mechanism for JSON, with backslashes. I should've double-checked against RFC 4180, but thankfully that mechanism isn't currently triggered anywhere for CSV (it's used for log exportation and no data for these triggers that code path). I've also checked the code from other teams and it's just handwritten C++ stream statements inside a loop that doesn't even try to escape data. It also happens to be fine for the same reason (log exportation). I've also written serializers for JSON, BSON and YAML and they actually output spec-compliant documents, because there's only one spec to pay attention to. CSV isn't a specification, it's a bunch of loosely-related formats that look similar at a glance. There's a reason why fleshed-out CSV parsers usually have a ton of knobs to deal with all the dialects out there (and I've almost added my own by accident), that's simply not a thing for properly specified file formats.
  • nly 2y ago
    The joy of CSV is everyone knows roughly what you mean and the details can communicated succintly. The python3 csv module basically does the job.
• meemo 2y ago
  Quick question while we’re on the topic of CSV files: is there a command-line tool you’d recommend for handling CSV files that are malformed, corrupted, or use unexpected encodings? My experience with CSVs is mostly limited to personal projects, and I generally find the format very convenient. That said, I occasionally (about once a year) run into issues that are tricky to resolve.
• williamcotton 2y ago
  Essential CSV shell tools: csvtk: https://bioinf.shenwei.me/csvtk/ https://bioinf.shenwei.me/csvtk/ gawk: https://www.gnu.org/software/gawk/manual/html_node/Comma-Separated-Fields.html https://www.gnu.org/software/gawk/manual/html_node/Comma-Sep... awk: https://github.com/onetrueawk/awk?tab=readme-ov-file#csv https://github.com/onetrueawk/awk?tab=readme-ov-file#csv
  • dbro 2y ago
    Forgive me for promoting this that I wrote: csvquote: https://github.com/dbro/csvquote https://github.com/dbro/csvquote Especially for use with existing shell text processing tools, eg. cut, sort, wc, etc.
  • saint_yossarian 2y ago
    Also VisiData is an excellent TUI spreadsheet.
    • leonim 2y ago
      One of my favorite tools. However, I don’t think that Visidata is a spreadsheet, even though it looks like one and is named after one. It is more spreadsheet adjacent. It is focused on row-based and column-based operations. It doesn’t support arbitrary inter-cell operation(s), like you get in Excel-like spreadsheets. It is great for “Tidy Data’, where each row represents a coherent set of information about an object or observation. This is very much like Awk, or other pipeline tools which are also line/row oriented. For CLI tools, I’m also a big fan of Miller (https://github.com/johnkerl/miller https://github.com/johnkerl/miller) to filter/modify CSV and other data sources.
  • Yomguithereal 2y ago
    I would add xan to this list: https://github.com/medialab/xan https://github.com/medialab/xan But of course, I am partial ;)
  • jgord 2y ago
    aaand xsv : https://github.com/BurntSushi/xsv https://github.com/BurntSushi/xsv
• evnp 2y ago
  Anyone with a love of CSV hasn't been asked to deal with CSV-injection prevention in an enterprise setting, without breaking various customer data formats. There's a dearth of good resources about this around the web, this is the best I've come across: https://georgemauer.net/2017/10/07/csv-injection.html https://georgemauer.net/2017/10/07/csv-injection.html
  • Suppafly 2y ago
    That mostly breaks down to "excel is intentionally stupid with csv files if you don't use the import function to open them" along with the normal "don't trust customer input without stripping or escaping it" concerns you'd have with any input.
    • evnp 2y ago
      That was my initial reaction as well – it's a vulnerability in MS software, not ours, not our problem. Unfortunately, reality quickly came to bear: our customers and employees ubiquitously use excel and other similar spreadsheet software, which exposes us and them to risk regardless where the issue lies. We're inherently vulnerable because of the environment we're operating in, by using CSV. "don't trust customer input without stripping or escaping it" feels obvious, but I don't think it stands up to scrutiny. What exactly do you strip or escape when you're trying to prevent an unknown multitude of legacy spreadsheet clients that you don't control from mishandling data in an unknown variety of ways? How do you know you're not disrupting downstream customer data flows with your escaping? The core issue, as I understand it, stems from possible unintended formula execution – which can be prevented by prefixing certain cells with a space or some invisible character (mentioned in the linked post above). This _does_ modify customer data, but hopefully in a way that unobtrusive enough to be acceptable. All in all, it seems to be a problem without a perfect solution.
      • togakangaroo 2y ago
        Hey, I'm the author of the linked article, cool to see this is still getting passed around. Definitely agree there's no perfect solution. There's some escaping that seems to work ok, but that's going to break CSV-imports. An imperfect solutions is that applications should be designed with task-driven UIs so that they know the intended purpose of a CSV export and can make the decision to escape/not escape then. Libraries can help drive this by designing their interfaces in a similar manner. Something like `export_csv_for_eventual_import()`, `export_csv_for_spreadsheet_viewing()`. Another imperfect solution would be to ... ugh...generate exports in Excel format rather than CSV. I know, I know, but it does solve the problem. Or we could just get everyone in the world to switch to emacs csv-mode as a csv viewer. I'm down with that as well.
• notatallshaw 2y ago
  What isn't fun about CSV is quickly written parsers and serializers repeatedly making the common mistake of not handling, or badly handling, quoting. For a long time I was very wary of CSV until I learnt Python and started using it's excellent csv standard library module.
  • goatlover 2y ago
    Why not Pandas, since you're working with tabular data anyway?
    • FridgeSeal 2y ago
      Because maybe they’re not doing something column oriented? Because it has a notoriously finicky API? A dozen other reasons?
    • notatallshaw 2y ago
      When I first started, installing packages which required compiling native code on either my work Windows machine and the old Unix servers was not easy. So I largely stuck to the Python standard library where I could, and most of the operations I had at the time did not require data analysis on a server, that was mostly done in a database. Often the job was validating and transforming the data to then insert it into a database. As the Python packaging ecosystem matured and I found I could easily use Pandas everywhere it just wasn't my first thing I'd reach to. And occasionally it'd be very helpful to iterate through them with the csv module, only taking a few MBs of memory, vs. loading the entire dataset into memory with Pandas.
  • codeulike 2y ago
    Thats true, in recent years its been less of a disaster with lots of good csv libraries for various languages. In the 90s csv was a constant footgun, perhaps thats why they went crazy and came up with XML
  • Macha 2y ago
    Even widely used libraries that you might expect get it right, don't. (Like Spark, which uses Java style backslash escaping)
• uoaei 2y ago
  I think I understand the point being made, but all this reliance on text-based data means we require proper agreement on text encodings, etc. I don't think it's very useful for number-based data anyway, it's a massively bloated way to store float32s for instance and usually developers truncate the data losing about half of the precision in the process. For numerical data, nothing beats packing floats into blobs.
  • zzo38computer 2y ago
    I think binary formats have many advantages. Not only for numbers but other data as well, including data that contains text (to avoid needing escaping, etc; and to declare what character sets are being used if that is necessary), and other structures. (For some of my stuff I use a variant of DER, which adds a few new types such as key/value list type.)
• jacobsenscott 2y ago
  In abstract CSV is great. In reality, it is a nightmare not because of CSV, but because of all the legacy tools that product it in slightly different ways (different character encodings mostly - excel still produces some variant of latin1, some tools drop a BOM in your UTF8, etc). Unless you control the producer of the data you are stuck trying to infer the character encoding and transcoding to your destination, and there's no foolproof way of doing that.
• liotier 2y ago
  CSV is the bane of my existence. There is no reason to use it outside of legacy use-cases, when so many alternatives are not so brittle that they require endless defensive hacks to avoid erring as soon as exposed to the universe. CSV must die.
  • Someone1234 2y ago
    CVS isn't brittle, and I'm not sure what "hacks" you're referring to. If you or your parser just follow RFC4180 (particularly quote every field, and double quoting to cancel-quote), that will get you 90%+ compatibility.
    • liotier 2y ago
      /me laughs in legacese RFC4180 is a late attempt at CSV standardization, merely codifying a bunch of sane practices. It also provides a nice specification for generating CSV. But anyone taking care to code from a specification might as well use a proper file format. The real specification for CSV is as follows: "Valid CSV is whatever is designated as CSV by its emitter". I wish I was joking. There is literally an infinity of ways CSV can be broken. The developer will bump his head on each as he encounters them, and add a specific fix. After a while, his code will be robust against the local strains of CSV... Until the next mutation is encountered after acquiring yet another company with a bunch of ERP way past their last extended maintainance era, a history of local adaptations and CSV as a message bus.
    • bb01100100 2y ago
      Surely you’ve come across situations where line number 10,000,021 of a 60m line CSV fails to parse because there aren’t enough fields in that line of the file…? The issue is that you can’t definitively know which of the 50 fields is missing, so you have to fail the line or worse the file. In my experience (perhaps more niche than yours since you mentioned it has been your day job), the lack of fall back options makes for brittle integrations. Failing entire files due to a borked row can be expensive in terms of time. Having to ingest large CSV files from legacy systems has made me rethink the value of XML, lol. Types and schemas add complexity for sure, but you get options for dealing with variances in structure and content.
• masfuerte 2y ago
  The fact that you can parse CSV in reverse is quite cool, but you can't necessarily use it for crash recovery (as suggested) because you can't be sure that the last thing written was a complete record.
  • nly 2y ago
    Last field rather than last record. The first row will give you column count.
    • masfuerte 2y ago
      The column count doesn't help because you don't know where the last record starts because you don't know whether you're in a string or not. Unless you scan the entire file from the beginning, which defeats the object.
      • nly 2y ago
        Unless newlines in strings are escaped
• 999900000999 2y ago
  The best part about csv, anyone can write a parser in 30 minutes meaning that I can take data from the early '90s and import it into a modern web service. The worst part about CSV, anyone can ride a parser in about 30 minutes, meaning that it's very easy to get incorrect implementations, incorrect data, and other strange undefined behaviors. But to be clear json, and yaml also have issues with everyone trying to reinvent the wheel constantly. XML is rather ugly, but it seems to be the most resilient.
  • Xelbair 2y ago
    until you find someone abusing XSD schemas, or someone designing a "dynamically typed" XML... or sneaks in extra data in comments - happened to me way often than it should.
    • 999900000999 2y ago
      My condolences. Any open standard runs the risk of this happening. It's not a problem I think we'll ever solve.
      • marcosdumay 2y ago
        In principle, if you make your standard extensible enough, people should stop sneaking data into comments or strings. ... What makes the GP's problem so much more amusing. XML was the last place I'd expect to see it.
        • 999900000999 2y ago
          That's assuming they know how to use it properly. Rest has this same issue. I've seen this when trying to integrate with 3rd party apis. Status Code 200 Body: Sorry bro, no data. Even then, this is subject to debate. Should a 404 only be used when the endpoint doesn't exist ? When we have no data to return, etc.
          • Xelbair 2y ago
• wglb 2y ago
  How much easier would all of this be if whoever did CSV first had done the equivalent of "man ascii". There are all these wonderful codes there like FS, GS, RS, US that could have avoided all the hassle that quoting has brought generations of programmers and data users.
• nelblu 2y ago
  Also CSV can be queried : https://til.simonwillison.net/sqlite/one-line-csv-operations https://til.simonwillison.net/sqlite/one-line-csv-operations
• johnea 2y ago
  I have to agree. It was pretty straightforward (although tedious) to write custom CSV data exports in embedded C, with ZERO dependencies. I know, I know, only old boomers care about removing pip from their code dev process, but, I'm an old boomer, so it was a great feature for me. Straight out of libc I was able to dump data in real-time, that everyone on the latest malware OSes was able to import and analyze. CSV is awesome!
  • SJC_Hacker 2y ago
    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<TAB>bar<TAB>"foobar" 5) foo<EOL> bar<EOL foobar<EOL> Have fun writing that parser!
    • nmz 2y ago
      Using <tab> 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.
      • SJC_Hacker 2y ago
        Tell it to Microsoft
• baumschubser 2y ago
  Just last week I was bitten by a customer’s CSV that failed due to Windows‘ invisible BOM character that sometimes occurs at the beginning of unicode text files. The first column‘s title is not „First Title“ then but „&zwnbsp;First Title“. Imagine how long it takes before you catch that invisible character. Aside from that: Yes, if CSV would be a intentional, defined format, most of us would do something different here and there. But it is not, it is more of a convention that came upon us. CSV „happened“, so to say. No need to defend it more passionate than the fact that we walk on two legs. It could have been much worse and it has surprising advantages against other things that were well thought out before we did it.
  • bobmcnamara 2y ago
    I wish the UTF8BOM was standardized. Encoding guessing usually works until it doesn't.
• k_bx 2y ago
  I've recently been developing a raspberry pi based solution which works with telemetry logs. First implementation used an SQLite database (with WAL log) – only to find it corrupted after just couple of days of extensive power on/off cycles. I've since started looking at parquet files – which turned out to not be friendly to append-only operations. I've ended up implementing writing events into ipc files which then periodically get "flushed" into the parquet files. It works and it's efficient – but man is it non-trivial to implement properly! My point here is: for a regular developer – CSV (or jsonl) is still the king.
  • theoryofx 2y ago
    > First implementation used an SQLite database (with WAL log) – only to find it corrupted after just couple of days of extensive power on/off cycles. Did you try setting `PRAGMA synchronous=FULL` on your connection? This forces fsync() after writes. That should be all that's required if you're using an NVMe SSD. But I believe most microSD cards do not even respect fsync() calls properly and so there's technically no way to handle power offs safely, regardless of what software you use. I use SanDisk High Endurance SD cards because I believe (but have not fully tested) that they handle fsync() properly. But I think you have to buy "industrial" SD cards to get real power fail protection.
    • k_bx 2y ago
      Raspberry Pi uses microSD card. Just using fsync after every write would be a bit devastating, but batching might've worked ok in this case. Anyways, too late to check now.
  • theLiminator 2y ago
    > I've since started looking at parquet files – which turned out to not be friendly to append-only operations. I've ended up implementing writing events into ipc files which then periodically get "flushed" into the parquet files. It works and it's efficient – but man is it non-trivial to implement properly! I think the industry standard for supporting this is something like iceberg or delta, it's not very lightweight, but if you're doing anything non-trivial, it's the next logical move.
• BrenBarn 2y ago
  I always feel like CSV gets a bad rap. It definitely has problems if you get into corner cases but for many situations it's just fine.
• relistan 2y ago
  CSV is bad. Furthermore it’s unnecessary. ASCII has field and record separator characters that were for this purpose.
  • munchler 2y ago
    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.
    • 0xbadcafebee 2y ago
      That's a feature, not a bug
    • relistan 2y ago
      I can’t remember the last time I or anyone else I know typed a CSV file out. It’s almost universally the lowest common denominator interchange format.
• nextts 2y ago
  10. CSV doesn't need commas! Use a different separator if you need to. CSV is the Vim of formats. If you get a CSV from 1970 you can still load it.
• fbn79 2y ago
  CSV is too new and without a good standard quoting strategy. Better staying with the boring old fixed length column format :))
• merillecuz56 2y ago
  [dead]
• athenot 2y ago
  CSV is awesome for front-end webapps needing to fetch A LOT of data from a server in order to display an information-dense rendering. For that use-case, one controls both sides so the usual serialization issues aren't a problem.
• hermitcrab 2y ago
  There is a lot not to like about CSV, for all the reasons given here. The only real positive is that you can easily create, read and edit CSV in an editor. Personally I think we missed a trick by not using the ASCII US and RS characters: Columns separated by \u001F (ASCII unit separator). Rows separated by \u001E (ASCII record separator). No escaping needed. More about this at: https://successfulsoftware.net/2022/04/30/why-isnt-there-a-decent-file-format-for-tabular-data/ https://successfulsoftware.net/2022/04/30/why-isnt-there-a-d...
  • eximius 2y ago
    Welp, now I know my weekend project.
  • xnx 2y ago
    Good idea, but probably a non-starter due to no keyboard keys for those characters. Even | would've been a better character to use since it almost never appears in common data.
    • hermitcrab 2y ago
      It is probably unrealistic to expect keyboard keyboard vendors to add new keys. But editors could support adding them through keyboard shortcuts (Ctrl + something). Pipe can be useful as a field delimiter. But what do you use as the record delimiter?
• hermitcrab 2y ago
  I wrote my own CSV parser in C++. I wasn't sure what to do in some edge cases, e.g. when character 1 is space and character 2 is a quote. So I tried importing the edge case CSV into both MS Excel and Apple Numbers. They parsed it differently!
• diegolo 2y ago
  People that talk about readability: if you store using jsonl (one json per line) - you can get your csv by using the terminal command jq.
• baazaa 2y ago
  the simplicity is underappreciated because people don't realise how many dumb data engineers there are. i'm pretty sure most of them can't unpack an xml or json. people see a csv and think they can probably do it themselves, any other data format they think 'gee better buy some software with the integration for this'.
• account-5 2y ago
  I like CSV for the same reasons I like INI files. It's simple, text based, and there's no typing encoded in the format, it's just strings. You don't need a library. They're not without their drawbacks, like no official standards etc, but they do their job well. I will be bookmarking this like I have the ini critique of toml: https://github.com/madmurphy/libconfini/wiki/An-INI-critique-of-TOML https://github.com/madmurphy/libconfini/wiki/An-INI-critique... I think the first line of the toml critique applies to CSV: it's a federation of dialects.
  • deepsun 2y ago
    Similarly I had once loved the schemaless datastorages. They are so much simpler! Until I worked quite a bit with them and realized that there's always schema in the data, otherwise it's just random noise. The question is who maintains the schema, you or a dbms. Re. formats -- the usefulness comes from features (like format enforcing). E.g. you may skip .ini at all and just go with lines on text files, but somewhere you still need to convert those lines to your data, there's no way around it, the question is who's going to do that (and report sane error messages).
    • nukem222 2y ago
      Schemaless can be accomplished with well-formed formats like json, xml, yaml, toml, etc. from the producer side these are roughly equivalent interfaces. There's zero upside to using CSVs except to comfort your customer. Or maybe you have centered importing of CSVs into your actual business, in which case you should probably not exist.
  • nukem222 2y ago
    > It's simple My experience has indicated the exact opposite. CSVs are the only "structured" format nobody can claim to parse 100% (ok probably not true thinking about html etc, just take this as hyperbole.) Just use a well-specified format and save your brain-cells. Occasionally, we must work with people who can only export to csv. This does not imply csv is a reasonable way to represent data compared to other options.
• JohnMakin 2y ago
  with quick and dirty bash stuff ive written the same csv parser so many times it lives in my head and i can write it from memory. no other format is like that. trying to parse json without jq or a library is much more difficult
• cypherpunks01 2y ago
  Any recommendations for CSV editors on OSX? I was just looking around for this today. The "Numbers" app is pretty awful and I couldn't find any superb substitutes, only ones that were just OK.
  • mytec 2y ago
    I’ve been using Easy CSV Editor. I especially like getting the min/max, unique, etc values in a given column.
• Vaslo 2y ago
  So easy to get data in and out of an application, opens seamlessly in Excel or your favor DB for further inspection. The only issue is the comma rather than a less used separator like | that occasionally causes issues.
• nukem222 2y ago
  [flagged]
• amelius 2y ago
  If this was really a love letter, it would have been in CSV format.
• 486sx33 2y ago
  I love CSV for a number of reasons. Not the least of which it’s super easy to write a program (code) in C to directly output all kinds of things to CSV. You can also write simple middleware to go from just about any database or just general “thing” to CSV. Very easily. Then toss CSV into excel and do literally anything you want. It’s sort of like, the computing dream when I was growing up. +1 to ini files. I like you can mess around with them yourself in notepad. Wish there was a general outline / structure to those though.
• jgord 2y ago
  shout out to BurntSushis excellent xsv util https://github.com/BurntSushi/xsv https://github.com/BurntSushi/xsv
• ok123456 2y ago
  It's an ad hoc text format that is often abused and a last-chance format for interchange. While heuristics can frequently work at determining the structure, they can just as easily frequently fail. This is especially true when dealing with dates and times or other locale-specific formats. Then, people outright abuse it by embedding arrays or other such nonsense. You can use CSV for interchange, but a duck db import script with the schema should accompany it.
• PaulHoule 2y ago
  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 that stuck with me from grad school was that you could do a huge number of FLOPS on a matrix in the time it would take to serialize and deserialize it to aSCII.
  • jcattle 2y ago
    What advantages does excel give you over CSV?
    • PaulHoule 2y ago
      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.
      • kec 2y ago
        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-rename-microsoft-excel-misreading-dates https://www.theverge.com/2020/8/6/21355674/human-genes-renam...
        • PaulHoule 2y ago
          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 2y ago
            [deleted]
• didgetmaster 2y ago
  I have written a new database system that will convert CSV, JSON, and XML files into relational tables. On of the biggest challenges to CSV files is the lack of data types on the header line that could help determine the schema for the table. For example a file containing customer data might have a column for a Zip Code. Do you make the column type a number or a string? The first thousand rows might have just 5 digit numbers (e.g. 90210) but suddenly get to rows with the expanded format (e.g. 12345-1234) which can't be stored in an integer column.
  • fsckboy 2y ago
    csv does not stop you from making the first line be column headers, with implied data types, you just have to comma separate them!
    • didgetmaster 2y ago
      I realize that. But when reading a header you have to imply the data types which might be wrong. I always thought it would have been great if the first line read something like: name:STRING,address:STRING,zip code:INTEGER,ID:BIG_INT,...
• osigurdson 2y ago
  JSON, XML, YAML are tree describing languages while CSV defines a single table. This is why CSV still works for a lot of things (sure there is JSON lines format of course).
• achr2 2y ago
  Using ascii 'US' Unit Separator and 'RS' Record Separator characters would be a far better implementation of a CSV file.
  • metalliqaz 2y ago
    and of course you can do that if you wish, as many CSV libraries allow arbitrary separators and escapes (though they usually default to the "excel compatible" format) but at least in my case, I would not like to use those characters because they are cumbersome to work with in a text editor. I like very much to be able to type out CSV columns and rows quickly, when I need to.
    • achr2 2y ago
      It’s all a pros and cons.. the benefit of those characters are they are not used anywhere else, hence you never have to worry about escaping/quoting strings. But obviously most of my csv usage is automated in/out.
• Dwedit 2y ago
  I prefer Tab-Separated. Its problem though: No tabs allowed in your data.
• rr808 2y ago
  I wish CSV could have headers for meta data. And schemas would be awesome. And pipes as well to avoid the commas in strings problem.
• jll29 2y ago
  The post should at least mention in passing the major problem with CSV: it is a "no spec" family of de-facto formats, not a single thing (it is an example of "historically grown"). And omission of that meams I'm going to have to call this our for its bias (but then it is a love letter, and love makes blind...). Unlike XML or JSON, there isn't a document defining the grammar of well-formed or valid CSV files, and there are many flavours that are incompatible with each other in the sense that a reader for one flavour would not be suitable for reading the other and vice versa. Quoting, escaping, UTF-8 support are particular problem areas, but also that you cannot tell programmatically whether line 1 contains column header names or already data (you will have to make an educated guess but there ambiguities in it that cannot be resolved by machine). Having worked extensively with SGML for linguistic corpora, with XML for Web development and recently with JSON I would say programmatically, JSON is the most convenient to use regarding client code, but also its lack of types makes it useful less broadly than SGML, which is rightly used by e.g. airlines for technical documntation and digital humanities researchers to encode/annotate historic documents, for which it is very suitable, but programmatically puts more burden on developers. You can't have it all... XML is simpler than SGML, has perhaps the broadest scope and good software support stack (mostly FOSS), but it has been abused a lot (nod to Java coders: Eclipse, Apache UIMA), but I guess a format is not responsible for how people use or abuse it. As usual, the best developers know the pros and cons and make good-taste judgments what to use each time, but some people go ideological. (Waiting for someone to write a love letter to the infamous Windows INI file format...)
  • y42 2y ago
    why you hate csv, not the program that is not able to properly create csv?
  • golly_ned 2y ago
    It does mention this. Point 2.
  • jimbokun 2y ago
    The post does mention it, as a positive: https://github.com/medialab/xan/blob/master/docs/LOVE_LETTER.md#2-csv-is-a-collective-idea https://github.com/medialab/xan/blob/master/docs/LOVE_LETTER...
• stevage 2y ago
  They're completely skipping over the complications of header rows and front matter. "8. Reverse CSV is still valid CSV" is not true if there are header rows for instance. But really, whether or not CSV is a good format or not comes down to how much control you have over the input you'll be reading. If you have to deal with random CSV from "in the wild", it's pretty rough. If you have some sort of supplier agreement with someone that's providing the data, or you're always parsing data from the same source, it's pretty fine.
• tomrod 2y ago
  I used to prefer csv. Then I started using parquet. Never want to use sas7bdat again.
• pretoriusdre 2y ago
  CSV has caused me a lot of problems due to the weak type system. If I save a Dataframe to CSV and reload it, there is no guarantee that I'll end up with an identical dataframe. I can depend on parquet. The only real disadvantages with parquet are that they aren't human-readable or mutable, but I can live with that since I can easily load and resave them.
• deleted 2y ago
  [deleted]
• wukerplank 2y ago
  CSV is so deceptively simple that people don't care understanding it. I wasted countless hours working around services providing non-escaped data that off the shelf parsers could not parse.
• Ericson2314 2y ago
  > CSV is dynamically typed No, CSV is dependently typed. Way cooler ;) I wrote something about this https://github.com/Ericson2314/baccumulation/blob/main/database/hierarchical-csv.md https://github.com/Ericson2314/baccumulation/blob/main/datab...
• hdjrudni 2y ago
  I just wish line breaks weren't allowed to be quoted. I would have preferred \n. Now I can't read line-by-line or stream line-by-line.
• samdung 2y ago
  CSV works because CSV is understood by non technical people who have to deal with some amount of technicality. CSV is the friendship bridge that prevents technical and non technical people from going to war. I can tell an MBA guy to upload a CSV file and i'll take care of it. Imagine i tell him i need everything in a PARQUET file!!! I'm no longer a team player.
  • Foobar8568 2y ago
    Among the shit I have seen in CSV, no " for strings, including those with a return char, innovative SEP, date, numbers, no escape for " within strings, rows related to the reporting tools used to export to CSV etc
    • bell-cot 2y ago
      True. But most of those problems are pretty easy for the non-technical person to see, understand, and (often) fix. Which strengthens the "friendship bridge". (I'm assuming the technical person can easily write a basic parsing script for the CSV data - which can flag, if not fix, most of the format problems.) For a dataset of any size, my experience is that most of the time & effort goes into handling records which do not comply with the non-technical person's beliefs about their data. Which data came from (say) an old customer database - and between bugs in the db software, and abuse by frustrated, lazy, or just ill-trained CSR's, there are all sorts of "interesting" things, which need cleaning up.
  • MarceliusK 2y ago
    "Friendship bridge" is the perfect phrase
  • tucnak 2y ago
    This is so relatable to all data eng people from SWE background! Thanks
  • tim333 2y ago
    Indeed the my main use is most financial services will output your records in csv, although I mostly open that in excel which sometimes gets a bit confused.
• beautron 2y ago
  I also love CSV for its simplicity. A key part of that love is that it comes from the perspective of me as a programmer. Many of the criticisms of CSV I'm reading here boil down to something like: CSV has no authoritative standard, and everyone implements it differently, which makes it bad as a data interchange format. I agree with those criticisms when I imagine them from the perspective of a user who is not also a programmer. If this user exports a CSV from one program, and then tries to load the CSV into a different program, but it fails, then what good is CSV to them? But from the perspective of a programmer, CSV is great. If a client gives me data to load into some app I'm building for them, then I am very happy when it is in a CSV format, because I know I can quickly write a parser, not by reading some spec, but by looking at the actual CSV file. Parsing CSV is quick and fun if you only care about parsing one specific file. And that's the key: It's so quick and fun, that it enables you to just parse anew each time you have to deal with some CSV file. It just doesn't take very long to look at the file, write a row-processing loop, and debug it against the file. The beauty of CSV isn't that it's easy to write a General CSV Parser that parses every CSV file in the wild, but rather that its easy to write specific CSV parsers on the spot. Going back to our non-programmer user's problem, and revisiting it as a programmer, the situation is now different. If I, a programmer, export a CSV file from one program, and it fails to import into some other program, then as long as I have an example of the CSV format the importing program wants, I can quickly write a translator program to convert between the formats. There's something so appealing about to me about simple-to-parse-by-hand data formats. They are very empowering to a programmer.
  • MarceliusK 2y ago
    Totally agree that its biggest strength is how approachable it is for quick, ad hoc tooling. Need to convert formats? Join two datasets? Normalize a weird export? CSV gives you just enough structure to work with and not so much that it gets in your way.
  • dkarl 2y ago
    > I know I can quickly write a parser, not by reading some spec, but by looking at the actual CSV file This is fine if you can hand-check all the data, or if you are okay if two offsetting errors happen to corrupt a portion of the data without affecting all of it. Also I find it odd that you call it "easy" to write custom code to parse CSV files and translate between CSV formats. If somebody give you a JSON file that isn't valid JSON, you tell them it isn't valid, and they say "oh, sorry" and give you a new one. That's the standard for "easy." When there are many and diverse data formats that meet that standard, it seems perverse to use the word "easy" to talk about empirically discovering the quirks in various undocumented dialects and writing custom logic to accommodate them. Like, I get that a farmer a couple hundred years ago would describe plowing a field with a horse as "easy," but given the emergence of alternatives, you wouldn't use the word in that context anymore.
• realPtolemy 2y ago
  I love CSV
• MarceliusK 2y ago
  This might be the most passionate and well-argued defense of CSV I've read
• thenoblesunfish 2y ago
  All hail TSV. Like CSV, but you're probably less likely to want tabs, than commas.
• hiddew 2y ago
  I think for "untyped" files with records, using the ASCII file, (group) and record separators (hex 1C, 1D and 1E) work nicely. The only constraint is that the content cannot contain these characters, but I found that that is generally no problem in practice. Also the file is less human readable with a simple text editor. For other use cases I would use newline separated JSON. Is has most of the benefits as written in the article, except the uncompressed file size.
  • akie 2y ago
    I agree that JSONL is the spiritual successor of CSV with most of the benefits and almost none of the drawbacks. It has a downside though: wherever JSON itself is used, it tends to be a few kilobytes at least (from an API response, for example). If you collect those in a JSONL file the lines tend to get verrrry long and difficult to edit. CSV files are more compact. JSONL files are a lot easier to work with though. Less headaches.
    • k_bx 2y ago
      The drawbacks are quite substantial actually – uses much more data per record. For many cases it's a no-go.
  • taftster 2y ago
    Honestly yes. If text editors would have supported these codes from the start, we might not even have XML, JSON or similar today. If these codes weren't "binary" and all scary, we would live in much different world. I wonder how much we have been hindered ourselves by reinventing plain text human-readable formats over the years. CSV -> XML -> JSON -> YAML and that's just the top-level lineage, not counting all the branches everywhere out from these. And the unix folks will be able to name plenty of formats predating all of this.
• gpvos 2y ago
  I've said it before, CSV will still be used in 200 years. It's ugly, but it occupies an optimal niche between human readability, parsing simplicity, and universality.
  • 6510 2y ago
    With a nice ASIC parser to replace databases.
• HelloNurse 2y ago
  Items #6 to #9 sound like genuine trolling to me; item #8, reversing bytes because of course no other text encodings than ASCII exist, is particularly horrible.
  • Yomguithereal 2y ago
    the reversing bytes part is encoding agnostic. you just feed the reversed bytes to the csv parser then re-reverse both the yielded rows and the cells bytes and get the original order of the bytes themselves.
    • ttyprintk 2y ago
      Except for multibyte encodings.
• seydor 2y ago
  Just don't write that love letter in French ... or any language that uses comma for decimals
  • k_bx 2y ago
    The library quotes values like that in quotes
  • reddalo 2y ago
    The Italian version of Excel uses a custom CSV style with ; as a column separator. This breaks many applications that accept CSVs. It's super annoying.
  • boogheta 2y ago
    You should look at its author's nationality ;)
• InsideOutSanta 2y ago
  This is particularly funny because I just received a ticket saying that the CSV import in our product doesn't work. I asked for the CSV, and it uses a semicolon as a delimiter. That's just what their Excel produced, apparently. I'm taking their word for it because... Excel. To me, CSV is one of the best examples of why Postel's Law is scary. Being a liberal recipient means your work never ends because senders will always find fun new ideas for interpreting the format creatively and keeping you on your toes.
  • Fokamul 2y ago
    Of course, because there are locales which uses comma as decimal separator. So CSV in Excel then defaults to semicolon. Another Microsoft BS, they should defaults to ENG locale in CSV, do a translation in background. And let user choose, if they want to save as different separator. Excel in every part of world should produce same CSV by default. Bunch of idiots.
    • roelschroeven 2y ago
      Yes. CSV is a data interchange format, it's meant to be written on one computer and read by another. Making the representation of data dependent on the locale in use is stupid af. Locales are for interaction with the user, not for data interchange.
• Timwi 2y ago
  I am annoyed that comma won out as the separator. Tab would have been a massively better choice. Especially for those of us who have discovered and embraced elastic tabstops. Any slightly large CSV is unreadable and uneditable because you can't easily see where the commas are, but with tabs and elastic tabstops, the whole thing is displayed as a nice table. (That is, of course, assuming the file doesn't contain newlines or other tabs inside of fields. The format should use \t \n etc for those. What a missed opportunity.)
  • Fokamul 2y ago
    CSV have multiple different separators. Eg. Excel defaults to different separators based on locale. Like CZ locale, it uses commas in numbers instead of dot, so CSV uses semicolon as default separator.
    • skrebbel 2y ago
      > Excel defaults to different separators based on locale. Which is absolutely awful for interop and does not deserve being hauled as a feature.
  • scryers_bloom 2y ago
    I wrote a web scraper for some county government data and went for tabs as well. It's nice how the columns lined up in my editor (some of these files had hundreds of thousands of lines).
  • alabastervlog 2y ago
    We have dedicated field separator characters :-/ And all kinds of other weirdness, right in ascii. Vertical tabs, LOL. Put those in filenames on someone else's computer if you want to fuck with them. Linux and its common file systems are terrifyingly permissive in the character set they allow for file names. Nobody uses any of that stuff, though.
• spintin 2y ago
  [dead]
• julik 2y ago
  Something I support completely - previously https://news.ycombinator.com/item?id=35418933#35438029 https://news.ycombinator.com/item?id=35418933#35438029 If CSV is indeed so horrible - and I do not deny that there can be an improvement - how about the clever data people spec out a format that Does not require a bizarre C++ RPC struct definition library _both_ to write and to read Does not invent a clever number encoding scheme that requires native code to decode at any normal speed Does not use a fancy compression algorithm (or several!) that you need - again - native libraries to decompress Does not, basically, require you be using C++, Java or Python to be able to do any meaningful work with it It is not that hard, really - but CSV is better (even though it's terrible) exactly because it does not have all of these clever dependency requirements for clever features piled onto it. I do understand the utility of RLE, number encoding etc. I do not, and will not, understand the utility of Thrift/Avro, zstandard and brotli and whatnot over standard deflate, and custom integer encoding which requires you download half of Apache Commons and libboost to decode. Yes, those help the 5% to 10% of the use cases where massive savings can be realised. It absolutely ruins the experience for the other 90 to 95. But they also give Parquet and its ilk a very high barrier of entry.
• jellyfishbeaver 2y ago
  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."
  • pasc1878 2y ago
    Yes because users can read the data themselves and don't need a programmer. Financial users live in Excel. If you stick to one locale (unfortunately it will have to be US) then you are OKish.
  • 0xbadcafebee 2y ago
    > 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?
• conceptme 2y ago
  The worst thing about CSV is Excel using localization to choose the delimiter: https://answers.microsoft.com/en-us/msoffice/forum/all/csv-file-are-using-wrong-separator/67b520e4-ce48-4bf5-914b-fa99de840549 https://answers.microsoft.com/en-us/msoffice/forum/all/csv-f... The second worst thing is that the escape character cannot be determined safely from the document itself.
• fatih-erikli-cg 2y ago
  [dead]
• deleted 2y ago
  [deleted]
• jwr 2y ago
  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.
  • byyll 2y ago
    I was recently writing a parser for a weird CSV. It had multiple header column rows in it as well as other header rows indicating a folder.
  • mjw_byrne 2y ago
    I used to be a data analyst at a Big 4 management consultancy, so I've seen an awful lot of this kind of thing. One thing I never understood is the inverse correlation between "cost of product" and "ability to do serialisation properly". Free database like Postgres? Perfect every time. Big complex 6-figure e-discovery system? Apparently written by someone who has never heard of quoting, escaping or the difference between \n and \r and who thinks it's clever to use 0xFF as a delimiter, because in the Windows-1252 code page it looks like a weird rune and therefore "it won't be in the data".
    • ethbr1 2y ago
      > Big complex 6-figure e-discovery system? Apparently written by someone who has never heard of quoting... It's because about a certain size, system projects are captured by the large consultancy shops, who eat the majority of the price in profit and management overhead... ... and then send the coding work to a lowest-cost someone who has never heard of quoting, etc. And it's a vicious cycle, because the developers in those shops that do learn and mature quickly leave for better pay and management. (Yes, there's usually a shit hot tiger team somewhere in these orgs, but they spend all their time bailing out dumpster fires or landing T10 customers. The average customer isn't getting them.)
• 0xbadcafebee 2y ago
  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.
• dkarl 2y ago
  I'll repeat what I say every time I talk about CSV: I have never encountered a customer who insisted on integrating via CSV who was capable of producing valid CSV. Anybody who can reliably produce valid CSV will send you something else if you ask for it. > CSV is not a binary format, can be opened with any text editor and does not require any specialized program to be read. This means, by extension, that it can both be read and edited by humans directly, somehow. This is why you should run screaming when someone says they have to integrate via CSV. It's because they want to do this. Nobody is "pretending CSV is dead." It'll never die, because some people insist on sending hand-edited, unvalidated data files to your system and not checking for the outcome until mid-morning the next day when they notice that the text selling their product is garbled. Then they will frantically demand that you fix it in the middle of the day, and they will demand that your system be "smarter" about processing their syntactically invalid files. Seriously. I've worked on systems that took CSV files. I inherited a system in which close to twenty "enhancement requests" had been accepted, implemented, and deployed to production that were requests to ignore and fix up different syntactical errors, because the engineer who owned it was naive enough to take the customer complaints at face value. For one customer, he wrote code that guessed at where to insert a quote to make an invalid line valid. (This turned out to be a popular request, so it was enabled for multiple customers.) For another customer, he added code that ignored quoting on newlines. Seriously, if we encountered a properly quoted newline, we were supposed to ignore the quoting, interpret it as the end of the line, and implicitly append however many commas were required to make the number of fields correct. Since he actually was using a CSV parsing library, he did all of this in code that would pre-process each line, parse the line using the library, look at the error message, attempt to fix up the line, GOTO 10. All of these steps were heavily branched based on the customer id. The first thing I did when I inherited that work was make it clear to my boss how much time we were spending on CSV parsing bullshit because customers were sending us invalid files and acting like we were responsible, and he started looking at how much revenue we were making from different companies and sending them ultimatums. No surprise, the customers who insisted on sending CSVs were mostly small-time, and the ones who decided to end their contracts rather than get their shit together were the least lucrative of all. > column-oriented data formats ... are not able to stream files row by row I'll let this one speak for itself.
• larusso 2y ago
  I can‘t really understand the love for the format. Yes it’s simple but also not defined in a common spec. Same story with markdown. Yes GitHub tried to push for a spec but it still feels more like a flavor. I mean there is nothing wrong with not having a spec. But certain guarantees are not given. Will the document exported by X work with Y.
• esbranson 2y ago
  CSV on the Web (CSVW) is a W3C standard designed to enable the description of CSV files in a machine-readable way.[1] "Use the CSV on the Web (CSVW) standard to add metadata to describe the contents and structure of comma-separated values (CSV) data files." — UK Government Digital Service[2][3] [1] https://www.w3.org/TR/tabular-data-primer/ https://www.w3.org/TR/tabular-data-primer/ [2] https://www.gov.uk/government/publications/recommended-open-standards-for-government/using-metadata-to-describe-csv-data https://www.gov.uk/government/publications/recommended-open-... [3] https://csvw.org/ https://csvw.org/
• abought 2y ago
  At various points in my career, I've had to oversee people creating data export features for research-focused apps. Eventually, I instituted a very simple rule: As part of code review, the developer of the feature must be able to roundtrip export -> import a realistic test dataset using the same program and workflow that they expect a consumer of the data to use. They have up to one business day to accomplish this task, and are allowed to ask an end user for help. If they don't meet that goal, the PR is sent back to the developer. What's fascinating about the exercise is that I've bounced as many "clever" hand-rolled CSV exporters (due to edge cases) as other more advanced file formats (due to total incompatibility with every COTS consuming program). All without having to say a word of judgment. Data export is often a task anchored by humans at one end. Sometimes those humans can work with a better alternative, and it's always worth asking!
• Evidlo 2y ago
  There was/is CSVY [0] which attempted to put column style and separator information in a standard header. It is supported by R lang. I also asked W3C on theirGithub if there was any spec for CSV headers and they said there isn't [1]. Kind of defeats the point of the spec in my opinion. 0: https://github.com/leeper/csvy https://github.com/leeper/csvy 1: https://github.com/w3c/csvw/issues/873 https://github.com/w3c/csvw/issues/873
• richardwhiuk 2y ago
  CSV isn't dynamically typed. Everything is just a string.
• barbazoo 2y ago
  > Excel hates CSV Does it though? Seems to be importing from and exporting to CSV just fine? Elaborate maybe.
• acc_297 2y ago
  Working in clinical trial data processing I receive data in 1 of 3 formats: csv, sas datasets, image scans of pdf pages showing spreadsheets Of these 3 options sas datasets are my preference but I'll immediately convert to csv or excel, csv is a close 2nd once you confirm the quoting / seperator conventions it's very easy to parse. I understand why someone may find the csv format disagreeable but in my experience the alternatives can be so much worse I don't worry too much about csv files
• sirukinx 2y ago
  For everyone complaining about CSV/TSV, there's a scripting language called R. It makes working with CSV/TSV files super simple. It's as easy this: # Import tidyverse after installing it with install.packages("tidyverse") library(tidyverse) # Import TSV dataframe_tsv <- read_tsv("data/FileFullOfDataToBeRead.tsv") # Import CSV dataframe_csv <- read_csv("data/FileFullOfDataToBeRead.csv") # Mangle your data with dplyr, regular expressions, search and replace, drop NA's, you name it. <code to sanitize all your data> Multiple libraries exist for R to move data around, change the names of entire columns, change values in every single row with regular expressions, drop any values that have no assigned value, it's the swiss army knife of data. There are also all sorts of things you can do with data in R, from mapping with GPS coordinates to complex scientific graphing with ggplot2 and others. Here's an example for reading iButton temperature sensor data: https://github.com/hominidae/ibutton_tempsensors/ https://github.com/hominidae/ibutton_tempsensors/ Notice that in the code you can do the following to skip leading lines by passing it as an argument: skip = 18 cf1h <- read_csv("data/Coldframe_01_High.csv", skip = 18)
• jbverschoor 2y ago
  CSV is the PHP of fileformats
• ringofchaos 2y ago
  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.
• dubyajaysmith 2y ago
  9. Excel hates CSV: It clearly means CSV must be doing something right. <3<3<3
• jongjong 2y ago
  I've found some use cases where CSV can be a good alternative to arrays for storage, search and retrieval. Storing and searching nested arrays in document databases tends to be complicated and require special queries (sometimes you don't want to create a separate collection/table when the arrays are short and 1D). Validating arrays is actually quite complicated; you have to impose limits not only on the number of elements in the array, but also on the type and size of elements within the array. Then it adds a ton of complexity if you need to pass around data because, at the end of the day, the transport protocol is either string or binary; so you need some way to indicate that something is an array if you serialize it to a string (hence why JSON exists). Reminds me of how I built a simple query language which does not require quotation marks around strings, this means that you don't need to escape strings in user input anymore and it prevents a whole bunch of security vulnerabilities such as query injections. The only cost was to demand that each token in the query language be separated by a single space. Because if I type 2 spaces after an operator, then the second one will be treated as part of the string; meaning that the string begins with a space. If I see a quotation mark, it's just a normal quotation mark character which is part of the string; no need to escape. If you constrain user input based on its token position within a rigid query structure, you don't need special escape characters. It's amazing how much security has been sacrificed just to have programming languages which collapse space characters between tokens... It's kind of crazy that we decided that quotation marks are OK to use as special characters within strings, but commas are totally out of bounds... That said, I think Tab Separated Values TSV are even more broadly applicable.
• mharig 2y ago
  [dead]
• RandomMarius 2y ago
  "CSV" should die. The linked article makes critical ommisions and is wrong about some points. Goes to show just how awful "CSV" is. For one thing, it talks about needing only to quote commas and newlines... qotes are usually fine... until they are on either side of the value. then you NEED to quote them as well. Then there is the question about what exactly "text" is; with all the complications around Unicode, BOM markers, and LTR/RTL text.
• orefalo 2y ago
  CSV? it's surely simple but.. - where is meta data? - how do you encode binary? - how do you index? - where are relationships? different files?