Shell Programming and Scripting

I am trying to validate the third column in a pipe delimited file.
The column must be 10 char long and all digits 0-9.

I am writing out two new files from the existing file, if it would be quicker, I could leave the bad rows in the file and ignore them in the next process.

What I have is working, but is taking a long time to run.
There are over 1,000,000 rows in the file and the current code has taken 1hr 40 min to process 230,000 rows.

Main part of program.

while read line
do
echo "${line}" | awk -F"|" '{print $3 }' | read emplid

badrow="N"

# remove echo commands

if [ ${badrow} = "N" ]
then
if [ ${#emplid} -ne 10 ]
then
badrow="Y"
# echo " Bad emplid length = ${emplid}"
fi
fi


if [ ${badrow} = "N" ]
then
nonums="$(echo ${emplid} | sed 's/[0-9]//g')"

if [ ! -z "$nonums" ]
then
badrow="Y"
# echo " Bad emplid numeric = ${emplid} "
else
badrow="N"
# echo " Good emplid = ${emplid} "
fi
fi


#
# If badrow = N then write to bad file if good then write to good file.
#
# increment new counters

if [ ${badrow} = "N" ]
then
# Write good row to file.
echo "${line}" >> ${good_record}
let good_recno=${good_recno}+1
else
# Write bad row to file.
echo "${line}" >> ${bad_record}
let bad_recno=${bad_recno}+1
fi

let recno=${recno}+1

done < ${incomingpathfile}


***
Example data

0001010101|TT10101|0000011111|More data and delimiters
1001010101|SS10101|0000022222|More data and delimiters
2001010101|RR10101| 00022222|More data and delimiters
2001010101|QQ10101|O000033333|More data and delimiters

nawk -f barry.awk myDate

barry.awk

Code:

BEGIN {
  FS=OFS="|"
  fileRoot="file"
}
{
  out= fileRoot ( match($3, /^[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]$/) ? "Good" : "Bad")
  print >> out
  close(out)
}

Python alternative:

Code:

goodfile = open("goodfile.txt","a")
badfile = open("badfile.txt","a")
for line in open("inputfile"):
  thirdcol = line.split("|")[2] #get 3rd column
  if not thirdcol.isdigit() or not len(thirdcol) == 10: #check for digit and length 10
     badfile.write(line)  #write to bad file
  else:
     goodfile.write(line) #write to good file

Nice and concise, as usual. But as the original poster has a large file, I wondered if opening and closing a file every line of the input file was as time efficient as a more traditionnal code:

Code:

$3 ~ /^[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]$/ {
    print > "fileGood"
}

$3 !~ /^[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]$/ {
    print > "fileBad"
}

Timing the two scripts on a 500.000 lines file gives me this:

Code:

real    0m13.361s
user    0m6.420s
sys     0m6.500s


real    0m1.051s
user    0m0.940s
sys     0m0.110s

$3 ~ /^[0-9]\{10\}$/ {
print > fileGood
}

$3 !~ /^[0-9]\{10\}$/ {
print > fileBad
}

you're right - it's just a habbit of mine to close the output descriptors if dealing with multipe files (knowing that awk's have a low set limit on the number of openned output descriptors).

As in OP's situation there will be only TWO output descriptors, there's no need to keep closing and reopenning one.

Code:

BEGIN {
  FS=OFS="|"
  fileRoot="file"
}
{
  out= fileRoot ( match($3, /^[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]$/) ? "Good" : "Bad")
  print >> out
  #close(out)
}

timing the scripts WITH the 'close' and withOUT the 'close' on the 100K line file:
Speaking of speed..... This implementation will try to do TWO pattern matches for EVERY input line. In essense, you need to do just ONE and the result of your match is binary: either "Good" OR "Bad".
<modestyON>
I believe my implementation should perform a bit better
</modestyON>

unfortunately this paradigm with 'number of repetitions' does not work on all awk's, including Solaris' 'nawk' and gawk.