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 number 0100 is displayed as 100 because the leading zero is dropped. This can be addressed by placing an equal sign before the first quote when the CSV file is created. For example, item 0100 would normally be exported as "0100." With the equal sign added, it now exports as ="0100." This forces Excel to use the values within the quotes without dropping the leading zeroes.

This feature addresses this issue.  A new  option is available in Global Setup-> System-> Company Specialized Control.

                                                        
1. Add = Sign to Following Fields When Export CSV? (Item Number, UPC Code, 25 Char PO Number, Tracking Number) - The default value for the field is "N." If set to Y, any item number, UPC code, 25-character PO number or tracking number (UPS/FedEx tracking numbers) will be exported with an equal sign before the first quote.

A possible issue with supporting this feature is Bartender integration. The default setting for Bartender is to use “Mixed Quote and Comma,” which works with CSV output without the equal sign. Exporting the data with the equal sign will not work properly since Bartender will print verbatim with the equal sign and quote. Because of this, all label printing programs that generate CSV files for Bartender to use do not support this new Global Setup option.

However, for those CSV files that are intended for Excel, and if users use the CSV file for Bartender purposes, the Bartender CSV Delimitation option can be changed from “Mixed Quote and Comma” to “Quote and Comma.”  “Quote and Comma” is not the default, but its behavior is to only retrieve the information inside the quote, and ignore the value outside of the quote. Therefore, the “=” will have no impact to Bartender when this option is set.  

Programs Modified: NSCTLBAR, NSCTLCOM, NSCTLINI, CSVOUTPT.PL, CSVPARSE.PL, PARASCII.PL, CPSABLST.CBL, CP01P1.CBL, CP01P1S.CBL, CP02S7.CBL, BM05P1.CBL, BM11P1.CBL, BM25P3.CBL, BM31P3.CBL, BMCSEXPP.CBL, BMCSTLST.CBL, BMDIDPRT.CBL, BMITMVRP.CBL, BMLABPFP.CBL, BMOPRVRP.CBL, BMRCSCHP.CBL, BMWIPDTP.CBL, BMWORRPT.CBL, CP02P11.CBL, CP02P12.CBL, CP051P.CBL, CP051P4.CBL, CP06P2.CBL, CP1925P.CBL, CPPROHRP.CBL, CPPROSRP.CBL, IM06P1.CBL, IM08P1.CBL, IM08P1B.CBL, IM20P1.CBL, IM26P1.CBL, IM29P1.CBL, IMAGERPT.CBL, IMATPLST.CBL, IMATPRP3.CBL,

IMATPRPT.CBL, IMLNKPRP.CBL, IMQACOMP.CBL, IMROACSP.CBL, IMSLSHSP.CBL, IMSNPLST.CBL, PO04P1N.CBL, PO05P1.CBL, PO05P3.CBL,

POITMEXP.CBL, POWHHST2.CBL, POWHHSTP.CBL, CPFCSEXP.CBL, IMTAGEXP.CBL, BMRTGLST.CBL, PO01P1.CBL, POWHRLSS.CBL, POWHRLST.CBL, SY12BMOP.CBL, SY12MONP.CBL, IM09CSV.PL

CLS

• Related Articles

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

• Feature - Prevent MM Item from Purchasing

  Release Date - 3/11/16 The purpose of this feature is to prevent an MM item from purchasing. MM item means the item's purchased or manufactured code is set to “M” and the item’s mostly purchased or manufactured is set to “M.” In the Purchase Order ...

• Feature - New Item Import Through CSV File

  Program Original Release Date: 05/15/2007 Document Release Date: 08/21/2020 Version: 7.5 & up The feature can be accessed in I/M, Utility, and New Item Import. It allows you to import new items from either an ASCII or CSV file. This document only ...

• Feature - 2023 Form 1099 CSV Electronic Filing Support

  Release Date: 12/12/23 Updated: 1/17/24 Version: 8.5 and Above There were no changes to the 1099-NEC or 1099-MISC physical form for tax year 2023. Please refer to the following Knowledge Base article for instructions on how to print 1099-NEC and ...

• Common Issues with Importing CSV Files Into Elliott

  Release Date: 12/18/2020 Version: 7.0 & Up Elliott Business Software has extensive features with CSV file export and import. It provides a gateway to view Elliott's data in Excel spreadsheets and optionally allow users to edit the data in ...