Executing match and merge using values with leading zeros in a .csv file

Description


Any value that contains a leading zero when used in a .csv file is stored as text because those values when stored as integers would remove the leading zeros. (i.e. 0608 would display as 608).

Summary


This issue is caused by the way the data is stored in the .csv file. Numbers are treated as integers and therefore shouldn’t contain leading zeros, so the zeros are stripped during parsing, resulting in no matches.

The following options should be used when executing a match and merge against the leading zero integers:

Option 1: Rename the .csv file to .txt. Match and merge will treat all values as text.

Option 2: If necessary, create a schema.ini file for the .csv file and place it into the same directory. This will ensure every field/column is treated as text rather than a number, so any leading zeros will remain intact.

Example of schema.ini for a file named Invoices.csv

[Invoices.csv]

Format=CSVDelimited

CharacterSet=OEM

Col1="VENDOR #" Char Width 255

Col2="VENDOR NAME" Char Width 255

Col3="INVOICE #" Char Width 255

Col4="INVOICE DATE" Char Width 255

Col5="INVOICE AMT" Char Width 255

Col6="PO #" Char Width 255

Filed under All Versions, PaperVision® Capture

Last modified June 6, 2017

Submitted Feb 26, 2016

Related Articles

Share via Email Print