Script to check field value from a file records

I need a script to check the records in a file , if any value match transfer the record in error.txt file.

1- If any of the any field value is NULL(nothing in this field)

Record1|Record2|Record3|Record4|Record5|DATE1|DATE2

Example:

11111111|22222222|NULL|12|444|27042018|27042018 (Record3 is null)

99999999|88888888|007172FD|NULL|975|02052018|27042018 (Record4 is null)

2- If any of the field value is set as 0 , except Record4

Example:

Record1|Record2|Record3|Record4|Record5|DATE1|DATE2

Record1 = 0 or �00000000�
Record2 = 0 or �0000000000�
Record3 = 0 or �0000000000�

11111111111|000080809643|00000000|64|374|02052018|27042018
11111111111|000000000000|45678987|64|374|02052018|27042018
0000000000|000082079914|0071170B|1|308|27042018|27042018

3- If Next DATE1/DATE2 values are not in DDMMYYYY format

4- If Record5 value is negative

11111111111|000045672345|45678987|64|-222|02052018|27042018

Assuming NULL means "empty field", try

awk -F\| '
NR > 1  {for (i=1; i<=3; i++) if (!$i)  {print; next}
         if ($4   == "")        {print; next}
         if ($5+0 <= 0)         {print; next}
         for (i=6; i<=7; i++) if (!($i ~ /[0-3][0-9][01][0-9]20[12][0-9]/))     {print; next}
        }
' file > error.txt

The date format check may need to be refined with a more detailed regex...

If any of the field value is set as 0, except Record4

Example:
Record1|Record2|Record3|Record4|Record5|DATE1|DATE2
Record1 = 0 or �00000000�
Record2 = 0 or �0000000000�
Record3 = 0 or �0000000000�

If Record5 value is negative

11111111111|000045672345|45678987|64|-222|02052018|27042018

---------- Post updated 05-02-18 at 06:43 PM ---------- Previous update was 05-01-18 at 08:18 PM ----------

Thanks above script worked

---------- Post updated 05-03-18 at 01:44 PM ---------- Previous update was 05-02-18 at 06:43 PM ----------

HI , i my case format DDMMYYY or MMDDYYYY not create any problem, i am using following AWK script in some files it works & in some files it is not working.

awk -F'|' -v OFS='|' ' $6 !~ "^(0[1-9]|[12][0-9]|3[01])(0[1-9]|1[12])[0-9]{4}$" || $7 !~ "^(0[1-9]|[12][0-9]|3[01])(0[1-9]|1[12])[0-9]{4}$" '

Please take the time to carefully AND unambiguously phrase your request.

Is your problem solved ("Thanks above script worked")?
Or has another quirk occurred ("in some files it works & in some files it is not working"? How could a script be working and not working? Show WHAT works, and WHAT doesn't. Supply data samples and error conditions & messages!

Sorry for that I convert files dos2unix it worked.

i use following awk command:

awk -F'|' -v OFS='|' ' $6 !~ "^(0[1-9]|[12][0-9]|3[01])(0[1-9]|1[12])[0-9]{4}$" || $7 !~ "^(0[1-9]|[12][0-9]|3[01])(0[1-9]|1[12])[0-9]{4}$" '

but its not matching the date for given record:

Record1|Record2|Record3|Record4|Record5|DATE1|DATE2

108899999|0000222222|00AAAAA|-228|60|29102017|27042018

if DATE1 having 10th month its not matched with awk script.

Of course not - you didn't cover the 10 in the regex.

Thanks , i use following awk command to check date format DDMMYYYY , it works:

 awk -F'|' -v OFS='|' ' $6 !~ "^(0[1-9]|[12][0-9]|3[01])(0[1-9]|1[012])((19|20)[0-9][0-9])$" || $7 !~ "^(0[1-9]|[12][0-9]|3[01])(0[1-9]|1[012])((19|20)[0-9][0-9])$"'

HI,

I used below script but its also include some record which not matched with any condition as given in first post.

Record1|Record2|Record3|Record4|Record5|DATE1|DATE2

1024093815|000062317748|002F96B6|95|649|07052018|03052018

1032569624|000054060884|00E10177|189|1060|08052018|03052018

Code:

awk -F\| '
NR > 1  {for (i=1; i<=3; i++) if (!$i)  {print; next}
         if ($4   == "")        {print; next}
         if ($5+0 <= 0)         {print; next}
         for (i=6; i<=7; i++) if (!($i ~ /[0-3][0-9][01][0-9]20[12][0-9]/))     {print; next}
        }
'