Hacker News Reader

Comment by Qem | Hacker News Reader

  9. Excel hates CSV
  It clearly means CSV must be doing something right.

This is one area where LibreOffice Calc shines in comparison to Excel. Importing CSVs is much more convenient.

View on HN

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.

Honestly I’m happier when Excel doesn’t try to convert anything. too much bugginess.

Especially gene names. It was so bad that the scientific community renamed the genes in question rather than suffering from the same horror endlessly.

[0] https://www.theverge.com/2020/8/6/21355674/human-genes-renam...

Just wrong!!

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've noticed recently, they ask you about some of the transformations with a popup instead of automatically doing them when you open csv files.

I usually have the opposite problem. There's even a joke about it:

How is Excel like an Incel? Both of them think everything is a date.

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.

Your comma isn't my comma. French systems use the comma as a decimal point for numbers and we use semicolons to separate fields in CSV files.

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.

I don't care what Excel calls it. As I said, if I name a file .txt but stuff it with binary data, it's not a text file.

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.

If it only was that easy. Experience has shown that the only reliable way is to run heuristics against the first few lines of the file.

There are office programs that save CSV with the proper comma delimiter regardless of the locale.

There are people who run non-local locales for various good reasons.

There are technically savvy people who have to deal with CSV shenanigans and can and will send it with the proper comma delimiter.

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 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.

Search and replace + text to columns after the fact works fine.

Reply on news.ycombinator.com