FDM throws error if no value in date field when using ADDDAYS function

Products

• CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

We have run into an issue with FDM when trying to mask a CSV file. If there is no value in date field, we are seeing the following error:

Table : C:/FDM_JOBS/35346561/MyTest.csv Column: "Birth_DD" 
Error parsing date:""
Dateformat : "dd"
message not found m0321-RowData  for language:en("""Bitcoin\\""","""John""","""""","""04""","""1999""") 
Unparseable date: """" 
error parsing date value for function ADDDAYS

 

Environment

ALL Supported Releases of FDM

Cause

As an overview, below are samples of the source text file, and definition file being used.

Sample_doublequotes.txt (source file being masked) - the first line is the header information:

"Non_PII","Full_Name","Birth_DD","Birth_MM","Birth_YYYY"
"Bitcoin\\","John","","04","1999"
"Bitcoin\\","Bob","10","06","2000"

Sample _doublequotes_dm.txt (original definition file):

HEADER=Y,TRAILER=,DELIM=,,DATEQUOTED=N,CHARQUOTED=N,NUMQUOTED=N
"Non_PII"
"Full_Name"
"Birth_DD"
"Birth_MM"
"Birth_MM"

Sample_masking_configuration.csv (The original masking configuration): Notice that the Date Format uses is "dd" (bolded)

Table,Column,Function,Parm1,Parm2,Parm3,Parm4,Keep Nulls,Date Format,Cross Reference,Override SQL,Unique Columns,XPath Element,Substr start,Substr length,Notes,Preformat,Update,Use Masked Values,Restart Column,From Occurrence,To Occurrence,Parm5,Parm6,Parm7,Parm8,Parm9,Parm10
,"\"Birth_DD\"",ADDDAYS,3,,,,Y,"\"dd\"",,,,,,,,,,,,,,,,,,,
,"\"Full_Name\"",HASHLOV,Names,3,,,Y,,,,,,,,,,,,,,,,,,,,

Sample_masking_options.txt (Original options file):

AUDIT=ALL
AUDITDIR=C:\FDM_masking\AUDIT
AUDITFILE=backslash_audit.csv
BATCHSIZE=10000
CHUNKSIZE=10000
COMMIT=1000
FETCHSIZE=100000
IGNOREQUOTECHAR=Y
LOGDIR=C:\FDM_masking\LOGS
LOGFILENAME=backslash_log.txt
RETAINDOUBLEQUOTESINDATA=N
SEEDTABLE=gtsrc_reference_data
SEEDTABLECOLUMNS=rd_ref_id,rd_ref_value,rd_ref_value2,rd_ref_value3,rd_ref_value4,rd_ref_value5,rd_ref_value6,rd_ref_value7,rd_ref_value8,rd_ref_value9,rd_index
SEEDTABLECONNECT=connect_SCRAMBLE.txt
TRIMVALUES=Y

 

The reason FDM is throwing the error is because of the use of the IGNOREQUOTECHAR masking option is enabled. This option causes FDM to attempting to process "" as a date, which is causing the error:

Invalid Date Format and/or unparseable date value.

Resolution

There are three changes need, to resolve this issue.

1. The definition file is used by FDM to define the files being used, It helps FDM understand the data that we are working with. By default, FDM considers all data in a flat file as a Character (varchar2) data type. When working with Date related data it's important to indicate which fields should be treated as date data types. This was not done in the original definition file. Also, since the data is contained in quotes (double or single quotes) setting the DATEQUOTED, CHARQUOTED, and NUMQUOTED options correctly in the definition file.
   
   For example, the definition file should be as follows:
   HEADER=1,TRAILER=,DELIM=,,DATEQUOTED=Y,CHARQUOTED=Y,NUMQUOTED=Y
   "Non_PII"
   "Full_Name"
   "Birth_DD","DD"
   "Birth_MM","MM"
   "Birth_YYYY","YYYY"
   
   
2. In the masking configuration, the Date Format set on the Birth_DD tab was "dd". FDM does not see this as a proper Date Format. Changing this to DD is recommended.
   For Example, in the FDM UI:
   
   
   
   
   

   This can also be done in the Sample_masking_configuration.csv file, if you don't want to manually edit the FDM UI.

   Table,Column,Function,Parm1,Parm2,Parm3,Parm4,Keep Nulls,Date Format,Cross Reference,Override SQL,Unique Columns,XPath Element,Substr start,Substr length,Notes,Preformat,Update,Use Masked Values,Restart Column,From Occurrence,To Occurrence,Parm5,Parm6,Parm7,Parm8,Parm9,Parm10
   ,"\"Birth_DD\"",ADDDAYS,3,,,,Y,"DD",,,,,,,,,,,,,,,,,,,
   ,"\"Full_Name\"",HASHLOV,Voya_Names,3,,,Y,,,,,,,,,,,,,,,,,,,,

3. The last change is with the options file. Since the IGNOREQUOTECHAR FDM to not be able to properly parse the Birth_DD field because of the quotes, we need to remove the option, or set the option to IGNOREQUOTECHAR = N.

With these three changes, the masking job runs successfully with the follow sample output:
Notice that both the Full_Name and Birth_DD fields were masked, when values were found in those fields.

Non_PII,Full_Name,Birth_DD,Birth_MM,Birth_YYYY
"Bitcoin\","Joseph","","04","1999"
"Bitcoin\","Melanie","13","06","2000"

Additional Information

Valid Data Formats can be found at Function Date Formats in the TDM Documentation.

See Mask Data Stored in Flat Files, for more information regarding working with Flat Files.