Validating a string /column

[hcclnoodles]

Hi there I have a file that I recieve that looks like the folowing, As these details are being manually entered by the customer, we sometimes get a few typos on 4th column (the 14 digit number starting with 42....) As you can see the first one has a space as the first character and then a 13 number string (this has been typed wrong)

I have been asked somehow remove any line that does not have th full 14 character numeric string beginning with 42 and place it in an exceptions file (thus insuring it doesnt get processed)


19-5-204057,161104,0090.09, 4209152509001,AUTH CODE:200395,0,101
19-5-204074,161104,0010.00,42067036239101,AUTH CODE:7935 ,0,101
19-5-204075,161104,0010.00,42067036239100,AUTH CODE:3145 ,0,101
23-5-185395,161104,0012.02,42071862819101,AUTH CODE:005932,0,101
23-5-185405,161104,0155.76,42079561219101,AUTH CODE:289603,0,101
23-5-185407,161104,0023.71,42055829039101,AUTH CODE:694281,0,101


I really have no idea where to start on this so any pointers would be great, has awk got the capability to do this ??

 

[PHV]

Something like this ?
awk -F',' '
length(sprintf("%.0f",0+$4))!=14{print > "/path/to/bad";next}
{print}
' /path/to/input > /path/to/good

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[futurelet]

BEGIN { exceptfile="foobar"; FS="," }

{ s=$4
  # Remove non-numerals.
  gsub( /[^0-9]/, "", s )
  if ( 14 != length(s) )
  { print >exceptfile
    next
  }
}

{ print }

If you have nawk, use it instead of awk because on some systems awk is very old and lacks many useful features. For an introduction to Awk, see faq271-5564.