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

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

• Feature - Add Serial Number to Mass Billing CSV Import

  Release Date - 6/18/18 This feature allows the user to specify serial numbers when using the Mass Billing application and choosing the CSV Import option. The serial number is supported in column E of the CSV file: The application will test for the ...

• Feature - Add Desc2 Column to Item Linkage CSV Report

  Release Date: 4/19/2017 In I/M -> Reports -> Item Linkage CSV Report, the Desc2 column is now added to the exported CSV file. See sample screen below. The following columns are supported in the CSV file: Item# - Item number Desc - Item description 1 ...

• Feature - Add Prevent Entry for Unit Price When Below Minimum Price or Cost

  Release date:3/5/2018 Currently when entering a line item in Order Entry, the application will warn the user if the price is below the item minimum price or unit cost. This new feature will allow users to prevent the entry in Order Entry, Sales Desk, ...