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 - Prevent Excel from Dropping Leading Zeroes in CSV Item Number Field

      Release Date - 1/4/18 When exporting Elliott's item numbers to CSV through Elliott-provided reports with a CSV export option, the leading zeroes will be dropped when the file is opened with Excel. For example, when opening the CSV in Excel, item ...

    • How Do You Export Tracking Numbers From Elliott Into Shopify?

      Release Date: 7/7/2023 Version: 8.5 & Up Q: How do you export tracking numbers from Elliott into Shopify? A: Shopify is a good tool to build a presence on the internet and allow customers to enter orders for your products. Once you have shipped the ...

    • How to Export Price Codes to CSV File

      Release Date: 6/16/17 Q - Can you have Elliott produce the Price List Report with all price codes in a CSV format? We are trying to import it into Salesforce (through our Salesforce implementation team). A - Elliott provides a powerful utility to ...

    • How to Retrieve Tracking Number for an Order from Notes

      Release Date: 12/13/2017 Q - Can you let me know how the tracking number is stored in Elliott so I can use query to retrieve the information? A - Regarding how Elliott stores tracking numbers, please refer to the following Knowledge Base article: ...

    • 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 ...