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.