Better text conversion options coming to Excel
Microsoft is addressing the long-standing problems with Excel 365 importing text files (.CSV) by adding some options to prevent common conversion problems. The ‘Automatic Data Conversion’ choices are welcome, but you’re still better using other, safer, Excel import options.
Opening .csv files directly in Excel is done a lot. Microsoft encourages it by associating .csv so those files are opened directly in Excel by default. That’s quick and easy, really too easy because that kind of direct opening doesn’t always work very well.
Excel makes a lot of assumptions about the text in each cell, converting into various Excel data types without warning and not always getting it right. Genetic researchers have been caught too many times because the names of some genes are mistaken for dates or very large numbers.
Now Microsoft are addressing the problem directly, even though there are already better solutions, see below.
Automatic Data Conversion
Excel 365 is getting some options to prevent common text conversion problems. Go to File | Options | Advanced | Automatic Data Conversion.
When entering, pasting or loading text, Excel will perform these conversions by default
- Remove leading zeros and convert to a number
- Keep first 15 digits of long numbers and display in scientific notation
- Convert digits surrounding the letter ‘E’ to a number in scientific notation.
When loading a .csv file or similar file, notify me of any automatic data conversions.
All these options default ON which is the equivalent of the way Excel already works. What’s changing is that users have the option to turn some of the automatic conversions OFF.
Geneticists could avoid some of the common gene code import problems by turning OFF the ‘Remove leading zeros …’ and ‘Convert digits surrounding letter E’ options. However that won’t fix all the genetic conversion problems (e.g MARCH1 would still convert to a date).
Who gets it?
Automatic Data Conversion options are first in Excel 365 for Windows, Insiders Version v 2207 build 15427.20000 then later in preview and public versions.
Better choices for importing .CSV into Excel
These new options are nice to have but aren’t enough to solve the CSV to Excel problems.
Opening small .CSV text direct in Excel is fine because you can easily see the results. But as the file size grows (more row and columns) it’s easy to miss automatic conversion errors.
It’s MUCH better to use Excel’s data import options to control the incoming text and how it’s transformed into cells.
There are two ways. In modern Excel there’s ‘Get and Transform’ which has sophisticated conversion and transformation options.
The Text Import Wizard (in Excel for Windows and Mac) lets you Older Excel’s have the Text Import Wizard which lets you specify the data type for each column, overriding Microsoft’s defaults. In particular, choose Text or a date format.
Gene names changed to stop confusing Excel
Excel’s General problem that messes up what you type
Great text importing tricks for Excel
Import PDF’s into Excel and refresh later
Excel’s much better data import features