User Tools

Site Tools


training:excel_import_leading_zeroes_2016

Preventing Leading 0's From Being Dropped When Opening a CSV File in Excel 2016 or higher

All should know that a CSV file is never to be opened in Excel because Excel butchers the Part Numbers and Barcodes by stripping off the leading zeros from anything that looks like a number. It also changes a perfectly good barcode into scientific notation like 1.2E+11, rendering it more than useless. The only solution is to data load it into Excel if it needs to be edited, unless you edit it instead in Microsoft Notepad.

For those of you who have attempted to data load a CSV into the latest version of Excel with the Data Menu > Get Data From Text/CSV, you will have seen a new data importer that looks more like one might see inside of Microsoft Query.

The first method presented to the importer is to auto-determine the columns by the first 200 rows. It does not work because it auto-strips off all of the leading zeros from the barcodes.

The second method presented is to change from ‘auto-determine’ to ‘manually determine’. It still not work even when you tell it to treat a column as a Text column because if it finds a double-quote in a description line (as an inch symbol), then 50 lines later if it finds another double-quote, it flat-out drops everything between the two double-quote appearances. Or, it will attempt to stuff the 50 lines into a single Excel cell. Loading something like a Polaris Price file is disastrous as thousands of lines are dropped out of the file.

The only method that works is to use Excel’s Legacy Data Load. It can be turned on in File Menu > Options > Data > “Show legacy data import wizards” > “From Text (Legacy)”

Then on the Data Menu, instead of choosing “From Text/CSV”, choose “Get Data” > “Legacy Wizards” > “From Text (Legacy)” Then, manually set any of the columns to Text, which are not really numbers (especially the Barcode, Part №, and Supplier Part № fields). This reliably imports the data without butchering or dropping any.

training/excel_import_leading_zeroes_2016.txt · Last modified: 2018/01/23 16:47 (6 years ago) by sjackson