Hacker News Reader

The specific use case the OP author was focusing on was CSV. (A format which has no place to signal the encoding inline). They noted that, to this day, Windows Excel will output CSV in Win-1252. (And the user doing the CSV export has probably never even heard of encodings).

If you assume UTF-8, you will have corrupted text.

I agree that I'm mad about Excel outputting Win-1252 CSV by default.

More pressure on M$ - faster the change. Also, almost any software nowadays assuming UTF8 for plaintext anyway

You are suggesting that if software developers willfully refuse to implement measures to detect CP-1252 in CSVs, instead insisting on assumign UTF-8 even though they know that will result in lots of corruption with data from the most popular producer of CSVs -- you are suggesting that will be pressure on MS to make it output in UTF-8 by default instead?

If the world worked that way, it would be a very different world than the one we've got.

If you think you can detect only cp-1252 i have news for you. This is 1-byte encoding, it can't fit much. So, be prepared for whole zoo of other 1-byte encodings from that era (e.g. cyrillic letters - welcome to cp-1251, where everything above first 128 chars have totally different meaning). Writing encoding detector is not easy at all. Chance of guessing wrong is high. I'm glad most of the world (but not excel) moved on away from that can of worms

> You are suggesting that if software developers willfully refuse to implement

it will happen anyway, sooner or later

> will result in lots of corruption

there are lots of corruption anyway, e.g euro sign, some european letters, whole other alphabets like cyrillic one). The most dangerous are subtle ones like german ẞ)

P.S. I don't have office installed to check, but online version exports non cp-1252 chars as "?" . So nice

P.P.S. Apple Numbers offers choice of encoding on export, with UTF-8 default. Google Sheets exports as UTF-8 without choice

OP is in fact about doing your best to detect encoding without proper tagging. It is not perfect and you won't have 100% accuracy. But it can get pretty decent (OP is literally about the techniques used to do so, statistically etc), and is necessary because of the actual world we live in. If just refusing to do it would get MS to export in UTF-8 by default that would of course have happened a long time ago!

And that's far from the only thing Excel does quietly. It also changes comma's to semi-colons based on system locale. It's braindead.

Imho Presume 8 bit, encapsulting 7bit usacsii. That includes utf8 and many many other encodings.

Don't interpret user supplied strings at all. Defines max lengths as byte lengths.

Remain agnostic of encoding. Especially in libraries.

It's easier than people think it is thanks to some very clever people's work a long time ago.

Programming languages have lumbered slowly towards UTF-8 by default but from time to time you find an environment with a corrupted character encoding.

I worked at an AI company that was ahead of its time (actually I worked at a few of those) where the data scientists had a special talent for finding Docker images with strange configurations so all the time I'd find out one particular container was running a Hungarian or other wrong charset.

