Exported Tracking Number in CSV Shows Zeroes After the 15th Digit

Release Date: 8/23/19
Version: All

Q - When I go to COP -> Processing -> Print Pick/Pack Ticket & Lbls -> Shipping -> Manifest order Status Report, and I choose to export to CSV file, the CSV file has a tracking number column. When I open the CSV file, the tracking number will be displayed in scientific notation format.  The when I choose to convert the cell phone to "string," the numbers after the 15th digit become zero. See sample screen below:


What do I do to preserve the original tracking number format?

A - These are common problems with Excel when trying to open a CSV file:
  • The lead zero of the item number, UPC code gets dropped.
  • A long numeric number is converted to scientific notation format.
In the above example, a 20-digit numeric tracking number in the CSV file is being converted by Excel to scientific notation format automatically. But Excel does not have the precision to preserve all 20 digits in the scientific notation format.  As a result, when you convert it back to, say, string format, the last 5 digits become zero.

To resolve this type of issue, you can go to Elliott Global Setup -> System -> Comp. Specialized Control. and answer "Y" to the following flag: 

Add = Sign to Following Fields When Export CSV?
Item Number, UPC Code, 25 Char PO Number,
Tracking Number

By setting this flag to "Y," the 20-digit tracking number will be exported as something like:
='12345678901234567890'
Excel will see the equal sign and hence will not perform any "smart" reformatting.  As a result, this preserves the original 20-digit tracking number.  

This principle also applies to dropping off the lead zero.  For example, if you have an item number like"'012345" and you export it to a CSV file, Excel will open it and display it as "12345," dropping the lead zero.  By setting the above flag to "Y," the item number will be exported as ="012345" and thus preserve the original value when bringing up the CSV file in Excel.

Please see the following KB article for more details:

EMK

Feedback and Knowledge Base