Windward Software Wiki

User Tools

Site Tools

Trace:
training:excel_import_leading_zeroes_2016

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision		Previous revision
training:excel_import_leading_zeroes_2016 [2018/01/23 16:43 (6 years ago)] – created sjacksontraining:excel_import_leading_zeroes_2016 [2018/01/23 16:47 (6 years ago)] (current) – sjackson
Line 1: Line 1:
 +====== 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. 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. 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.
 {{:training:excel:new_excel_wizard_1.jpg?nolink|}} {{:training:excel:new_excel_wizard_1.jpg?nolink|}}
-{{:training:excel:new_excel_wizard_2.jpg?nolink|}} 
    
  
Line 10: Line 11:
  
 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 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.
 +{{:training:excel:new_excel_wizard_2.jpg?nolink|}}
  
 The only method that works is to use Excel’s Legacy Data Load.   The only method that works is to use Excel’s Legacy Data Load.  
training/excel_import_leading_zeroes_2016.txt · Last modified: 2018/01/23 16:47 (6 years ago) by sjackson