(And that's the problem with Docker... People give up on being in control of their environment and just throw in five different kitchen sinks and it works... Sometimes)

If csv files bring criminal liability then I am guilty.

Sidenote: this particular criminal conspiracy is open to potential new members. Please join the Committee To Keep Csv Evil: https://discord.gg/uqu4BkNP5G

Jokes aside, talking about the future is grand but the problem is that data was written in the past and we need to read it in the present. That means that you do have to detect encoding and you can't just decide that the world runs on UTF-8. Even today, mainland China does not use UTF-8 and is not, as far as I know, in the process of switching.

I understand UTF-8 is mostly fine even for east asian languages though - and bytes are cheap

I explained how old files could be read.

Let me reframe it as a Schelling Point [1] - the uncoordinated coordination problem.

You arrange to meet your file on a certain day in New York, no place or time were mentioned. When and where will you go? It seems impossible.

But perhaps you go at noon, to the UTF-8 Building in midtown Manhattan. Are you there now?

[1] https://en.wikipedia.org/wiki/Focal_point_(game_theory)

I'm sorry, quite right - you did. Using the computer locale.

The trouble is that people transmit data from one computer to another and so from one locale to another. And sadly, they do not always set the character encoding header correctly, if they even know what that is.

I mean take csvbase's case. It has to accept csv files from anyone. And christ preserve me, they aren't going to label them as "Win-1252" or "UTF-16" or whatever.

There is no alternative but statistical detection. And there is good evidence that this solution is fairly satisfactory, because millions of computers are using it right now! csvbase uploads run into more problems with trailing commas than with character detection getting it wrong at this point, that is your "Schelling Point" I'm afraid!

So trailing commas are another failure. That issue is mildly entangled with headers. Perhaps start making a list...

HTTP actually does quite a good job of providing headers containing MIME type and encoding. There is a little work to get the default (e.g. HTML and XML are different), and decide on the case where the XML payload encoding is different to the HTTP transport encoding (e.g. perhaps XML parsers need a way to override the embedded header).

So we end up at another plausible future-directed design decision: computer-computer communication should use HTTP. I think many systems have ended up there already, perhaps prompted by the issues we have discussed.

Moral: good specs attract usage; bad, incomprehensible or inconsistently implemented specs fade away.

Old CSV files bring no stigma.

There is no CSV specification. That does bring opprobrium. RFC4180 is from 2005, long after Unicode and XML, so people should have known better. The absence of a standard points to disagreement, perhaps commercial disagreement, but IETF should be independent, is it not?

That failure to standardize encoding, and other issues (headers, etc.), has wasted an enormous amount of time for many creative hackers, who could have been producing value, rather than banging their head against a vague assembly of pseudo-spec-illusions. Me included, sadly.

You're blaming the lack of a spec for all the wasted time but that's not the cause.

The cause is that CSV is popular and is popular because it is incompletely defined. (See also: HTML, RSS.)

Making a CSV spec post-hoc solves nothing, as others here have pointed out, because there is already an "installed base" so to speak. If anything it's worse because it might mislead some people into thinking they can write to the spec and handle any CSV file.

The right move, if you want a nicely precise and strict spec, is to admit it's a new thing and give it a new name, maybe CSVS or something like that.

But good luck making it popular - there are plenty of CSV libraries out there that handle most CSV files well enough, just as there is tons of software that handles HTML and RSS well enough (which is why I am a fan of both those formats).

The only argument I’ll present for giving over CSV to those who want to flail at the idea of standardizing it: the name implies too much standardization already. Why “C”SV? Most CSV processing tools accept a delineator, right? They are just whatever separated values, use semicolons or tabs if you want.

> The cause is that CSV is popular and is popular because it is incompletely defined. (See also: HTML, RSS.)

This is a surprisingly deep thought. I'm going to need a couple of days to reflect on this...

> China does not use UTF-8 and is not, as far as I know, in the process of switching.

That’s … not true? Most Chinese software and websites are utf8 by default, and it’s been that way for a while. GBK and her sisters might linger around in legacy formats, but UTF-8 has certainly reached the point of mass adoption from what I can tell.

> We know CJK languages need UTF-16 for compression.

My understanding is that it is for the opposite of compression: it saves memory when uncompressed versus the UTF-8 surrogates needing more bytes. My understanding is that UTF-8 surrogates compress pretty well as they have common patterns that form dictionary "words" just as easily anything else. UTF-8 seems to be winning in the long run for even CJK and astral plane languages on disk and the operating systems and applications that were preferring UTF-16 in memory are mostly only doing so out of backwards compatibility and are themselves often using more UTF-8 buffers internally as those reflect the files at rest.

(.NET has a backwards compatibility based on using UTF-16 codepoint strings by default but has more and more UTF-8 only pathways and has some interesting compile time options now to use UTF-8 only today. Python 3 made the choice that UTF-8 was the only string format to support, even with input from CJK communities. UTF-8 really does seem to be slowly winning everything.)

> JSON and CSV are woefully neglectful,

As the article also points out, JSON probably got it right: UTF-8 only and BOM is an error (because UTF-8) (but parsers are allowed to gently ignore that error if they wish). https://www.rfc-editor.org/rfc/rfc8259#section-8.1

That seems to be the way forward for new text-based formats that only care about backward compatibility with low byte ASCII: UTF-8 only, no BOM. UTF-8 (unlike UTF-16 and missing reservations for some of its needed surrogates) is infinitely expandable if we ever do find a reason to extend past the "astral plane".

(Anyone still working in CSV by choice is maybe guilty of criminal liability though. I still think the best thing Excel could do to help murder CSV is give us a file extension to force Excel to open a JSON file, like .XLJSON. Every time I've resorted to CSV has been because "the user needs to double click the file and open in Excel". Excel has great JSON support, it just won't let you double click a file for it, which is the only problem, because no business executive wants the training on "Data > From JSON" no matter how prominent in the ribbon that tool is.)

> When the whole world looks to the future, Microsoft will follow.

That ship is turning slowly. Windows backward compatibility guarantees likely mean that Windows will always have some UTF-16, but the terminals in Windows now correctly default to UTF-8 (since Windows 10) and even .NET with its compatibility decrees is more "UTF-8 native" than ever (especially when compiling for running on Linux, which is several layers of surprise for anyone that was around in the era where Microsoft picked UCS-2 as its one true format in the first place).

You can fit Japanese comfortably in a 16 bit charset but Chinese needs more than that.

My take though is that CSV is not a good thing because the format isn't completely standardized, you just can't count that people did the right thing with escaping, whether a particular column is intended to be handled as strings or numeric values, etc.

Where I work we publish data files in various formats, I'm planning on making a Jupyter notebook to show people how to process our data with Pandas -- one critical point is that I'm going to use one of the commercial statistics data formats (like Stata) because I can load the data right the first time and not look back. (e.g. CSV = good because it is "open" is wrong)

If I am exporting files for Excel users I export an Excel file. Good Excel output libraries have been around for at least 20 years and even if you don't have fun with formulas, formatting and all that it is easy to make a file that people will load right the first time and every time.

> Good Excel output libraries have been around for at least 20 years

I wish that were the case more often. Depends on your ecosystem, of course.

For instance, I've yet to find a good XLSX library for JS that works well "idempotently" (in the browser as well as Node/etc). Every one I tried either had native dependencies and couldn't run in-browser or had a cost (time, money, size) I couldn't budget for at the time.

I have found some XLS libraries for JS that were extremely "mid", but outputting XLS is nearly as bad as CSV in 2024. (Including all the huge messy legacy of character set Encoding problems.)

The best and worst thing about CSV is that it seems "low overhead": it seems really cheap to output. ("How hard can it be, just ','.join(records)?" has been the pit so many of us fall into over and over again and sometimes never come out.) In terms of low overhead: in a world where all my APIs are already talking JSON if I can wrap an existing HTTP API with just two extra headers to get "free" Excel files for my users, that could be a beautiful world:

  Content-Type: application/json+excel
  Content-Disposition: attachment; filename="BusinessUsersDumbReportRequest.xljson"

All the pieces are already there. If you could teach every user to use "Data > From JSON" we could maybe have nice things today instead of yet another CSV export dump. We just need someone on the Excel team to greenlight a "double to click to open an .XLJSON file" feature.

Why not just add a file association for plain .json files?

First and foremost to avoid accidents: We don't want a misconfigured HTTP website accidentally opening a new Excel window for every fetch/XHR call or to have to fight Excel defaults to get JSON to open up in our IDE or Dev Tools of choice. We don't want random shell scripts accidentally curl-ing things to Excel. Things like that.

Secondly for "ownership" reasons: We don't want to give non-developers the mistaken impression that Excel "owns" JSON and that it is a Microsoft format. I've had people tell me that CSV must be a Microsoft format because you can double click them in Excel and they show an Excel-like icon (in some ways it has been too long since Lotus existed and Excel was in the "we'll take all of our competitors' file associations too" era). On the one hand it might be nice to blame all of CSV's problems on Microsoft and Excel if that were actually the case, but on the other hand it also confuses people as to the real/valid uses of the format. Unfortunately, too, that transitive relationship goes both ways and I've heard second hand that CSV files are among the reasons the Excel team hopes to never add another file type association again because supposedly they get far too many support requests for CSV file problems that maybe shouldn't be their job to deal with.

A separate file association adds some intent of "this file was actually meant to be opened in Excel and hopefully the developer actually tested it some".

> Requiring a UTF-8 BOM just destroys the happy planned property that ASCII-is-UTF8. Why spoil that good work?

So that you know you are dealing with UTF-8. Assuming ASCII only works if you are only dealing with English texts and data.

Who is assuming ASCII?

Much code incorrectly assumes that ASCII can be rendered in any code page.

The BOM only tells you that your are not dealing with ASCII. You could encounter anything after it.