I just started using Office 365 at work, and almost immediately I hit a hilariously bad problem in the core simple “import CSV” functionality. Basically, the Excel 2016 that I got (it is 2016, not the latest 365 that my home machine all use) does not understand Windows line endings or decimal numbers.
Here is what it looks like, importing a very standard CSV from some experiments with Simics:
- Windows-standard CR-LF line endings are interpreted as two lines.
- Decimal numbers in US dot-based format in the fourth column get processed without the dot… leading to some too-large-to-use values being produced!
It is completely incomprehensible that Microsoft ships something this broken! On the plus side, this new importer actually understands commas… while the old one defaulted to tabs. But that is only a small help when the data gets butchered.
Fixing It: Legacy Importer
Fortunately, there is a fix. The old importer can be revived by setting the option to allow legacy importers:
Once the legacy importer is enabled, you then have to find the right menu option to actually use it. It does not in any way replace the default button for csv/text, but rather it is buried two levels down. First, click “Get Data”, then “Legacy Wizards”, and finally “From Text (Legacy)”:
This brings up the old importer from previous versions of Excel, and this can actually get the job done:
Ah, that works.
Seems to ring a bell… doesn’t Excel adapt to your locale, in this case assuming Swedish conventions for decimal numbers? I think I’ve had that problem myself at some time.
The Excel locale handling means that I cannot just open a CSV as an Excel file – if I do that, I get all the data in a single column. But the importer should be able to deal with exactly those kinds of issues.
Excel has always been painfully limited for CSV (and semi-colon or tab delimited value) files. If you have a file that uses decimal commas, common in many countries, you need to change your Windows locale before you can import it!
Use LibreOffice instead. It is not better than MS Office in all aspects, but it totally trounces Excel here.