Skip to content

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

Feedback and Knowledge Base