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

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


    • Related Articles

    • Feature - Add Tracking Number to Shipped Data Export

      Release Date: 07/09/2021 Version: 8.5 & Up In COP -> Utilities -> Sales Order Export -> Shipped Data Export, the tracking number is added to the report. To keep the reports within the same space, the Misc. Charges & Sales Tax columns are taken out ...

    • Shipment Confirmation Email Shows FedEx URL for UPS Tracking Number

      Release Date: 02/06/2024 Version: 7.x & Up Q - How is the URL used in the shipment confirmation email determined? We have a case where the FedEx URL is used for a UPS shipment, which obviously doesn’t work. See sample below: A - Elliott tries to ...

    • Feature - Equal Sign Support in Export Processor

      Release Date - 1/5/18 When exporting Elliott's alphanumeric fields to CSV through the Export Processor, the leading zeroes will be dropped when the file is opened with Excel. For example, when opening the CSV in Excel, item number 0100 is displayed ...

    • Feature - Import of Shipment Tracking Number for Sales Order Through CSV File

      Release date: 5/19/2020 Revised: 7/26/24 Version 8.5 and Up You can now import shipment tracking numbers for sales orders through a CSV File. You can find the utility in COP, Processing, Print Pick/Pack Tickets & Lbls, Shipping, and Tracking Number ...

    • Feature - Add Ship Via and Tracking Number to Expanded Aging Report CSV

      Release date: 7/27/2021 Versions: 8.5 & Up The purpose of this feature is for payment collection. There's a need to find outstanding unpaid invoices with shipment tracking numbers to follow up the trading partners for payments. Two new columns, Ship ...