Hacker News Reader

>Excel tries to interpret fields with digits as numbers, stripping off leading 0's, etc. (think serial numbers or whatever with leading 0's)

Fyi for those unaware... many other tools other than MS Excel also remove leading zeros and interpret as number.

Examples of users asking questions on how to preserve them in other software:

- Google Sheets issue with leading zeros: https://webapps.stackexchange.com/questions/120835/importdat...

- LibreOffice: https://ask.libreoffice.org/en/question/246295/how-can-i-imp...

- Python Pandas import csv issue with leading zeros: https://stackoverflow.com/questions/13250046/how-to-keep-lea...

- R software import csv issue with leading zeros: https://stackoverflow.com/questions/31411119/r-reading-in-cs...

- Tableau: https://community.tableau.com/s/question/0D54T00000C5i2iSAB/...

Everybody complains about the misinterpretation of leading zeros and blame Microsoft Excel but for some reason, the programmers of the other non-Excel software have deliberately coded the same default behavior.

> for some reason

The reason is Excel.

>The reason is Excel.

I've thought about that and it seems plausible but I'm not fully convinced.

As another example, Google Sheets does not blindly copy Excel behavior of converting scientific gene names into a date. Excerpt from paper[1]:

> To date, there is no way to permanently deactivate automatic conversion to dates in MS Excel and other spreadsheet software such as LibreOffice Calc or Apache OpenOffice Calc. We note, however, that the spreadsheet program Google Sheets did not convert any gene names to dates or numbers when typed or pasted; notably, when these sheets were later reopened with Excel, LibreOffice Calc or OpenOffice Calc, gene symbols such as SEPT1 and MARCH1 were protected from date conversion.

There are several Excel behaviors that alternative software don't copy. But for some reason, the leading zero removal in csv import has stuck as a default even though it has repeated complaints and generates very similar Q&A in other software packages. (Why copy a behavior that makes you have to answer identical frustrated questions from users wanting to preserve leading zeros?!? Do programmers of LibreOffice/Google/Pandas all like inflicting pain on their users?!?)

Maybe another reason is that other industries or workflows depend on leading zero removal but we never hear from them praising it. Therefore, we're distorted by a biased subset that only complains loudly about it.

[1] https://genomebiology.biomedcentral.com/articles/10.1186/s13...

So, if all your friends jump off a cliff are you going to follow them?

> Excel saves files in different character sets on MacOS and Windows.

Doesn't excel also use different separators depending on the locale? I recall once it would create semicolon-separated files if the locale uses `,` as decimal separator, and will put commas in your file in that case.

Yes it does. Excel does not generate valid CSV (as per RFC4180) on e.g. German computers.

This is why I switched to TSV. The risks are too high.

(Yes, I know about ASCII delimiters, but IMHO /t and /n are good enough for numbers-first files, especially considering how most keyboards don't feature the aforementioned delimiters, and hardly anyone has even heard of them.)

https://ronaldduncan.wordpress.com/2009/10/31/text-file-form...

With commas one uses "CSV" as the file extension, and with tabs presumably TSV.

Is there a file extension and MIME type for ASCII-delimited files? ASV?

CSVs are ascii-delimited. Do you mean files using US as field separator?

.gop?

Yes. If I want to use the 0x1c to 0x1f in a delimited file, how would I signal by file extension (and/or MIME type) that is what is happening?

TSV is a lot better, but still isn't great for tabs in fields (rare) and line breaks in fields (more common). One nice characteristic of tab-delimited is you can typically copy all the text from a text editor and paste right into a spreadsheet.

I'm fixing this with a smooth upgrade to QTSV:

https://news.ycombinator.com/edit?id=25022836

Each cell is QSN, which can contain tabs and newlines:

http://www.oilshell.org/release/latest/doc/qsn.html

The nice thing about this is that you can import QTSV into ANY program that accepts TSV. A QTSV file is syntactically valid TSV.

You may have to do an extra step to dequote certain fields, but that's better than a completely new format that doesn't import at all.

Let me know if you want to help :)

>Excel neither reads nor writes UTF-8 CSV files without jumping through hoops.

It's literally as simple as selecting "csv utf-8" when saving. When opening excel also handles it automatically if it has BOM.

It shouldn't need a BOM. Besides, UTF-8 doesn't need a BOM at all.

It doesn't, but only if you assume all text is utf-8. You can't do that on windows because there's plenty of legacy files that use encoding like windows-1252 which are incompatible with utf-8.

Start by converting them to UTF-8. As a metaphor : would you want to work with files that can only be opened properly in, say, Lotus 1-2-3 ?

