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.
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.
Probably Microsoft treats a pure-text, simply specified, human-readable and editable spreadsheet format that fosters interoperability with competing software as an existential threat.
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.
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).
> 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.
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.
That, bad specs, weird management/ timezone/ governance/ communications and random \n\r issues transformed a 2 day fun little project into a 4 week hell. I will never work with CSV in France ever again. Mostly because of Excel, normal CSV nice.
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.
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.
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.
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.
Even in that case I'd be hesitant to open a CSV file in excel. The problem is that it will automatically apply whatever transformation it thinks is appropriate the moment you open the file. Have a digit string that isn't semantically a number? Too bad, it's a number now, and we're gonna go ahead and round it. You didn't really need _all_ of the digits of that insurance policy number, did you?
They did finally add options to turn off the common offenders, but I have a deeply ingrained distrust at this point.
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.
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.
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.
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.
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.
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.
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.
I agree that the default way Excel handles CSV files is terrible. Using Power Query to manage them is the way to go. But it's the general Microsoft approach to backwards compatibility so very unlikely to change now.
In the past I remember that Excel not properly handling UTF-8 encoded text in a CSV. It would treat it as raw ASCII (or possibly code page 1252). So if you opened and saved a CSV, it would corrupt any Unicode text in the file. It's possible this has been fixed in newer versions, I haven't tried in a while.
It's related to how older versions of Windows/Office handled Unicode in general.
From what I have heard, it's still an issue with Excel, although I assume that Windows may handle plain text better these days (I haven't used it in a while)
You need to write an UTF-8 BOM at the beginning (0xEF, 0xBB, 0xBF), if you want to make sure it's recognized as UTF-8.
Ugh, UTF-8 BOM. Many apps can handle UTF-8 but will try to return those bytes as content; maybe ours in 2015 too
I was on the Power Query team when we were improving the encoding sniffing. An app can scan ahead i.e. 64kB, but ultimately the user needs to just say what the encoding is. All the Power Query data import dialogs should let you specify the encoding.
UTF-8 BOM is probably not a good idea for anything other than (maybe) plain text documents. For data, many (although not all) programs should not need to care about character encoding, and if they include something such as UTF-8 BOM then it will become necessary to consider the character encoding even though it shouldn't be necessary.
I have repeatedly seen people getting the spreadsheets altered by excel, and in general a lot of troubles due to localisation reasons. Sometimes these changes can be subtle and be hard to spot until somebody tries to troubleshoot what went wrong down the line.
It works better if you click to "import the data" instead of just opening the csv file with it, and if you then choose the right data types. But having to do this everytime to make it work is really annoying, esp when you have a lot of columns, plus people can easily get confused with the data types. I have never seen that much confusion eg with macos's numbers.
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.
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.
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
The only situation I can think of where a tab is not a tab, is in a code editor that's been configured (possibly by default) to use spaces instead. But that's an easy enough configuration to change. And certainly wouldn't be a problem for something like TextEdit.
Functionally the same. I'd prefer CSV if my content was likely to have whitespace in it and didn't want billion quotes. I'd prefer TSV if my content was unlikely to have whitespace, and more likely to contain commas.
> IMO the delimiter should be a configurable option
It is. CSV has been character separated vs comma separated for probably decades now. Most tools you'd use to mess with them allow you to specify which separator character is being used.
There is at least a chance that the text fields will fit in a tab. If not, a tool like “column” on Linux can be used. There is no chance that a text field will fit inside the width of a comma.
If have to use a dedicated tabular data editing program, I may as well use a spreadsheet application. What do the options you propose do better than libreoffice calc?
is pretty nice because you can inspect a file or dataset on a server (no X) before downloading, to see if you even want to bother. Or you can pass it along through a series of pipes to just get the rows you want.
I’m not sure where that quote is from but it is incorrect, CSVs aren’t easily viewed or modified in text editors in general (at least not in any way that takes advantage of their tabular nature).
There’s at least a slight chance that tab separated values will look ok in a text editor (although in general, nope).
100% agree. TSV is under-rated. Tabs don't naturally occur in data nearly as often as commas so tabs are a great delimiter. Copy paste into Excel also works much better with tabs.
Code editors may convert tabs to spaces but are you really editing and saving TSV data files in your code editor?
The problem with TSV is what are you going to do about quotes. Some fields might contain them [1] or they might be needed to store fields with tabs inside them.
Because of this in order to read a plain simple TSV (fields separated by tabs, nothing more) with the Python csv module [2] you need to set the quote character to an improbable value, say € (using the euro sign because HN won't let me use U+1F40D), or just parse it by hand, e.g. row.split('\t').
TSV's big advantage is that, as commonly implemented, the separators are escaped, not quoted. This means that a literal newline (ASCII 0x0A) is always a record separator and a literal tab (ASCII 0x09) is always a field separator. This is the format many databases -- including Postgres -- use for text export by default.
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.
But when looking for a picture to back up my (likely flawed) memory, Google helpfully told me that you can get a record separator character by hitting Ctrl-^ (caret). Who knew?
The entire argument against ASCII Delimited Text boils down to "No one bothered to support it in popular editors back in 1984. Because I grew up without it, it is impossible to imagine supporting it today."
You need 4 new keyboard shortcuts. Use ctrl+, ctrl+. ctrl+[ ctrl+] You need 4 new character symbols. You need a bit of new formatting rules. Pretty much page breaks decorated with the new symbols. It's really not that hard.
But, like many problems in tech, the popular advice is "Everyone recognizes the problem and the solution. But, the problematic way is already widely used and the solution is not. Therefore everyone doing anything new should invest in continuing to support the problem forever."
The keys would be something other than those, though. They would be: CTRL+\ for file separator, CTRL+] for group separator, CTRL+^ for record separator, CTRL+_ for unit separator. Other than that, it would work like you described, I think.
> But, like many problems in tech, the popular advice is "Everyone recognizes the problem and the solution. But, the problematic way is already widely used and the solution is not
This is unfortunately common. However, what else happens too, is disagreement about what is the problem and the solution.
In Windows (and DOS EDIT.COM and a few other similarly ancient tools) there have existed Alt+028, Alt+029, Alt+030, and Alt+031 for a long time. I vaguely recall some file format I was working with in QBASIC used some or all of them and I was editing those files for some reason. That was not quite as far back as 1984, but sometime in the early 1990s for sure. I believe EDIT.COM had basic glyphs for them too, but I don't recall what they were, might have been random Wingdings like the playing card suits.
Having keyboard shortcuts doesn't necessarily solve why people don't want to use that format, either.
> I believe EDIT.COM had basic glyphs for them too, but I don't recall what they were, might have been random Wingdings like the playing card suits.
That is not specific to EDIT.COM; they are the PC characters with the same codes as the corresponding control characters, so they appear as graphic characters. (They can be used in any program that can use PC character set.)
However, in EDIT.COM and QBASIC you can also prefix a control character with CTRL+P in order to enter it directly into the file (and they appear as graphic characters, since I think the only control characters they will handle as control characters are tabs and line breaks).
Suits are PC characters 3 to 6; these are PC characters 28 to 31 which are other shapes.
> The entire argument against ASCII Delimited Text boils down to "No one bothered to support it in popular editors back in 1984. Because I grew up without it, it is impossible to imagine supporting it today."
There's also the argument of "Now you have two byte values that cannot be allowed to appear in a record under any circumstances. (E.g., incoming data from uncontrolled sources MUST be sanitized to reject or replace those bytes.)" Unless you add an escaping mechanism, in which case the argument shifts to "Why switch from CSV/TSV if the alternative still needs an escaping mechanism?"
Length-delimited binary formats do not need escaping. But the usual "ASCII Delimited Text" proposal just uses two unprintable bytes as record and line separators, and the signalling is all in-band.
This means that records must not contain either of those two bytes, or else the format of the table will be corrupted. And unless you're producing the data yourself, this means you have to sanitize the data before adding it, and have a policy for how to respond to invalid data. But maintaining a proper sanitization layer has historically been finicky: just look at all the XSS vulnerabilities out there.
If you're creating a binary format, you can easily design it to hold arbitrary data without escaping. But just taking a text format and swapping out the delimiters does not achieve this goal.
At least you don't need these values in your data, unlike the comma, which shows up in human-written text.
If you do need these values in your data, then don't use them as delimiters.
Something the industry has stopped doing, but maybe should do again, is restricting characters that can appear in data. "The first name must not contain a record separator" is a quite reasonable restriction. Even Elon Musk's next kid won't be able to violate that restriction.
It's more like, "because the industry grew up without it, other approaches gained critical mass."
Path dependence is a thing. Things that experience network effects don't get changed unless the alternative is far superior, and ASCII Delimited Text is not that superior.
Ignoring that and pushing for it anyway will at most achieve an xkcd 927.
As soon as you give those characters magic meanings then suddenly people will have reason to want to use them— it'll be a CSV containing localization strings for tooltips that contain that character and bam, we'll be back to escaping.
Except the usages of that character will be rare and so potentially way more scary. At least with quotes and commas, the breakages are everywhere so you confront them sooner rather than later.
Graphical representations of the control characters begin at U+2400 in the "Control Pictures" Unicode block. Instead of the actual U+001E Record Separator, you put the U+241E Symbol for Record Separator in the help text.
.... with a note underneath urging readers not to copy and paste the character because it's only the graphical representation of it, not the thing itself.
Perhaps a more salient example might be CSV nested in CSV. This happens all the time with XML (hello junit) and even JSON— when you plug a USB drive into my LG TV, it creates a metadata file on it that contains {"INFO":"{ \"thing\": true, <etc> }"}
You’ll still find that sequence in data; it’ll just be rare enough that it won’t rear its ugly head until your solution has been in production for a while.
And that’s why I tend to use tab delimited files more… when viewed with invisible characters shown, it’s pretty clear to read/write separate fields and have an easier to parse format.
This, of course, assumes that your input doesn’t include tabs or newlines… because then you’re still stuck with the same problem, just with a different delimiter.
Can't type them on a keyboard I guess, or generally work with them in the usual text-oriented tools? Part of the appeal of CSV is you can just open it up in Notepad or something if you need to. Maybe that's more a critique of text tools than it is of ASCII record separator characters.
If there were visible well known characters that could be printed for those and keys on a keyboard for inputting them we would probably have RSV files. Because they are buried down in the nonprintable section of the ASCII chart they are a pain for people to deal with. All it would have taken is one more key on the keyboard, maybe splitting the tab key in half.
> If there were visible well known characters that could be printed...
...There would be datasets that include those characters, and so they wouldn't be as useful for record separators. Look into your heart and know it to be true.
(...and many more.) "This comes up every single time someone mentions CSV. Without fail." - top reply from burntsushi in that last link, and it remains as true today as in 2017 :D
You're not wrong though, we just need some major text editor to get the ball rolling and start making some attempts to understand these characters, and the rest will follow suit. We're kinda stuck at a local optimum which is clearly not ideal but also not troublesome enough to easily drum up wide support for ADSV (ASCII Delimiter Separated Values).
Hahah, I came here to make the comment about ASCII's control characters, so I'm glad someone else beat me to it, and also that someone further pointed out that this topic comes up every time someone mentions CSV!
>we just need some major text editor to get the ball rolling and start making some attempts to understand these characters
Many text editors offer extensions APIs, including Vim, Emacs, Notepad++. But the ideal behavior would be to auto-align record separators and treat unit separators as a special kind of newline. That would allow the file to actually look like a table within the text editor. Input record separator as shift+space and unit separator as shift+enter.
> I don't get how this is not widely used as standard.
It requires bespoke tools for edition, and while CSV is absolute garbage it can be ingested and produced by most spreadsheet software, as well as databases.
I was really excited when I learned of these characters, but ultimately if it’s in ASCII then it’s in-band and will eventually require escaping leading to the same problem.
Eh, all you're really saying is "I'm not using CSV. Instead I'm using my CSV." Except that's all that anybody does.
CSV can just as easily support escaping as any other format, but there is no agreement for a CSV format.
After all, a missed escape can just as easily destroy a JSON or XML structure. And parallel processing of text is already a little sketchy simply because UTF-8 exists.
I want to push Sqlite as a data interchange format! it has the benefit of being well defined, and can store binary data, like images for product pictures inside the database. not a good idea if you're trying to serve users behind a web app, but as interchange, better than a zip file with filenames that have to be "relinked".
One very minor problem is that you max out storing blobs of 2GB(? I think, maybe 4GB). Granted few will hit this, but this limit did kill one previous data transfer idea of mine.
For context: I have a LOT of experience of interchange formats, like "full time job, every day, all day, hundreds of formats, for 20-years" experience.
Based on that experience I have come to one key, but maybe, counter-intuitive truth about interchange formats:
- Too much freedom is bad.
Why? Generating interchange data is cheaper than consuming it, because the creator only needs to consider the stuff they want to include, whereas the consumer needs to consider every single possible edge case and or scenario the format itself can support.
This is why XML is WAY more costly to ingest than CSV, because in XML someone is going to use: attributes, CDATA, namespaces, comments, different declaration, includes, et al. In CVS they're going to use rows, a format separator, and quotes (with or without escaping). That's it. That's all it supports.
Sqlite as an interchange format is a HORRIFYING suggestion, because every single feature Sqlite supports may need to be supported by consumers. Even if you curtailed Sqlite's vast feature set, you've still created something vastly more expensive to consume than XML, which itself is obnoxious.
My favorite interchange formats are, in order:
- CVS, JSON (inc. NDJSON), YAML, XML, BSON (due to type system), MessagePack, Protobuf, [Giant Gap] Sqlite, Excel (xlsx, et al)
More features mean more cost, more edge cases, more failures, more complex consumers. Keep in mind, this is ONLY about interchange formats between two parties, I have wildly different opinions about what I would use for my own application where I am only ever the creator/consumer, I actually love Sqlite for THAT.
Interesting! I've dealt with file interchange between closed source (and a couple open source) programs, but that was a while ago. I've also had to deal with csvs and xslts between SaaS vendors for import export of customer's data. I've done a bunch of reverse engineering of proprietary formats so we could import the vendor's files, which had more information than they were willing to export in an interchange format. Sometimes they're encrypted and you have to break it.
What you say is fair. Csv is underspecified though, there's no company called csv that's gonna sue for trademark enforcement, there's no official csv standard library that everyone uses. (They exist are some but there are so many naive importations because from first principles, because how hard could it be? output records and use a comma and newline (of which there are three possible options)).
How often do you deal with multiple Csv files to represent multiple tables that are actually what's used by vendors internally, vs one giant flattened Csv with hundreds of columns and lots of empty cells? I don't have your level of experience with csvs, but I've dealt with a them being a mess, where the other side implement whatever they think is reasonable given the name "comma separated values".
With sqlite, we're in the Internet age and so I presume this hypothetical developer would use the sqlite library and not implement their own library from scratch for funsies. This then leads to types, database normalization, multiple tables. I hear you that too many choices can bad, and xml is a great example of this, but sqlite isn't xml and isn't Csv.
It's hard to have this discussion in the abstract so I'll be forthcoming about where I'm coming from, which is Csv import export between vendors for stores, think like Doordash to UberEATS. the biggest problem we have is images of the items, and how to deal with that. It's an ongoing issue how to get them, but the failure mode, which does happen, is that when moving vendor, they just have to redo a lot of work that they shouldn't have to.
Ultimately the North Star I want to push towards is moving beyond csvs, because it'll let a people who currently have to hand edit the Csv so every row imports properly, not have to do that. They'd still exist, but instead have to deal with, well, what you see with XML files. which has its shortcomings, as you mention, but at least once how a vendor is using it is understood, individual records are generally understandable.
I was moved so I don't deal with import export currently, but it's because sqlite is so nice to work with on personal projects where it's appropriate that I want to push the notion of moving to sqlite over csvs.
Oh, this is interesting. Are you tying different systems together? If so, do you use some preferred intermediate format? Do you have a giant library of * -> intermediate -> * converters that you sprinkle between everything? Or maybe the intermediate format is in memory?
Not the person you were replying to, but from my experience CSV is a good place to define data types coming into a system and a safe way to dump data as long as I write everything down.
So I might do things like have every step in a pipeline begin development by reading from and writing to CSV. This helps with parallel dev work and debugging, and is easy to load into any intermediate format.
> do you use some preferred intermediate format?
This is usually dictated by speed vs money calculations, weird context issues, and familiarity. I think it's useful to look at both "why isn't this a file" and "why isn't this all in memory" perspectives.
For tabular/time-series greater than 100k rows I personally feel like parquet cannot be beat. It's self-describing, strongly-typed, relatively compact, supports a bunch of io/decode skipping, and is quite fast.
Orc also looks good, but isn't well supported. I think parquet is optimal for now for most analytical use-cases that don't require human readability.
It is an interchange format, so it is inter-system by virtue of that. If I am a self-creator/consumer the format I use can be literally anything even binary memory dumps.
> not a good idea if you're trying to serve users behind a web app
I use Sqlite for a static site! Generating those static pages out to individual pages would involve millions of individual files. So instead I serve up a sqlite database over http, and use a sqlite wasm driver [0] to load (database) pages as needed. Good indexing cuts down on the number of pages it grabs, and I can even get full text search!
Only feature I'm missing is compression, which is complicated because for popular extensions like sqlite-zstd written in Rust.
When you need to encode the newline character in your data, you say \n in the JSON. Unlike (the RFC dialect of) CSV, JSON has an escape sequence denoting a newline and in fact requires its use. The only reason to introduce newlines into JSON data is prettyprinting.
The new line character in a JSON string would always be \n. The new line in the record itself as whitespace would not be acceptable as that breaks the one line record contract.
Remember that this does not allow arbitrary representation of serialized JSON data. But it allows for any and all JSON data as you can always roundtrip valid JSON to a compact one line representation without extra whitespace.
It's tricky, but simple enough, RFC states that " must be used, inserting a " is done with "". This makes knowing what a record is difficult, since you must keep a variable that keeps the entire string.
How do you do this simply? you read each line, and if there's an uneven number of ", then you have an incomplete record and you will keep all lines until there is an odd number of ". after having the string, parsing the fields correctly is harder but you can do it in regex or PEGs or a disgusting state machine.
Actually even whitespace-separated json would be a valid format and if you forbid json documents to be a single integer or float then even just concatenating json gives a valid format as JSON is a prefix free language.
That is[0] if a string s is a valid JSON then there is no substring s[0..i] for i < n that is a valid json.
So you could just consume as many bytes you need to produce a json and then start a new one when that one is complete. To handle malformed data you just need to throw out the partial data on syntax error and start from the following byte (and likely throw away data a few more times if the error was in the middle of a document)
That is [][]""[][]""[] is unambiguos to parse[1]
[0] again assuming that we restrict ourselves to string, null, boolean, array and objects at the root
[1] still this is not a good format as a single missing " can destroy the entire document.
In jsonl a modified chunk will lose you at most the removed lines and the two adjacent ones (unless the noise is randomly valid json), in particular a single byte edit can destry at most 2 lines.
utf-8 is also similarly self-correcting and so is html and many media formats.
My point was that in my made-up concatenated json format
[]"""[][][][][][][][][][][]"""[]
and
[]""[][][][][][][][][][][]""[]
are both valid but have differ only for 2 bytes but have entirely different structures.
Also it is a made-up format nobody uses (if somebody were to want this they would likely disallow strings at the root level).
It's just that people tend to use specialized tools for encoding and decoding it instead of like ",".join(row) and row.split(",")
I have seen people try to build up JSON strings like that too, and then you have all the same problems.
So there is no problem with CSV except that maybe it's too deceptively simple. We also see people trying to build things like URLs and query strings without using a proper library.
JSON has a clearly-defined standards: ISO/IEC 21778:2017, IETF RFC 7159, and ECMA-404. Additionally, Crockford has had a spec available on json.org since it's creation in 2001.
Do you have any examples of Python, Java, or any of the other Tiobe top 40 languages breaking the JSON spec in their standard library?
In contrast, for the few of those that have CSV libraries, how many of those libraries will simply fail to parse a large number of the .csv variations out there?
You need more than a standard; that standard has to be complete and unambiguous. What you're looking for is https://github.com/nst/JSONTestSuite
EDIT: The readme's results are from 2016, but there's more recent results (last updated 5 years ago). Of the 54 parsers /versions tested, 7 gave always the expected result per the spec (disregarding cases where the spec does not define a result).
JSON doesn't fail for very large values because they are sent over the wire as strings. Only parsers may fail if they or their backing language doesn't account for BigInts or floats larger than f64, but these problems exist when parsing any string to a number.
And indeed applies to CSV as well: it's just strings at the end of the day, its up to the parser to make sense of it into the data types one wants. There is nothing inherently stopping you from parsing a JSON string into a uint64: I've done so plenty!
Trailing commas and comments are plainly not standard JSON under any definition. There are standards that include them which extend JSON, sure, but I'm not aware of any JSON library that emits this kind of stuff by default.
There is a clear standard and it's usually written on an old Word '97 doc in a local file server. Using CSV means that you are the compatibility layer, and this is useful if you need firm control or understanding of your data.
If that sounds like a lot of edge-case work keep in mind that people have been doing this for more than half a century. Lots of examples and notes you can steal.
> It's just that people tend to use specialized tools for encoding and decoding it instead of like ",".join(row) and row.split(",")
You really super can't just split on commas for csv. You need to handle the string encodings since records can have commas occur in a string, and you need to handle quoting since you need to know when a string ends and that string may have internal quote characters. For either format unless you know your data super well you need to use a library.
Eh, it really isn't. The format does not lend itself to tabular data, instead the most natural way of representing data involves duplicating the keys N times for each record.
You're missing my point: basically nothing spits out data in that format because it's not ergonomic to do so. JSON is designed to represent object hierarchies, not tabular data.
JSON was designed to represent any data. There's plenty of systems that spit out data in exact that format because it's the natural way to represent tabular data using JSON serialization. And clearly if you're the one building the system you can choose to use it.
JSON is lists of lists of any length and groups of key/value pairs (basically lisp S-expressions with lots of unnecessary syntax). This makes it a superset of CSV's capabilities.
JSON fundamentally IS made to represent tabular data, but it's made to represent key-value groups too.
Why make it able to represent tabular data if that's not an intended use?
> This header will contain names corresponding to the fields in the file and should contain the same number of fields as the records in the rest of the file
But of course, CSV is the wild west and there's no guarantee that any two encoders will do the same thing (sometimes, there's not even a guarantee that the same encoder will do the same thing with two different inputs).
Headers should have as many rows as possible that contain data items for their column and data items in a row should have a header for the respective columns, but real CSV files should be assumed to have incomplete or variable length lines.
The fact that json can represent a superset of tabular data structures that csv is specifically designed to represent can be rephrased into that csv is more specialised than json in representing tabular data. The fact that json can also represent tabular data does not mean it is a better or more efficient way to represent that data instead of a format like csv.
In the same way, there are hierarchically structured datasets that can be represented by both json in hierarchical form and csv in tabular form by repeating certain variables, but if using csv would require repeating them too many times, it would be a bad idea to choose that instead of json. The fact that you can do sth does not always make it a good idea to do it. The question imo is about which way would be more natural, easy or efficient.
> The fact that json can represent a superset of tabular data structures that csv is specifically designed to represent can be rephrased into that csv is more specialised than json in representing tabular data. The fact that json can also represent tabular data does not mean it is a better or more efficient way to represent that data instead of a format like csv.
The reverse is true as well: being more specialized is a description of goals, not advantages.
> JSON is lists of lists of any length and groups of key/value pairs
The "top-level" structure of JSON is usually an object, but it can be a list.
> JSON fundamentally IS made to represent tabular data
No, it's really not. It's made to represent objects consisting of a few primitive types and exactly two aggregate types: lists and objects. It's a textual representation of the JavaScript data model and even has "Object" in the name.
> Why make it able to represent tabular data if that's not an intended use?
It's mostly a question of specialization and ergonomics, which was my original point. You can represent tabular data using JSON (as you can in JavaScript), but it was not made for it. Anything that can represent """data""" and at least 2 nesting levels of arbitrary-length sequences can represent tabular data, which is basically every data format ever regardless of how awkward actually working with it may be.
JSON is designed to represent JavaScript objects with literal notation. Guess what, an array of strings or an array of numbers or even an array of mixed strings and numbers is a commonly encountered format in JavaScript.
"Any JSON primitive" does add a few requirements not semantically comparable to CSV, like numbers that are numbers, and keywords true, false, none.
When these syntaxes are parsed into objects, either the type info has to be
retaind, or some kind of attribute tag, so they can be output back to the same form.
> make it so any consumer can parse it by splitting on newline and then ...
There is something like that called JSON-lines. It has a .org domain 'n' everything:
Almost, except the way Excel-style quoting works with newlines sucks - you end up with rows that span multiple lines, so you can't split on newline to get individual rows.
With JSON those new lines are \n characters which are much easier to work with.
I ended up parsing the XML format instead of the CSV format when handling paste from Excel due to the newlines issue.
CSV seemed so simple but after numerous issues, a cell with both newline and " made me realize I should keep the little hair I had left and put in the work to parse the XML.
It's not great either, with all its weird tags, but at least it's possible to parse reliably.
This misses the point of standardization imo because it’s not possible to know a priori that the first line represents the variable names, that all the rows are supposed to have the same number of elements and in general that this is supposed to represent a table. An arbitrary parser or person wouldn’t know to guess since it's not standard or expected. Of course it would be parsed fine but the default result would be a kind of structure or multi-array rather than tabular.
This is the way. jsonl where each row is a json list. It has well-defined standard quoting.
Just like csv you don't actually need the header row either, as long as there's convention about field ordering. Similar to proto bufs, where the field names are not included in the file itself.
I see an array of arrays. The first and second arrays have two strings each, the last one has a float and a string. All those types are concrete.
Let's say those "1.1" and 7.4 values are supposed to be version strings. If your code is only sometimes putting quotes around the version string, the bug is in your code. You're outputting a float sometimes, but a string in others. Fix your shit. It's not your serialization format that's the problem.
If you have "7.4" as a string, and your serialization library is saying "Huh, that looks like a float, I'm going to make it a float", then get a new library, because it has a bug.
Types at the type layer are not the same as types at the semantic layer. Sure every type in the JSON level has a "strong type" but the semantic meaning of the contents of e.g. a string are usually not expressable in pure JSON. So it is with CSV; you can think of every cell in CSV as containing a string (series of bytes) with it being up to you to enforce the semantics atop those bytes. JSON gives you a couple extra types, and if you can fit things into those types well, then that's great, but for most data concrete semantically meaningful data you won't be able to do that and you'll end up in a similar world to CSVs.
> It’s that libraries and tools to parse CSV tend to suck. Whereas JSON is the lingua franca of data.
This isn't the case. An incredible amount of effort and ingenuity has gone into CSV parsing because of its ubiquity. Despite the lack of any sort of specification, it's easily the most widely supported data format in existence in terms of tools and language support.
Can you point me to a language with any significant number of users that does NOT have a JSON library?
I went looking at some of the more niche languages like Prolog, COBOL, RPG, APL, Eiffel, Maple, MATLAB, tcl, and a few others. All of these and more had JSON libraries (most had one baked into the standard library).
The exceptions I found (though I didn't look too far) were: Bash (use jq with it), J (an APL variant), Scratch (not exposed to users, but scratch code itself is encoded in JSON), and Forth (I could find implementations, but it's very hard to pin down forth dialects).
You claimed that CSV is "easily the most widely supported data format in existence in terms of tools and language support", which is a claim that CSV is better supported than JSON, which is a claim that JSON support is lacking.
Importing csvs in excel can be a huge pain due to how excel handles localisation. It can basically alter your data if you are not mindful about that, and I have seen it happening too many times.
Depends on what you mean by "better". I would rather software not handle a piece of data at all, than handle it erroneously and changing the data without me realising and thus causing all sorts of issues after.
CSV tooling has had to invest enormous amounts of effort to make a fragile, under-specified format half-useful. I would call it ubiquitous, I would call the tooling that we’ve built around it “impressive” but I would by no means call any of it “good”.
I do not miss dealing with csv files in the slightest.
Microsoft Windows has had to invest enormous amounts...
Apple macOS has had to invest enormous amounts...
Pick your distro of Linux has had to invest enormous amounts...
None of them a perfect and any number of valid complaints can be said about any of them. None of the complaints make any of the things useless. Everyone has workarounds.
Hell, JSON has had to invest enormous amounts of effort...
I guess the point is that I can take a generic json parser and point it at just about any JSON I get my hands on, and have close to no issues parsing it.
Want to do the same with csv? Good luck. Delimiter? Configurable. Encoding? Configurable. Misplaced comma? No parse in JSON, in csv: might still parse, but is now semantically incorrect and you possibly won’t know until it’s too late, depending on your parser. The list goes on.
> CSV tooling has had [...] to make a fragile, under-specified format half-useful
You get this backwards. Tabular structured data to store are ubiquitous. Text as a file format is also ubiquitous because it is accessible. The only actual decisions are about whether to encode your variables as rows or columns, what is the delimiter, and other rules such as escaping etc. Vars as columns makes sense because it makes appending easier. There is a bunch of stuff that can be used for delimeters, commas being the most common, none is perfect. But from this point onwards, decisions do not really matter, and "CSV" basically covers everything from now on. "CSV" is basically what comes naturally when you have tabular datasets and want to store them in text. CSV tooling is developed because there is a need for this way of formatting data. Whether CSV is "good" or "ugly" or whatever is irrelevant, handling data is complicated as much as the world itself is. The alternatives are either not structuring/storing the data in a tabular manner, or non-text (eg binary) formats. These alternative exist and are useful in their own right, but don't solve the same problems.
I think the issue is that CSV parsing is really easy to screw up. You mentioned delimiter choice and escaping, and I’d add header presence/absence to that list.
There are at least 3 knobs to turn every time you want to parse a CSV file. There’s reasonably good tooling around this (for example, Python’s CSV module has 8 parser parameters that let you select stuff), but the fact that you have to worry about these details is itself a problem.
You said “handling data is complicated as much as the world itself is”, and I 100% agree. But the really hard part is understanding what the data means, what it describes. Every second spent on figuring out which CSV parsing option I have to change could be better spent actually thinking about the data.
I am kind of amazed how people nag about having to parse practically a random file.
Having header or not should be specified up front and one should not parse some unknown file because that will always end up with failure.
If you have your own serialization and your own parsing working yeah this will simply work.
But then not pushing back to the user some errors and trying to deal with everything is going to be frustrating because amount of edge cases is almost infinite.
Handling random data is hard, saying it is a CSV and trying to support everything that comes with it is hard.
Before you dismiss it as 'not a language, people have argued that it is. And you can definitely program stuff in it, and so that surely makes it a language
Excel can import and parse JSON, it's under the "Get Data" header. It doesn't have a direct GUI way to export to JSON, but it takes just a few lines in Office Scripts. You can even use embedded TypeScript to call JSON.stringify.
Every time you cat a BSV file, your terminal beeps like it's throwing a tantrum. A record separator (RS) based file would be missing this feature! In other words, my previous comment was just a joke! :)
By the way, RS is decimal 30 (not octal '\030'). In octal, RS is '\036'. For example:
$ printf '\036' | xxd -p
1e
$ printf '\x1e' | xxd -p
1e
Well, Excel has a lot of common use-cases around processing numeric (and particularly financial) data. Since some locales use commas as decimal separators, using a character that's frequently present as a piece of data as a delimiter is a bit silly; it would be hard to think of a _worse_ character to use.
So, that means that Excel in those locales uses semicolons as separators rather than the more-frequently-used-in-data commas. Probably not the decision I'd make in retrospect, but not completely stupid.
> it's easily the most widely supported data format in existence in terms of tools and language support.
Even better, the majority of the time I write/read CSV these days I don't need to use a library or tools at all. It'd be overkill. CSV libraries are best saved for when you're dealing with random CSV files (especially from multiple sources) since the library will handle the minor differences/issues that can pop up in the wild.
> An incredible amount of effort and ingenuity has gone into CSV parsing because of its ubiquity.
Yea and it's still a partially-parseable shit show with guessed values. But we can and could have and should have done better by simply defining a format to use.
Space-wise, as long as you compress it, it's not going to make any difference. I suspect a JSON parser is a bit slower than a CSV parser, but the slight extra CPU usage is probably worth the benefits that come with JSON.
Until you have a large amount of data & need either random access or to work on multiple full columns at once. Duplicated keys names mean it's very easy for data in jsonlines format to be orders of magnitude larger than the same data as CSV, which is incredibly annoying if your processing for it isn't amenable to streaming.
I couldn't find the story on it, but there was an instance of a config for some major service getting truncated, but since it was yaml it was more difficult to figure out that that was what happened. I think in AWS, but I can't find the story, so can't really remember.
And fully fair that you can have similar issues in other formats. I think the complaint here was that it was a bit harder, specifically because it did not trip up any of the loading code. With a big lesson learned that configs should probably either go pascal string style, where they have an expected number of items as the first part of the data, or xml style, where they have a closing tag.
Really, it is always amusing to find how many of the annoying parts of XML turned out to be somewhat more well thought out than people want to admit.
Really depends on how the CSV is generated/transferred. If the output of the faulting software is line-buffered then it's quite likely that a failure would terminate the file at a line break.
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!
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.
I don't think we do? It's more that a bunch of companies already have their data in CSV format and aren't willing to invest any effort in moving to a new format. Doesn't matter how much one extolls all the benefits, they know right? They're paying someone else to deal with it.
Of course. I’m not saying I roll my own parser for every project that uses a CSV file, I’m just describing my criteria for using CSV vs some other format when I have the option.
Tab-Separated Value, as implemented by many databases, solves these problems, because tab, newline and other control characters are escaped. For example, the default text serialization format of Postgres (`COPY <table> TO '<file>'` without any options) is this way.
A good way to parallelize CSV processing is to split datasets into multiple files, kinda like manual sharding. xan has a parallel command able to perform a wide variety of map-reduce tasks on splitted files.
I don't understand why CSV became a thing when TSV, or a format using the nowadays weird ASCII control characters like start/end of text, start of heading, horizontal/vertical tab, file/group/record/unit separator.
It seems many possible designs would've avoided the quoting chaos and made parsing sort of trivial.
Any time you have a character with a special meaning you have to handle that character turning up in the data you're encoding. It's inevitable. No matter what obscure character you choose, you'll have to deal with it
The difference is that the coma and newline characters are much more common in text than 0x1F and 0x1E, which if you restrict your data to alphanumeric characters (which you really should) will never appear anywhere else.
The characters would likely be unique, maybe even by the spec.
Even if you wanted them, we use backslashes to escape strings in most common programming languages just fine, the problem CSV is that commas aren't easy to recognize because they might be within a single or double quote string, or might just be a separator.
Can strings in CSV have newlines? I bet parsers disagree since there's no spec really.
Except we have all these low ASCII characters specifically for this purpose that don't turn up in the data at all. But there is, of course, also an escape character specifically for escaping them if necessary.
You can't type any of those on a typewriter, or see them in old or simple simple editors, or no editor like just catting to a tty.
If you say those are contrived examples that don't matter any more then you have missed the point and will probably never acknowledge the point and there is no purpose in continuing to try to communicate.
One can only ever remember and type out just so many examples, and one can always contrive some response to any single or finite number of examples, but they are actually infinite, open-ended.
Having a least common denominator that is extremely low that works in all the infinite situations you never even thought of, vs just pretty low and pretty easy to meet in most common situations, is all the difference in the world.
Even if you find a character that really is never in the data - your encoded data will contain it. And it's inevitable that someone encodes the encoded data again. Like putting CSV in a CSV value.
I understand this argument in general. But basically everyone has some sort of spreatsheet application that can read CSV installed.
In some alternate worked where this "binary" format caught on it would be a very minor issue that it isn't human readable because everyone has a tool that is better at reading it than humans are. (See the above mentioned non-local property of quotes where you may think you are reading rows but are actually inside a single cell.)
Makes me also wonder if something like CBOR caught on early enough we would just be used to using something like `jq` to read it.
Exactly. "Use a delimiter that's not in the data" is not real serialisation, it's fingers-crossed-hope-for-the-best stuff.
I have in the past does data extractions from systems which really can't serialise properly, where the only option is to concat all the fields with some "unlikely" string like @#~!$ as a separator, then pick it apart later. Ugh.
> Exactly. "Use a delimiter that's not in the data" is not real serialisation, it's fingers-crossed-hope-for-the-best stuff.
It's not doing just this, you pick something that's likely not in the data, and then escape things properly. When writing strings you can write a double quote within double quotes with \", and if you mean to type the designated escape character you just write it twice, \\.
The only reason you go for something likely not in the data is to keep things short and readable, but it's not impossible to deal with.
I agree that it's best to pick "unlikely" delimiters so that you don't have to pepper your data with escape chars.
But some people (plenty in this thread) really do think "pick a delimiter that won't be in the data" - and then forget quoting and/or escaping - is a viable solution.
In TSV as commonly implemented (for example, the default output format of Postgres and MySQL), tab and newline are escaped, not quoted. This makes processing the data much easier. For example, you can skip to a certain record or field just by skipping literal newlines or tabs.
It's a lot easier to type comma than control characters and it's a lot easier to view comma than a tab (which might look like a space).
For automated serialization, plain text formats won out, because they are easy to implement a minimal working solution (both import and export) and more importantly, almost all systems agree on what plain text is.
We don't really have Apple formatted text, that will show up as binary on windows. Especially if you are just transferring id's and numbers, those will fall within ascii and that will work even if you are expecting unicode.
What issues would TSV have? As commonly implemented (for example, the default output format of Postgres and MySQL), in TSV, tab and newline are escaped, not quoted.
CSV's actual problem is that there's no single CSV, and you don't know what type you have (or even if you have single consistent type through the whole file) without trying to parse the whole file and seeing what breaks. Is there quoting? Is that quoting used consistently? Do you have five-digit ZIP codes, or have the East Coast ones been truncated to four digits because they began with zero? Spin the wheel!
I'm not clear why quotes prevent parallel processing?
I mean, you don't usually parallelize reading a file in the first place, only processing what you've already read and parsed. So read each record in one process and then add it to a multiprocessing queue for multiple processes to handle.
And data corruption is data corruption. If a movie I'm watching has a corrupted bit I don't mind a visual glitch and I want it to keep playing. But with a CSV I want to fix the problem, not ignore a record.
Do you really have a use case where reading itself is the performance bottleneck and you need to parallelize reading by starting at different file offsets? I know that multiple processes can read faster from certain high-end SSD's than just one process, but that's a level of performance optimization that is pretty extraordinary. I'm kind of curious what it is!
Doing sequential reading into a queue for workers to read is a lot more complicated than having a file format that supports parallel reading.
And the fix to allow parallel reading is pretty trivial: escape new lines so that you can just keep reading until the first unescaped new line and start at that record.
It is particularly helpful if you are distributing work across machines, but even in the single machine case, it's simpler to tell a bunch of workers their offset/limit in a file.
> I'm not clear why quotes prevent parallel processing?
Because of the "non-local" effect of quotes, you can't just jump into the middle of a file and start reading it, because you can't tell whether you're inside a quoted section or not. If (big if) you know something about the structure of the data, you might be able to guess. So that's why I said "tricky" instead of "impossible".
Contrast to my escaping-only strategy, where you can jump into the middle of a file and fully understand your context by looking one char on either side.
> Do you really have a use case where reading itself is the performance bottleneck and you need to parallelize reading by starting at different file offsets? I know that multiple processes can read faster from certain high-end SSD's than just one process, but that's a level of performance optimization that is pretty extraordinary. I'm kind of curious what it is!
I used to be a data analyst at a management consultancy. A very common scenario would be that I'm handed a multi-gigabyte CSV and told to "import the data". No spec, no schema, no nothing. Data loss or corruption is totally unacceptable, because we were highly risk-sensitive. So step 1 is to go through the whole thing trying to determine field types by testing them. Does column 3 always parse as a timestamp? Great, we'll call it a timestamp. That kind of thing. In that case, it's great to be able to parallelise reading.
> And data corruption is data corruption
Agreed, but I prefer data corruption which messes up one field, not data corruption which makes my importer sit there for 5 minutes thinking the whole file is a 10GB string value and then throw "EOF in quoted field".
> 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.
For my own parser, I made everything `\` escaped: outside of a quote or double-quote delimited string, any character prefixed with a `\` is read verbatim. There are no special exceptions resulting in `\,` producing a comma while `\a` produces `\a`. This makes it a good rule, because it is only one rule with no exceptions.
I considered this but then went the other way - a \ before anything other than a \, newline or comma is treated as an error. This leaves room for adding features, e.g. \N to signify a SQL NULL.
Regarding quoting and escaping, there are two options that make sense to me - either use quoting, in which case quotes are self-escaped and that's that; or use escaping, in which case quotes aren't necessary at all.
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.
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.
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.
I am saying JSONL is a lower bound format, if you can use something better you should. Data interchange, archiving, transmission, etc. It shouldn't be repeatedly queried.
Parquet, Arrow, sqlite, etc are all better formats.
Too bad xz/lzma isn't supported in these formats. I often get pretty big improvements in compression ratio. It's slower, but it can be parallelized too.
… 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"
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.
I already prefer using pipe as separator in logging; now you're telling me there is a chance that my logs can be automatically ingested as tabular data? Sign me up for this branch of the multiverse :)
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.
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.
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... :)
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.
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.
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.
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.
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.)
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.
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.
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.
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”.
>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.
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.
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.
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.
If you want to stream large volumes of row-oriented data, you aren't reading yourself and you should be using a binary format which is going to be significantly smaller (especially for numeric data).
Yeah that would be the next step in optimization. In the meanwhile, raw text CSV streaming (for not purely numeric data) is still extremely fast and easy to set up
While most people would prefer the second version, the first version is also valid JSON and will definitely see use when you want/need JSON but want to reduce data over the wire though you'd probably still see a wrapper object like:
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.
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.
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.
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.
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.
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.
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.
Appreciate your work! Your piece was pivotal in changing my mind about whether this should be considered in our purview to address.
The intention-based philosophy of all this makes a lot of sense, was eye opening, and I agree it should be the first approach. Unfortunately after considering our use cases, we quickly realized that we'd have no way of knowing how customers intend to use the csv exports they've requested - we've talked to some of them and it's a mix. We could approach things case by case but we really just want a setup which works well 99% of the time and mitigates known risk. We settled on the prefixing approach and have yet to receive any complaints about it, specifically using a space character with the mind that something unobtrusive (eg. easily strippable) but also visible, would be best - to avoid quirks stemming from something completely hidden.
Thank again for your writing and thoughts, like I said above I haven't found much else of quality on the topic.
I’ve almost always found the simple way around Excel users not knowing how to safely use CSV files is to just give the file another extension: I prefer .txt or .dat
Then, the user doesn’t have Excel has the default program for opening the file and has to jump through a couple safety hoops
If your customers and employees are using Excel then stop going against the grain with your niche software developer focused formats that need a lot of explanations.
I need to interface with a lot of non-technical people who exclusively use Excel. I give them .xlsx files. It's just as easy to export .xlsx as it is to export .CSV and my customers are happy.
How is .csv a niche dev-focused format? Our customers use our exports for a mix of purposes, some of them involving spreadsheet clients (not just excel) and some of them integrating with their own data pipelines. Csv conveniently works with these use cases across the board, without explanation, and is inconveniently saddled with these legacy security flaws in Excel (and probably other clients).
If xlsx works for all your use cases that's great, a much better solution that trying to sidestep these issues by lightly modifying the data. It's not an option for us, and (I'd imagine) a large contingent of export tools which can't make assumptions about downstream usage.
Someone filed a bug report on a project I work on, saying that it was a security vulnerability that we don't prefix cell values with a single quote (') when the cell content contains certain values like an equal sign (=). They said this can cause Excel to evaluate the content and potentially run unsafe code.
I responded that this was Excel's problem, not ours, and that nobody would assign a CVE to our product for such a "vulnerability". How naive I was! They forwarded me several such CVEs assigned to products that create CSVs that are "unsafe" for Excel.
I agree with the characterization ("security theater") of these bug reports. The problem is that the intentions of these reports don't make the potential risk less real, depending on the setting, and I worry that the "You're just looking for attention" reaction (a very fair one!) leads to a concerning downplaying of this issue across the web.
As a library author, I agree this very well may not be something that needs to be addressed. But as someone working in a company responsible for customers, employees, and their sensitive information, disregarding this issue disregards the reality of the tools these people will invariably use, downstream of software we _are_ responsible for. Aiming to make this downstream activity as safe as possible seems like a worthy goal.
I didn't know about the formula injection, I just knew that Excel and Sheets mangle my dates every time and it drives me bonkers. Why is that the default? It makes no sense.
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.
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
708 comments
[ 2.5 ms ] story [ 354 ms ] threadIt's not the fault of CSV that Excel changes which file format it uses based on locale.
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).
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.
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.
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.
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.
[0] https://www.theverge.com/2020/8/6/21355674/human-genes-renam...
They did finally add options to turn off the common offenders, but I have a deeply ingrained distrust at this point.
How is Excel like an Incel? Both of them think everything is a date.
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.
This is a good blog post and Xan is a really neat terminal tool.
https://miller.readthedocs/
That's it, but it's really bad.
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.
From what I have heard, it's still an issue with Excel, although I assume that Windows may handle plain text better these days (I haven't used it in a while)
You need to write an UTF-8 BOM at the beginning (0xEF, 0xBB, 0xBF), if you want to make sure it's recognized as UTF-8.
I was on the Power Query team when we were improving the encoding sniffing. An app can scan ahead i.e. 64kB, but ultimately the user needs to just say what the encoding is. All the Power Query data import dialogs should let you specify the encoding.
It works better if you click to "import the data" instead of just opening the csv file with it, and if you then choose the right data types. But having to do this everytime to make it work is really annoying, esp when you have a lot of columns, plus people can easily get confused with the data types. I have never seen that much confusion eg with macos's numbers.
But then some folks came along about 15 years ago screaming about spaces, and they won, so now tabs are 2 or 4 spaces.
The law of unintended consequences strikes again!
Note: not meant to denigrate you space supporters out there.
It is. CSV has been character separated vs comma separated for probably decades now. Most tools you'd use to mess with them allow you to specify which separator character is being used.
> The column command appeared in 4.3BSD-Reno.
[1]: https://man7.org/linux/man-pages/man1/column.1.html
[2]: https://man.freebsd.org/cgi/man.cgi?query=column&sektion=1
https://sharats.me/posts/automating-the-vim-workplace-3/#usi...
which greatly improved my TSV editing experience in vim. Couple that with autocmd TextChangedI and you get realtime recalculation and alignment.
There’s at least a slight chance that tab separated values will look ok in a text editor (although in general, nope).
Code editors may convert tabs to spaces but are you really editing and saving TSV data files in your code editor?
Because of this in order to read a plain simple TSV (fields separated by tabs, nothing more) with the Python csv module [2] you need to set the quote character to an improbable value, say € (using the euro sign because HN won't let me use U+1F40D), or just parse it by hand, e.g. row.split('\t').
[1]: https://github.com/wireservice/csvkit/issues/1194
[2]: https://docs.python.org/3/library/csv.html
There are some notes I put together about TSV a few years ago that expand on these points: https://github.com/solidsnack/tsv?tab=readme-ov-file#motivat...
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.
The characters you mention could be used in a custom delimiter variant of the format, but at that point it's back to a binary machine format.
/s in case :)
But when looking for a picture to back up my (likely flawed) memory, Google helpfully told me that you can get a record separator character by hitting Ctrl-^ (caret). Who knew?
You need 4 new keyboard shortcuts. Use ctrl+, ctrl+. ctrl+[ ctrl+] You need 4 new character symbols. You need a bit of new formatting rules. Pretty much page breaks decorated with the new symbols. It's really not that hard.
But, like many problems in tech, the popular advice is "Everyone recognizes the problem and the solution. But, the problematic way is already widely used and the solution is not. Therefore everyone doing anything new should invest in continuing to support the problem forever."
> But, like many problems in tech, the popular advice is "Everyone recognizes the problem and the solution. But, the problematic way is already widely used and the solution is not
This is unfortunately common. However, what else happens too, is disagreement about what is the problem and the solution.
Having keyboard shortcuts doesn't necessarily solve why people don't want to use that format, either.
That is not specific to EDIT.COM; they are the PC characters with the same codes as the corresponding control characters, so they appear as graphic characters. (They can be used in any program that can use PC character set.)
However, in EDIT.COM and QBASIC you can also prefix a control character with CTRL+P in order to enter it directly into the file (and they appear as graphic characters, since I think the only control characters they will handle as control characters are tabs and line breaks).
Suits are PC characters 3 to 6; these are PC characters 28 to 31 which are other shapes.
There's also the argument of "Now you have two byte values that cannot be allowed to appear in a record under any circumstances. (E.g., incoming data from uncontrolled sources MUST be sanitized to reject or replace those bytes.)" Unless you add an escaping mechanism, in which case the argument shifts to "Why switch from CSV/TSV if the alternative still needs an escaping mechanism?"
This means that records must not contain either of those two bytes, or else the format of the table will be corrupted. And unless you're producing the data yourself, this means you have to sanitize the data before adding it, and have a policy for how to respond to invalid data. But maintaining a proper sanitization layer has historically been finicky: just look at all the XSS vulnerabilities out there.
If you're creating a binary format, you can easily design it to hold arbitrary data without escaping. But just taking a text format and swapping out the delimiters does not achieve this goal.
If you do need these values in your data, then don't use them as delimiters.
Something the industry has stopped doing, but maybe should do again, is restricting characters that can appear in data. "The first name must not contain a record separator" is a quite reasonable restriction. Even Elon Musk's next kid won't be able to violate that restriction.
Path dependence is a thing. Things that experience network effects don't get changed unless the alternative is far superior, and ASCII Delimited Text is not that superior.
Ignoring that and pushing for it anyway will at most achieve an xkcd 927.
Except the usages of that character will be rare and so potentially way more scary. At least with quotes and commas, the breakages are everywhere so you confront them sooner rather than later.
Perhaps a more salient example might be CSV nested in CSV. This happens all the time with XML (hello junit) and even JSON— when you plug a USB drive into my LG TV, it creates a metadata file on it that contains {"INFO":"{ \"thing\": true, <etc> }"}
This, of course, assumes that your input doesn’t include tabs or newlines… because then you’re still stuck with the same problem, just with a different delimiter.
...There would be datasets that include those characters, and so they wouldn't be as useful for record separators. Look into your heart and know it to be true.
comment: https://news.ycombinator.com/item?id=26305052
comment: https://news.ycombinator.com/item?id=39679662
"ASCII Delimited Text – Not CSV or Tab Delimited Text" post [2014]: https://news.ycombinator.com/item?id=7474600
same post [2024]: https://news.ycombinator.com/item?id=42100499
comment: https://news.ycombinator.com/item?id=15440801
(...and many more.) "This comes up every single time someone mentions CSV. Without fail." - top reply from burntsushi in that last link, and it remains as true today as in 2017 :D
You're not wrong though, we just need some major text editor to get the ball rolling and start making some attempts to understand these characters, and the rest will follow suit. We're kinda stuck at a local optimum which is clearly not ideal but also not troublesome enough to easily drum up wide support for ADSV (ASCII Delimiter Separated Values).
Many text editors offer extensions APIs, including Vim, Emacs, Notepad++. But the ideal behavior would be to auto-align record separators and treat unit separators as a special kind of newline. That would allow the file to actually look like a table within the text editor. Input record separator as shift+space and unit separator as shift+enter.
1. the field separator to be shown as a special character
2. the row separator to be (optionally) be interpreted as a linefeed
IIRC 1) is true for Notepad++, but not 2).
It requires bespoke tools for edition, and while CSV is absolute garbage it can be ingested and produced by most spreadsheet software, as well as databases.
CSV can just as easily support escaping as any other format, but there is no agreement for a CSV format.
After all, a missed escape can just as easily destroy a JSON or XML structure. And parallel processing of text is already a little sketchy simply because UTF-8 exists.
Based on that experience I have come to one key, but maybe, counter-intuitive truth about interchange formats:
- Too much freedom is bad.
Why? Generating interchange data is cheaper than consuming it, because the creator only needs to consider the stuff they want to include, whereas the consumer needs to consider every single possible edge case and or scenario the format itself can support.
This is why XML is WAY more costly to ingest than CSV, because in XML someone is going to use: attributes, CDATA, namespaces, comments, different declaration, includes, et al. In CVS they're going to use rows, a format separator, and quotes (with or without escaping). That's it. That's all it supports.
Sqlite as an interchange format is a HORRIFYING suggestion, because every single feature Sqlite supports may need to be supported by consumers. Even if you curtailed Sqlite's vast feature set, you've still created something vastly more expensive to consume than XML, which itself is obnoxious.
My favorite interchange formats are, in order:
- CVS, JSON (inc. NDJSON), YAML, XML, BSON (due to type system), MessagePack, Protobuf, [Giant Gap] Sqlite, Excel (xlsx, et al)
More features mean more cost, more edge cases, more failures, more complex consumers. Keep in mind, this is ONLY about interchange formats between two parties, I have wildly different opinions about what I would use for my own application where I am only ever the creator/consumer, I actually love Sqlite for THAT.
What you say is fair. Csv is underspecified though, there's no company called csv that's gonna sue for trademark enforcement, there's no official csv standard library that everyone uses. (They exist are some but there are so many naive importations because from first principles, because how hard could it be? output records and use a comma and newline (of which there are three possible options)).
How often do you deal with multiple Csv files to represent multiple tables that are actually what's used by vendors internally, vs one giant flattened Csv with hundreds of columns and lots of empty cells? I don't have your level of experience with csvs, but I've dealt with a them being a mess, where the other side implement whatever they think is reasonable given the name "comma separated values".
With sqlite, we're in the Internet age and so I presume this hypothetical developer would use the sqlite library and not implement their own library from scratch for funsies. This then leads to types, database normalization, multiple tables. I hear you that too many choices can bad, and xml is a great example of this, but sqlite isn't xml and isn't Csv.
It's hard to have this discussion in the abstract so I'll be forthcoming about where I'm coming from, which is Csv import export between vendors for stores, think like Doordash to UberEATS. the biggest problem we have is images of the items, and how to deal with that. It's an ongoing issue how to get them, but the failure mode, which does happen, is that when moving vendor, they just have to redo a lot of work that they shouldn't have to.
Ultimately the North Star I want to push towards is moving beyond csvs, because it'll let a people who currently have to hand edit the Csv so every row imports properly, not have to do that. They'd still exist, but instead have to deal with, well, what you see with XML files. which has its shortcomings, as you mention, but at least once how a vendor is using it is understood, individual records are generally understandable.
I was moved so I don't deal with import export currently, but it's because sqlite is so nice to work with on personal projects where it's appropriate that I want to push the notion of moving to sqlite over csvs.
What about Parquet and the like?
So I might do things like have every step in a pipeline begin development by reading from and writing to CSV. This helps with parallel dev work and debugging, and is easy to load into any intermediate format.
> do you use some preferred intermediate format?
This is usually dictated by speed vs money calculations, weird context issues, and familiarity. I think it's useful to look at both "why isn't this a file" and "why isn't this all in memory" perspectives.
Orc also looks good, but isn't well supported. I think parquet is optimal for now for most analytical use-cases that don't require human readability.
I use Sqlite for a static site! Generating those static pages out to individual pages would involve millions of individual files. So instead I serve up a sqlite database over http, and use a sqlite wasm driver [0] to load (database) pages as needed. Good indexing cuts down on the number of pages it grabs, and I can even get full text search!
Only feature I'm missing is compression, which is complicated because for popular extensions like sqlite-zstd written in Rust.
[0] https://github.com/mmomtchev/sqlite-wasm-http
If you want CSV-ish, enforce an array of strings for each record. Or go further with actual objects and non-string types.
You can even jump to an arbitrary point and then seek till you see an actual new line as it’s always a record boundary.
It’s not that CSV is an invalid format. It’s that libraries and tools to parse CSV tend to suck. Whereas JSON is the lingua franca of data.
Remember that this does not allow arbitrary representation of serialized JSON data. But it allows for any and all JSON data as you can always roundtrip valid JSON to a compact one line representation without extra whitespace.
How do you do this simply? you read each line, and if there's an uneven number of ", then you have an incomplete record and you will keep all lines until there is an odd number of ". after having the string, parsing the fields correctly is harder but you can do it in regex or PEGs or a disgusting state machine.
That is[0] if a string s is a valid JSON then there is no substring s[0..i] for i < n that is a valid json.
So you could just consume as many bytes you need to produce a json and then start a new one when that one is complete. To handle malformed data you just need to throw out the partial data on syntax error and start from the following byte (and likely throw away data a few more times if the error was in the middle of a document)
That is [][]""[][]""[] is unambiguos to parse[1]
[0] again assuming that we restrict ourselves to string, null, boolean, array and objects at the root
[1] still this is not a good format as a single missing " can destroy the entire document.
utf-8 is also similarly self-correcting and so is html and many media formats.
My point was that in my made-up concatenated json format
[]"""[][][][][][][][][][][]"""[]
and
[]""[][][][][][][][][][][]""[]
are both valid but have differ only for 2 bytes but have entirely different structures.
Also it is a made-up format nobody uses (if somebody were to want this they would likely disallow strings at the root level).
It's just that people tend to use specialized tools for encoding and decoding it instead of like ",".join(row) and row.split(",")
I have seen people try to build up JSON strings like that too, and then you have all the same problems.
So there is no problem with CSV except that maybe it's too deceptively simple. We also see people trying to build things like URLs and query strings without using a proper library.
Do you have any examples of Python, Java, or any of the other Tiobe top 40 languages breaking the JSON spec in their standard library?
In contrast, for the few of those that have CSV libraries, how many of those libraries will simply fail to parse a large number of the .csv variations out there?
Sometimes commas to delimiter, sometimes semicolons, floating point values might have dots or commas to separate fraction digits.
Not to mention text encodings, Ascii, western european character sets, or maybe utf-8 or whatever...
It's a bloody mess.
EDIT: The readme's results are from 2016, but there's more recent results (last updated 5 years ago). Of the 54 parsers /versions tested, 7 gave always the expected result per the spec (disregarding cases where the spec does not define a result).
There's some interesting examples of ambiguities here: https://seriot.ch/projects/parsing_json.html
If that sounds like a lot of edge-case work keep in mind that people have been doing this for more than half a century. Lots of examples and notes you can steal.
You really super can't just split on commas for csv. You need to handle the string encodings since records can have commas occur in a string, and you need to handle quoting since you need to know when a string ends and that string may have internal quote characters. For either format unless you know your data super well you need to use a library.
JSON is lists of lists of any length and groups of key/value pairs (basically lisp S-expressions with lots of unnecessary syntax). This makes it a superset of CSV's capabilities.
JSON fundamentally IS made to represent tabular data, but it's made to represent key-value groups too.
Why make it able to represent tabular data if that's not an intended use?
I'd definitely put that in my list of falsehoods programmers believe about CSV files.
> This header will contain names corresponding to the fields in the file and should contain the same number of fields as the records in the rest of the file
But of course, CSV is the wild west and there's no guarantee that any two encoders will do the same thing (sometimes, there's not even a guarantee that the same encoder will do the same thing with two different inputs).
[0] https://www.ietf.org/rfc/rfc4180.txt
Headers should have as many rows as possible that contain data items for their column and data items in a row should have a header for the respective columns, but real CSV files should be assumed to have incomplete or variable length lines.
In the same way, there are hierarchically structured datasets that can be represented by both json in hierarchical form and csv in tabular form by repeating certain variables, but if using csv would require repeating them too many times, it would be a bad idea to choose that instead of json. The fact that you can do sth does not always make it a good idea to do it. The question imo is about which way would be more natural, easy or efficient.
The big advantage of JSON is that it's standardized and you can reuse the JSON infrastructure for more than just tabular data.
The reverse is true as well: being more specialized is a description of goals, not advantages.
The "top-level" structure of JSON is usually an object, but it can be a list.
> JSON fundamentally IS made to represent tabular data
No, it's really not. It's made to represent objects consisting of a few primitive types and exactly two aggregate types: lists and objects. It's a textual representation of the JavaScript data model and even has "Object" in the name.
> Why make it able to represent tabular data if that's not an intended use?
It's mostly a question of specialization and ergonomics, which was my original point. You can represent tabular data using JSON (as you can in JavaScript), but it was not made for it. Anything that can represent """data""" and at least 2 nesting levels of arbitrary-length sequences can represent tabular data, which is basically every data format ever regardless of how awkward actually working with it may be.
It could use backslash escapes to denote control characters and Unicode points.
Everyone would agree exactly on what the format is, in contrast to the zoo of CSV variants.
It wouldn't have pitfalls in it, like spaces that defeat quotes
oops; add an innocuous-looking space, and the quotes are now literal.When these syntaxes are parsed into objects, either the type info has to be retaind, or some kind of attribute tag, so they can be output back to the same form.
> make it so any consumer can parse it by splitting on newline and then ...
There is something like that called JSON-lines. It has a .org domain 'n' everything:
https://jsonlines.org/
With JSON those new lines are \n characters which are much easier to work with.
CSV seemed so simple but after numerous issues, a cell with both newline and " made me realize I should keep the little hair I had left and put in the work to parse the XML.
It's not great either, with all its weird tags, but at least it's possible to parse reliably.
Just like csv you don't actually need the header row either, as long as there's convention about field ordering. Similar to proto bufs, where the field names are not included in the file itself.
I see an array of arrays. The first and second arrays have two strings each, the last one has a float and a string. All those types are concrete.
Let's say those "1.1" and 7.4 values are supposed to be version strings. If your code is only sometimes putting quotes around the version string, the bug is in your code. You're outputting a float sometimes, but a string in others. Fix your shit. It's not your serialization format that's the problem.
If you have "7.4" as a string, and your serialization library is saying "Huh, that looks like a float, I'm going to make it a float", then get a new library, because it has a bug.
This isn't the case. An incredible amount of effort and ingenuity has gone into CSV parsing because of its ubiquity. Despite the lack of any sort of specification, it's easily the most widely supported data format in existence in terms of tools and language support.
I went looking at some of the more niche languages like Prolog, COBOL, RPG, APL, Eiffel, Maple, MATLAB, tcl, and a few others. All of these and more had JSON libraries (most had one baked into the standard library).
The exceptions I found (though I didn't look too far) were: Bash (use jq with it), J (an APL variant), Scratch (not exposed to users, but scratch code itself is encoded in JSON), and Forth (I could find implementations, but it's very hard to pin down forth dialects).
Scientists rename genes because Microsoft Excel reads them as dates (2020)
https://www.reddit.com/r/programming/comments/i57czq/scienti...
I do not miss dealing with csv files in the slightest.
Apple macOS has had to invest enormous amounts...
Pick your distro of Linux has had to invest enormous amounts...
None of them a perfect and any number of valid complaints can be said about any of them. None of the complaints make any of the things useless. Everyone has workarounds.
Hell, JSON has had to invest enormous amounts of effort...
Want to do the same with csv? Good luck. Delimiter? Configurable. Encoding? Configurable. Misplaced comma? No parse in JSON, in csv: might still parse, but is now semantically incorrect and you possibly won’t know until it’s too late, depending on your parser. The list goes on.
The table of contents points to a single Json object that is 20ish gb compressed
https://www.anthem.com/machine-readable-file/search/
All stock libs will fail
You get this backwards. Tabular structured data to store are ubiquitous. Text as a file format is also ubiquitous because it is accessible. The only actual decisions are about whether to encode your variables as rows or columns, what is the delimiter, and other rules such as escaping etc. Vars as columns makes sense because it makes appending easier. There is a bunch of stuff that can be used for delimeters, commas being the most common, none is perfect. But from this point onwards, decisions do not really matter, and "CSV" basically covers everything from now on. "CSV" is basically what comes naturally when you have tabular datasets and want to store them in text. CSV tooling is developed because there is a need for this way of formatting data. Whether CSV is "good" or "ugly" or whatever is irrelevant, handling data is complicated as much as the world itself is. The alternatives are either not structuring/storing the data in a tabular manner, or non-text (eg binary) formats. These alternative exist and are useful in their own right, but don't solve the same problems.
There are at least 3 knobs to turn every time you want to parse a CSV file. There’s reasonably good tooling around this (for example, Python’s CSV module has 8 parser parameters that let you select stuff), but the fact that you have to worry about these details is itself a problem.
You said “handling data is complicated as much as the world itself is”, and I 100% agree. But the really hard part is understanding what the data means, what it describes. Every second spent on figuring out which CSV parsing option I have to change could be better spent actually thinking about the data.
Having header or not should be specified up front and one should not parse some unknown file because that will always end up with failure.
If you have your own serialization and your own parsing working yeah this will simply work.
But then not pushing back to the user some errors and trying to deal with everything is going to be frustrating because amount of edge cases is almost infinite.
Handling random data is hard, saying it is a CSV and trying to support everything that comes with it is hard.
Before you dismiss it as 'not a language, people have argued that it is. And you can definitely program stuff in it, and so that surely makes it a language
ding! ding! ding! winner winner, chicken dinner!
Although BEL would drive me up the wall if I broke out any of my old TTY hardware.
0x1C - File Separator
0x1D - Group Separator
0x1E - Record Separator
0x1F - Unit Separator
So I guess 1F would be the "comma" and 1E would be the "newline."
I am pretty sure you shifted the meaning, the decimal separator is part of the atomic data it does not need a control character.
You would use 1F instead of the comma/semicolon/tab and 1E to split lines (record means line just like in SQL).
You could then use 1D to store multiple CSV tables in a single file.
This would confuse most users of csvs they are not programmers they at most use text editors and Excel.
By the way, RS is decimal 30 (not octal '\030'). In octal, RS is '\036'. For example:
See also https://en.cppreference.com/w/cpp/language/ascii for confirmation.Oh wait.
lol
Microsoft Excel -> application/vnd.ms-excel
CSV is a text format, xls[x], json, and (mostly) xml are not.
So, that means that Excel in those locales uses semicolons as separators rather than the more-frequently-used-in-data commas. Probably not the decision I'd make in retrospect, but not completely stupid.
Even better, the majority of the time I write/read CSV these days I don't need to use a library or tools at all. It'd be overkill. CSV libraries are best saved for when you're dealing with random CSV files (especially from multiple sources) since the library will handle the minor differences/issues that can pop up in the wild.
Yea and it's still a partially-parseable shit show with guessed values. But we can and could have and should have done better by simply defining a format to use.
People keep saying this but RFC 4180 exists.
Couple of standards that I know of that does this, primarily intended for logging:
https://jsonlines.org/
https://clef-json.org/
Really easy to work with in my experience.
Sure some space is usually wasted on keys but compression takes care of that.
CSV has the same failure mode. As does HTML. (But not XML)
And fully fair that you can have similar issues in other formats. I think the complaint here was that it was a bit harder, specifically because it did not trip up any of the loading code. With a big lesson learned that configs should probably either go pascal string style, where they have an expected number of items as the first part of the data, or xml style, where they have a closing tag.
Really, it is always amusing to find how many of the annoying parts of XML turned out to be somewhat more well thought out than people want to admit.
Still, fair point. And is part of why I said it is a flaw, not the flaw. Plenty of other reasons to not like YAML, to me. :D
Still, a fair point.
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!
You don't always get to pick the format in which data is provided to you.
https://github.com/medialab/xan
It seems many possible designs would've avoided the quoting chaos and made parsing sort of trivial.
Even if you wanted them, we use backslashes to escape strings in most common programming languages just fine, the problem CSV is that commas aren't easy to recognize because they might be within a single or double quote string, or might just be a separator.
Can strings in CSV have newlines? I bet parsers disagree since there's no spec really.
If you say those are contrived examples that don't matter any more then you have missed the point and will probably never acknowledge the point and there is no purpose in continuing to try to communicate.
One can only ever remember and type out just so many examples, and one can always contrive some response to any single or finite number of examples, but they are actually infinite, open-ended.
Having a least common denominator that is extremely low that works in all the infinite situations you never even thought of, vs just pretty low and pretty easy to meet in most common situations, is all the difference in the world.
In some alternate worked where this "binary" format caught on it would be a very minor issue that it isn't human readable because everyone has a tool that is better at reading it than humans are. (See the above mentioned non-local property of quotes where you may think you are reading rows but are actually inside a single cell.)
Makes me also wonder if something like CBOR caught on early enough we would just be used to using something like `jq` to read it.
I have in the past does data extractions from systems which really can't serialise properly, where the only option is to concat all the fields with some "unlikely" string like @#~!$ as a separator, then pick it apart later. Ugh.
It's not doing just this, you pick something that's likely not in the data, and then escape things properly. When writing strings you can write a double quote within double quotes with \", and if you mean to type the designated escape character you just write it twice, \\.
The only reason you go for something likely not in the data is to keep things short and readable, but it's not impossible to deal with.
But some people (plenty in this thread) really do think "pick a delimiter that won't be in the data" - and then forget quoting and/or escaping - is a viable solution.
For automated serialization, plain text formats won out, because they are easy to implement a minimal working solution (both import and export) and more importantly, almost all systems agree on what plain text is.
We don't really have Apple formatted text, that will show up as binary on windows. Especially if you are just transferring id's and numbers, those will fall within ascii and that will work even if you are expecting unicode.
I did always use TSV and I think the original use of CSV could have used that.
But TSV would still have many issues.
I mean, you don't usually parallelize reading a file in the first place, only processing what you've already read and parsed. So read each record in one process and then add it to a multiprocessing queue for multiple processes to handle.
And data corruption is data corruption. If a movie I'm watching has a corrupted bit I don't mind a visual glitch and I want it to keep playing. But with a CSV I want to fix the problem, not ignore a record.
Do you really have a use case where reading itself is the performance bottleneck and you need to parallelize reading by starting at different file offsets? I know that multiple processes can read faster from certain high-end SSD's than just one process, but that's a level of performance optimization that is pretty extraordinary. I'm kind of curious what it is!
And the fix to allow parallel reading is pretty trivial: escape new lines so that you can just keep reading until the first unescaped new line and start at that record.
It is particularly helpful if you are distributing work across machines, but even in the single machine case, it's simpler to tell a bunch of workers their offset/limit in a file.
It's not unsolvable, but now you have a more complicated system.
A better file format would not have this problem.
The fix is also trivial (escape new lines into \n or similar) would also make the files easier to view with a text editor.
Because of the "non-local" effect of quotes, you can't just jump into the middle of a file and start reading it, because you can't tell whether you're inside a quoted section or not. If (big if) you know something about the structure of the data, you might be able to guess. So that's why I said "tricky" instead of "impossible".
Contrast to my escaping-only strategy, where you can jump into the middle of a file and fully understand your context by looking one char on either side.
> Do you really have a use case where reading itself is the performance bottleneck and you need to parallelize reading by starting at different file offsets? I know that multiple processes can read faster from certain high-end SSD's than just one process, but that's a level of performance optimization that is pretty extraordinary. I'm kind of curious what it is!
I used to be a data analyst at a management consultancy. A very common scenario would be that I'm handed a multi-gigabyte CSV and told to "import the data". No spec, no schema, no nothing. Data loss or corruption is totally unacceptable, because we were highly risk-sensitive. So step 1 is to go through the whole thing trying to determine field types by testing them. Does column 3 always parse as a timestamp? Great, we'll call it a timestamp. That kind of thing. In that case, it's great to be able to parallelise reading.
> And data corruption is data corruption
Agreed, but I prefer data corruption which messes up one field, not data corruption which makes my importer sit there for 5 minutes thinking the whole file is a 10GB string value and then throw "EOF in quoted field".
For my own parser, I made everything `\` escaped: outside of a quote or double-quote delimited string, any character prefixed with a `\` is read verbatim. There are no special exceptions resulting in `\,` producing a comma while `\a` produces `\a`. This makes it a good rule, because it is only one rule with no exceptions.
Regarding quoting and escaping, there are two options that make sense to me - either use quoting, in which case quotes are self-escaped and that's that; or use escaping, in which case quotes aren't necessary at all.
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:
Another advantage of JSONL is that it's easier to chunk into smaller files.This whole thread is an uninformed rehash of bad ideas.
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.
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.
I am saying JSONL is a lower bound format, if you can use something better you should. Data interchange, archiving, transmission, etc. It shouldn't be repeatedly queried.
Parquet, Arrow, sqlite, etc are all better formats.
This is what keeps me coming back.
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"
But even ergonomically, in python, can read a csv like:
which imo is not less ergonomic thanWay easier to parse
sqlite3 gets it right.
Less likely to appear in normal data. Of course you have to escape it but at the very least the data looks less noisy.
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.
Maybe someone should type this up into a .psv file format (maybe it already exists).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.
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... :)
https://news.ycombinator.com/item?id=28221654
Most reasonably large CSV files will have issues parsing on another system.
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.
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.
Now if they would just also allow pasting CSV data as “source” it would be great.
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.
It creates a green/white coloured pivot table -- do you also observe that?
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.
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.)
I see what you did there.
That being said I noticed .parquet as an export format option on Shopify recently and an hopeful more providers offer the choice.
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
I looked at the RFC. What is controversial about it?
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.
Yeah, you are spot on with this one (cries in Czech, which used to be encoded in several various ways).
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.
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.
1. https://github.com/manifold-systems/manifold/tree/master/man...
Compare this CSV
To the directly-equivalent JSON 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. 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.
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.
A but unfair to compare CSV without parser library to JSON with 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.
Unless you need appendability, but then you should probably just use NDJSON/JSONL for a lot of cases.
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.
The python3 csv module basically does the job.
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.
csvtk: https://bioinf.shenwei.me/csvtk/
gawk: https://www.gnu.org/software/gawk/manual/html_node/Comma-Sep...
awk: https://github.com/onetrueawk/awk?tab=readme-ov-file#csv
csvquote: https://github.com/dbro/csvquote
Especially for use with existing shell text processing tools, eg. cut, sort, wc, etc.
For CLI tools, I’m also a big fan of Miller (https://github.com/johnkerl/miller) to filter/modify CSV and other data sources.
But of course, I am partial ;)
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
"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.
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.
The intention-based philosophy of all this makes a lot of sense, was eye opening, and I agree it should be the first approach. Unfortunately after considering our use cases, we quickly realized that we'd have no way of knowing how customers intend to use the csv exports they've requested - we've talked to some of them and it's a mix. We could approach things case by case but we really just want a setup which works well 99% of the time and mitigates known risk. We settled on the prefixing approach and have yet to receive any complaints about it, specifically using a space character with the mind that something unobtrusive (eg. easily strippable) but also visible, would be best - to avoid quirks stemming from something completely hidden.
Thank again for your writing and thoughts, like I said above I haven't found much else of quality on the topic.
Or you could just use the ISO standard .xlsx, which is a widely supported format that is not Excel specific but has first class support in Excel.
That's honestly a good solution and the end users prefer it anyway.
Then, the user doesn’t have Excel has the default program for opening the file and has to jump through a couple safety hoops
Nah, they just quickly learn to rename it .csv or .xls and excel will open it
I need to interface with a lot of non-technical people who exclusively use Excel. I give them .xlsx files. It's just as easy to export .xlsx as it is to export .CSV and my customers are happy.
If xlsx works for all your use cases that's great, a much better solution that trying to sidestep these issues by lightly modifying the data. It's not an option for us, and (I'd imagine) a large contingent of export tools which can't make assumptions about downstream usage.
I responded that this was Excel's problem, not ours, and that nobody would assign a CVE to our product for such a "vulnerability". How naive I was! They forwarded me several such CVEs assigned to products that create CSVs that are "unsafe" for Excel.
Terrible precedent. Ridiculous security theater.
I agree with the characterization ("security theater") of these bug reports. The problem is that the intentions of these reports don't make the potential risk less real, depending on the setting, and I worry that the "You're just looking for attention" reaction (a very fair one!) leads to a concerning downplaying of this issue across the web.
As a library author, I agree this very well may not be something that needs to be addressed. But as someone working in a company responsible for customers, employees, and their sensitive information, disregarding this issue disregards the reality of the tools these people will invariably use, downstream of software we _are_ responsible for. Aiming to make this downstream activity as safe as possible seems like a worthy goal.
For a long time I was very wary of CSV until I learnt Python and started using it's excellent csv standard library module.
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.