7 ms·
Why CSV is still king
- calibas 2y agoWhy don't we use 0x1F (␟) instead of "," or TAB to separate units and 0x1E (␞) to separate records? It seems like half the problems with CSV were solved back in the 70s with ASCII codes.
- gaganyaan 2y agoSomebody went one step further and invented a format that uses the unicode codepoints as separators: https://github.com/SixArm/usv https://github.com/SixArm/usv
- NikkiA 2y agoBecause nobody made keyboards with those keys. Had they stuck on a 'next unit' and 'next record' key pair that sent them, we'd all be fine, but instead we got overly redundant text editing keys rather than keyboards more suited to data entry.
- 01HNNWZ0MV43FF 2y agoBecause json5L hasn't caught on yet and everything else has obvious flaws
- ryan_j_naughton 2y agoEh, I'm skeptical of this statement. CVS is explicitly about tabular data. JSON (including JSON5) is much more flexible. Flexibility can be great but also can be annoying. If you want tabular data, then a system that enables nesting isn't great.
- yawnxyz 2y agoI love jsonlines but csvs are way more compact, since you don't have to repeat the column name for every line of data
- ianburrell 2y agoYou would write JSON arrays without names for tabular data. I don’t know if there is a standard way to do the header, but array of names would work. Or JSON Schema record.
- sam_perez 2y agoI think the fact that a human can mostly just read csvs is an important part of their adoption, too.
- jessekv 2y agoRather than highlighting flexibility as the differentiator, I would say: CSV is for dense data, JSON is for sparse data. They are flexible in different ways. For example, CSV is very flexible when renaming a column title.
- 0cf8612b2e1e 2y agoI routinely interface with 1GB+ csvs. The size explosion for json would be huge. Disk IO aside, I assume a json parser is going to be slower to parse than csv.
- imtringued 2y agoHow would JSON cause a size explosion? Nothing prevents you using ndjson where you define a header and then have an array per line.
- 0cf8612b2e1e 2y agoNobody does this currently. You have now created another bespoke format. If I am going to need a custom parser/writer, I might as well lean on a binary format that has far stronger properties than a text based one.
- ianburrell 2y agoJSONL is pretty common format. It makes sense for logs and anything else written incrementally. JSON parsers are super common. They are simpler and faster than CSV because it is more regular. JSONL is simple to implement cause write by record and read by line. The only difference with CSV are bracket characters around line and every string has quotes. The benefit is clear escaping rules including for newlines.
- 0cf8612b2e1e 2y agoJSONL is standard. Upthread said to write the header row and then make subsequent rows arrays. Of which I am not aware of anything that does this currently. My objection to JSONL was about the increase in file size owing to repeating the keys.
- ianburrell 2y agoJSON can write arrays in addition to hashes. JSON arrays are nearly identical to CSV. The only difference is brackets around li;es. There is no extra space wasted for keys.
- impure 2y agoI switched to TSV files for my app. None of my values contain tabs so I don't have to escape anything.
- SoftTalker 2y agoThe ASCII specification defines characters for separating fields, groups, records, and files, but I've rarely seen them used.
- zepolen 2y agoThat's because anyone can easily make a tab character with their keyboard. No one ever remembers the key combination for those special ascii characters.
- kstrauser 2y agoIf it became popular, all common editors would have an easy way to type them.
- cylinder714 2y agoThis encapsulates my problem with CSVs: - If I send someone a spreadsheet, they'll open it with a spreadsheet application; Excel, LibreOffice, whatever. - If I send someone a CSV file, they'll want to open it with a text editor. Ack, no! Open it with a spreadsheet app, or load it into SQLite, or, best of all, open it with VisiData or some kind of editor designed for tabular data. https://www.visidata.org/ https://www.visidata.org/
- hobs 2y agoActually no - spreadsheets classically choose their own way to interpret CSVs, that's the classic way to get your client to continue to send you support requests. There's a reason so many tools export to xls instead of csv.
- tanin 2y agoWhat surprised me the most about CSVs is that: - To escape the delimiter, we should enclose the value with double quotes. Ok, makes sense. - To escape double quotes within the enclosing double quotes, we need to use 2 double quotes. Many tools are getting it wrong. Meanwhile some tools like pgadmin, justifiably, allows you to configure the escaping character to be double quote or single quote because CSV standard is often not respected. Anyway, if you are looking for a desktop app for querying CSVs using SQL, I'd love to recommend my app: https://superintendent.app https://superintendent.app (offline app) -- it's more convenient than using command-line and much better for managing a lot of CSVs and queries.
- arp242 2y ago> Many tools are getting it wrong. They're not getting it wrong, they're just assuming a different variant. There is no "standard" for CSV. Yes, there's an RFC, published in 2005, about 30 years after everyone was already using CSV. That's too late. You can't expect people to drop all compatibility just because someone published some document somewhere. RFC 4180 explicitly says that "it does not specify an Internet standard of any kind", although many people do take it as a "standard". But even if it did call itself a standard: it's still just some document someone published somewhere. They should have just created a new "Comma Separated Data" (file.csd) standard or something instead of trying to retroactively redefine something that already exists. Then applications could add that as a new option, rather than "CSV, but different from what we already support". That was always going to be an uphill battle. Never mind that RFC 4180 is just insufficient by not specifying character encodings in the file itself, as well as some other things such as delimiters. If someone were to write a decent standard and market it a bit, then I could totally see this taking off, just as TOML "standardized INI files" took off.
- Cyberdog 2y agoDid TOML take off? As much as I love it, it seems really rare to see in the wild. I still see YAML everywhere and despair.
- 2y ago
- dietr1ch 2y agoI think that we just need someone to get fed up and simply tackle the list of well known problems of CVS. What we need is, - A standard (yeah, link xkcd 927, it's mentioned enough that I can recall it's ID) to be announced **after** the rest of things are ready. - Libraries to work with it in major languages. One in Rust + wrappers in common languages might get good traction these days. Having support for dataframe libraries right away might be necessary too. - Good tooling. I'm guessing one of the reasons CSV took off is that regular unix tools are able to deal with CVSs mostly fine (there's edge cases with field delimiters/commas, but it's not that bad). The new format would ideally have types, the files would be sharded and have metadata to quickly scan them, and the tooling should be able to make simple joins, ideally automatically based on the metadata since most of the times there's a single reasonable way to join tables. This seems too much work to get right since the very beginning, so maybe building on top of Apache Arrow might help reduce the solution space.
- wenc 2y ago> The new format would ideally have types, the files would be sharded and have metadata to quickly scan them, and the tooling should be able to make simple joins, ideally automatically based on the metadata since most of the times there's a single reasonable way to join tables. Parquet fits the bill here. It's not perfect (there is no perfect file format), but it's a practical compromise as of today, at least for new systems where a columnar format is appropriate. There are some columnar formats that are better in some aspects (like ORC and some proprietary formats) but they're not as widely supported. It's not that CSV/TSV is bad in every situation, but more that CSV/TSV is overused for things it shouldn't be used for. (CSV is good as for tabular format for simple applications, very bad as the storage format for data lakes or anything you want to query, questionable as an data exchange format, okay as a semi-structured format for structurally simple data -- many open data platforms offer it as a a download format and it generally works). To get a sense of how much variation a CSV reader needs to handle, we can take a look at the number of arguments there are in Pandas' read_csv. And it still fails on some CSVs! (I've had to preprocess CSVs before pd.read_csv would work) https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_csv.html https://pandas.pydata.org/pandas-docs/stable/reference/api/p... CSV is not king, but it is popular. But popularity doesn't mean it's good for every use case. Optimizing for human readability and easy generation means trading off other very important characteristics (type safety, legibility across different tooling, random access performance, reliability/consistency). You can't do anything about legacy systems, but when designing a new system, you should really ask yourself: is CSV really the right choice? (With DuckDB, the answer for me is increasingly no)
- breck 2y agoI love CSVs. I made, ScrollSets a language that compiles to CSVs! (https://scroll.pub/blog/scrollsets.html https://scroll.pub/blog/scrollsets.html) Here's a simple tool to turn your CSV into ScrollSet (https://scroll.pub/blog/csvToScrollSet.html https://scroll.pub/blog/csvToScrollSet.html) This is what powers the CSV download on PLDB.io and how so many people collaborate on building a single CSV (https://pldb.io/csv.html https://pldb.io/csv.html)
- endgame 2y agoI wasn't around at the time, but surely ASCII was (even if not ubiquitous)? Is there any particular reason that the FS/GS/RS/US (file/group/record/unit separator) characters didn't catch on in this role?
- al_borland 2y agoIf I had to take a guess, I’d say the answer is as simple as there is no key for them on the keyboard.
- euroderf 2y agoSounds like a job for a macOS keyboard code whiz.
- EvanAnderson 2y agoI did an ETL project years ago from a legacy app that used these delimiters. It was gloriously easy. No need to worry about escaping (as these characters were illegal in the input). It's a shame they didn't catch on.
- EvanAnderson 2y agoA lot of data that I see in CSV "format" would work fine as tab-delimited and wouldn't need any escaping (because most of the data I see doesn't allow literal tabs anyway). That would be a simple improvement over CSV.
- Nihilartikel 2y agoI've found the Unicode cat emoji to be an effective delimiter to avoid escaping more common chars in my cat-separated-value artifacts. Of course the cat emoji is escaped by the puppy emoji if it occurs in a value. The puppy emoji escapes itself when needed.
- geekodour 2y agolast line unclear ⬛ an example would be great!
- deleted 2y ago[deleted]
- TylerE 2y agoThink backlashes in shell. \$ is just $, \\$ is literal ‘\$’
- deleted 2y ago[deleted]
- ok_computer 2y agoI read that as the puppy emoji escapes itself as two characters print a single character, similar to \ in python strings using \\ to print \
- theendisney4 2y agoIn the 80's i thought we should have an entire character set just for code. While never implemented the idea arguably aged well. I also considered a dedicated keyboard like apl just to be dense about it. Have each character signed by the keyboard so that we have proof by whoem it was typed and when. People who dont work here don't get to write code. It just wont happen. haha
- acuozzo 2y ago> In the 80's i thought we should have an entire character set just for code. APL got pretty close.
- userbinator 2y agoI wish binary length-prefixed formats would've become more common. Parsing text, and especially escaping, seems to be a continual source of bugs and confusion. Then again, those who don't implement escaping correctly may also overlap with those who can't be bothered learning how to use a hex editor.
- deleted 2y ago[deleted]
- Dwedit 2y agoThey are pretty common, just not among "JSON/XML everywhere" people.
- __mharrison__ 2y agoCSV is the VHS of data formats. Or to reference our discussion from yesterday, the markdown of data formats. It gets the job done. I help clients deal with them frequently. For many cases they are sufficient, for other cases moving to something like parquet makes a lot of sense.
- zarzavat 2y agoJust use TSV. Commas are a terrible delimiter because many human strings have commas in them. This means that CSV needs quoting of fields and nobody can agree on how exactly that should work. TSV doesn’t have this problem. It can represent any string that doesn’t have either a tab or a newline, which is many more than CSV can.
- btreecat 2y agoIn that case, why not use "|" (pipe character)?
- uncharted9 2y agoIt's 2024 and Excel still doesn't natively parse CSV with tabs as delimiters. When I send such csv files to my colleagues, they complain about not being able to open them directly in Excel. I wish Excel could pop up a window like LibreOffice does to confirm the delimiter before opening a csv file.
- pdyc 2y agoindeed, i created my own tool to preview and adjust csv files before viewing https://csvonline.newbeelearn.com/csvdemo https://csvonline.newbeelearn.com/csvdemo . Its not ready yet would probably not work for large files but works well enough for csv's with appended data that screws up formatting.
- newusertoday 2y agowow! it does exactly what i want :-) . what are the odds of that! I tested with a bank file where csv starts after some lines and i was able to read csv after bit of fiddling with configure button. What is theming demo doing btw with csv?
- hilbert42 2y agoExchanging information between different data formats is one of the biggest problems I've experienced in computing and IT and it's been thus from the earliest days. Having so many formats is confusing, inefficient and leads to data loss. This article is right, CSV is king simply because it's essentially the lowest common denominator and I, like most of us, use it for that reason—at least that's so for data that can be stored in database type formats. But take other data such as images, sound and AVI, and even text. There are dozens of sound, image and other formats. It's all a first-class mess. For example, we fall back to the antiquated horrible JPG format because we can't agree on better ones such as say jpeg 2000, there being always excuses why we can't such speed, data size, inefficient algorithms etc. Take word processing for instance, why is it so hard to convert Microsoft's confounded nasty DOC format to say the open document ODT format without errors. It's almost impossible to get the layout in one format converted accurately into another. Similarly, information is lost converting from lossless TIF to say JPG, or from WAV to MP3, etc. What's worse is that so few seem to care about such things. Every time a conversion is done between lossless formats and lossy ones entropy increases. That's not to say that shouldn't happen it's just that in isolation one has little or no idea about the quality of the original material. Even with ever increasing speeds, more and more storage space so many still have an obsession—in fact a fetish—of compressing data into smaller and smaller sizes using lossy formats with little regard for what's actually lost. It's not only in sound and image formats where data integrity suffers over convenience, take the case of converting data fields from one format to another. How often has one experienced the situation where a field is truncated during conversion—where say 128 characters suddenly becomes 64 or so after conversion and there's no indication from the converter that data has actually been truncated? Many times I'd suggest. Another instance, is where fields in the original data don't exist in the converted format. For example, data is often lost from one's phone contacts when converted from an old phone to a new one because the new phone doesn't accommodate all the fields of the old one. Programmers really have a damn hide for not only allowing this to occur but for not even warning the poor hapless user that some of his/her data has been lost. That programmers have so little reagard and consideration for data integrity I reckon is a terrible situation and a blight on the whole IT industry. Why doesn't computer science take these issues more seriously?
- 2y ago
- thbb123 2y agoSad that the ASCII specification includes 2 codes: 30 and 31, respectively field separator and record separator, precisely to answer cleanly the need that CSV fullfils addresses. During the 90's I was anal for using them, pissing the hell out of my teammates and users for forcing them to use these 'standard compliant' files. Had to give up.
- cqqxo4zV46cp 2y agoStandards-compliance and using esoteric features over catering for the realities of usability. Your coworkers were right to steamroll you.
- aleph_minus_one 2y agoIf these ASCII code points were actively used, the support in common editors that are used for editing CSV files would become much better very fast.
- hanche 2y agoSqlite still supports it: .mode ascii
- viraptor 2y agoThey're hard to type though. You need to teach people how to use those -vs- just using a comma.
- jeff-hykin 2y agoAnd they still don't fix the escaping problem. You might as well use a niche utf8 emoji as a separator. Editors at least know how to consistently render an emoji.
- giraffe_lady 2y agoHilariously I have actually seen this done.
- theanonymousone 2y agoI fully agree that CSV is king and am quite happy about it. But the comma character was probably one of the worst choices they could make for the "standard", IMHO of course. Tab makes far more sense here, because you are very likely able to just convert non-delimiter tabs to spaces without losing semantics. Even considering how editors tend to mess with the tab character, there are still better choices based on frequency in typical text: |, ~, or even ;. All IMHO, again.
- deleted 2y ago[deleted]
- corytheboyd 2y agoI don’t think CSV became king because “,” is a great delimiter (obviously it is not), it became king because it is an easy and logical separator _to most people_. Yeah it’s infuriatingly dumb from a technical standpoint. All the points here that tabs or ascii separators are superior are of course correct. I honestly respect it for how ubiquitous it became WITHOUT having a standard. Still going to curse when I have to deal with a broken one though.
- jeff-hykin 2y ago> Efforts to standardize them I actually just finished a library to add proper typed parsing that works with existing CSV files. Its designed to be as compatible as possible with existing spreadsheets, while allowing for perfect escaping and infinite nesting of complex data structures and strings. I think its an ideal compromise, as most CSV files won't change at all. https://github.com/jeff-hykin/typed_csv https://github.com/jeff-hykin/typed_csv
- nuc1e0n 2y agoAs the article says, it will be interesting to see if NDJSON becomes more popular. Although it's a bit more difficult to parse and has makes for larger files than CSV it is more unambiguous.
- Kon-Peki 2y agoCSV comes from a world in which the producer and consumer know each other; if there are problems they talk to each other and work it out. There is still plenty of this kind of data exchange happening, and CSV is perfectly fine for it. If I'm consuming data produced by some giant tech company or mega bank or whatever, there is no chance I'll be able to get them to fix some issue I have processing it. From these kind of folks, I'd like something other than CSV.
- LorenPechtel 2y agoBut the big guy most likely exports the .csv correctly in the first place, you don't *need* to work with them. Only once have I seen a bad .csv from a "big" company--big fish in a small pond type big. We were looking to get data out, hey, great, .csv is a valid export format. I'm not sure exactly what was in that file but it appeared to be the printout with some field info attached to each field. (Put this at that location on the paper etc, one field per line.) Every output format it has is in some scenario bugged.
- valiant55 2y agoI'm surprised that the article and the comments failed to mentioned pipe delimited files. I work with almost two dozen different vendors (in healthcare) and 90% use pipes. Doing data exchange with a variety of delimiters is so common that I just built out a bespoke system for taking in a set of common configurations and parsing the information. Other settings include line endings, encoding, escape characters, whether the header is included etc.
- Havoc 2y agoI’ve been using parquet more lately. Different tradeoffs. Not having to worry about escaping chars and delimiters is nice though
- trillic 2y agoI like Pipe-separated values
- fragmede 2y ago> Why CSV Will Remain King it'll only remain king as long as we let it. move to using Sqlite db files as your interchange format
- mannyv 2y agoCSV is king because most ETL department programmers suck. Half the time they can't generate a CSV correctly. Anything more complicated would cause their tiny brains to explode. I'm not bitter, I just hate working with ETL 'teams' that struggle to output the data in a specified format - even when you specify it in the way they want you to.
- up2isomorphism 2y agoNot sure what is a “king “ in this case. But fav is one of example that is intuitive and straight horrible at the same time.
- bandie91 2y agothe site says "something went wrong" just 1 sec AFTER it successfully displayed the content. something is so wrong that had withdraw the content from the user... use js only to enhance UX!
- penguin_booze 2y agoIt's a bit annoying that jq quotes strings in the CSV output: echo foo | jq -rR 'split("") | @csv'
- maerF0x0 2y agoI prefer ndjson for systems I build. (with only json objects on the top level) It's much safer for a lot of edges. If there's significant repetition in the keys, they end up zipping well.
- deafpolygon 2y agoCSV is still king because of one thing: inertia It's just much easier to keep using it, since you're already doing it. In the meantime, how about XML? /awaits the pack of raving mad HNers