The time to drop that support is long passed. UTF-8 has won, the value of pandering to these ancient codepages versus the cost of choking and displaying mojibake just because someone dared to add a kanji character to a CSV should be clear by now.

And what happens to all the legacy office versions out there, or all the legacy csvs floating around? What you described works fine on a rolling release distro, but would be unacceptable for enterprise software. The current approach (opt in utf-8 with BOM to disambiguate between utf-8 and non-utf8) files is the best option that doesn't piss off their customers.

UTF-8 has won on the web and file storage. For programming (C#, probably Java too, the Windows API) it’s UTF-16. Good point is it’s an Unicode encoding, bad point is that is variable-size, which look fixed-size for common characters.

> For programming (C#, probably Java too, the Windows API) it’s UTF-16.

For programming it's UTF8, except for Windows-centric developers (and Microsoft technologies).

Java uses WTF16 internally but rarely assumes an external charset, and when it does, older API tend to use the default charset (which is generally ascii-compatible at best: on western windows it's commonly windows-1252, though it's been UTF-8 for years on most unices). Newer APIs like Files.newBufferedReader(Path) straight go with UTF-8.

It’s a relatively new feature (and it outputs UTF-8-BOM). If you work with one-time purchase versions, it’s only available in Office 2019. It’s been in Office 365 for a bit longer. But if you (or whoever sends you the CSV file) use older one-time-purchase versions of Excel, the option is not available. Even if you do have the latest version of Excel, the old option is still there, so someone might save a file as Windows-XXXX accidentally.

Those are hoops that IME you can't expect people not in your organization to jump through.

The worst sin of Excel is the import dialog, which doesn’t scroll on MacOS and if updated since the last revision in 1997 would eliminate most issues.

> Most CSV libraries are extremely buggy

Examples? (Of libraries and bugs)

op is being very hyperbolic. most widely used and supported csv libraries are not nearly as deficient as op claims.

golang builtin doesnt handle UTF BOM well . have to preprocess the io.Reader

Seems reasonable to me: this way, you don't have to duplicate the BOM stripping in the decoders of every single format you want to support.

It's also more general as there are other commonly-needed transformations that have an even weaker case for explicit support in the CSV library (charset conversion, dropping other bogus bytes, ...).

it's a stark difference compared to, say, https://www.papaparse.com/ in javascript which pretty much does anything it can to parse a file

Yes, I experienced a lot of pain while working with CSVs in UTF-16, turns out some libraries can't handle it.

Also, CSVs don't really scale because parsing generic CSV that may have quote and line-end inside a field is inherently single-threaded, so once you get to multi-gigabyte CSVs, and things start to go slow, there's nothing you can do.

Good point about Excel, there's not much point discussing CSVs and leaving Excel out of the discussion

> CSVs are the worst and only option for data exchange in 99% of the cases.

If it helps, for database data (eg not spreadsheet / excel), SQLite is a good alternative single-file format.

So true!

I wrote a few parsing tools for a client, and pretty much every problem we had was due to BOM in excel-edited CSVs.

I don’t know how many times I’ve dealt with broken .CSV files that someone ram through Excel or Access

UTF-8 or GTFO.

(Without BOM, you heretic.)

Please tell me that you're talking about Excel 2003 ?

Microsoft Office worships the concept of backwards compatibility.

Outlook renders HTML emails with a buggy and incomplete implementation of HTML 3.2 that has had approximately two changes in the last twenty years (one was to support high DPI, the other I can’t remember). There was that time they switched to using Trident (IE) in Outlook 2000–2003, but they switched back to MSO (Word) in 2007 for terrible reasons (“uh… security, yes, security”; their explanation was atrocious and never made a lick of sense to me; the original article talking about it went down a few years ago, if I recall correctly from last time I tried to find it).

Excel sticks with known-bad and known-stupid behaviour because people depend on it. I can’t think of an example off-hand, but they literally have help pages saying “if you encounter this specific bug, here’s how to work around it”, because writing that help page was easier and/or safer than actually fixing the bug.

A few months ago, scientists even renamed some human genes to stop MS Excel from misreading them as dates: https://news.ycombinator.com/item?id=24070385

Backwards compatibility doesn't have to mean to give up on forward compatibility !

(How do browsers deal with that ?)

Excel even implemented lotus 1-2-3 bugs to ensure compatibility: see 1900-02-29 https://answers.microsoft.com/en-us/msoffice/forum/msoffice_...

There's a spec?

If nothing else there is RFC4180 (which is the spec that backs the mime type).

I’m just here like “there is a spec for CSV?”