Introduction

Microsoft Excel automatically converts long strings of numbers into scientific notation. This feature is problematic when working with UPCs and GTINs as the original value can be distorted during data uploads. This issue is particularly problematic with CSV files, as cell formatting is not maintained between instances of a CSV—once a CSV file is closed, all cell formatting is reset.

Project

Recently, I took on the task of correcting ~280,000 UPC values on assembly item records in NetSuite. Many of these UPCs were populated in the following incorrect ways during implementation:

• Formula mis-input: #N/A

• Scientific notation: 6.54691E+11

• Leading apostrophe: '654690840981

• Rounded value: 654691000000

In each erroneous instance, the NetSuite order processing script could not match the provided value against external systems. After compiling a list of all valid UPCs and running some tests, I learned the following:

• The UPC column in the CSV file must be formatted as a number in Excel:

• If the CSV file is closed, the UPC column loses its number formatting.

• Reopening and saving the CSV without reformatting the UPC column reverts it to scientific notation.

In this example, column B is formatted as a number and the CSV is saved and ready for upload:

If the file is closed and reopened, Column B reverts to scientific notation. Note that the field value is still a valid UPC, however, saving and uploading this file will upload the scientific notation value in field B2 as the product's UPC:

Additionally, in order to ensure the #N/A fields were overwritten with blank data, I checked the 'Overwrite Missing Fields' box under Advanced Options in NetSuite:

This company only owns ~100,000 UPCs but catalogs ~280,000 products. Thus, the system only contained valid UPCs and blank fields after the update.

Last Updated: 6/22/23