Shell Programming and Scripting

Hi All,

I got a file with 3 fields delimited by hyphen "-". I have to validate & cleanse the data before i begine the processing

Requirements

1. No record should contain more than 2 delimiters
2. No record should even contain less than 2 delimiters
3. Any records that matches rule 1 & 2 should be captured to
bad_records.dat
4. Delete records satisfying rules 1 & 2 from original incoming data file


Sample Format

input.dat
COL1-COL2-COL3
scott-2000-10
tiger-1000-20
c-bill-1000-30
mike20-1000

so after the validation&cleansing process, the data should appear like below

input.dat
scott-2000-10
tiger-1000-20

bad_records.dat
c-bill-1000-30
mike20-1000

Please note i can't use perl progressive scanning. I need to achieve this via korn shell script


Thanks guys

Here is a process to think about

Code:

read thru file
  do
  read line
  tr "-" "\n" to change to rows
  then check row count
  if row count = 3
    write record to good_file
    else write record to bad file
done

See where you can go with that

Thanks Joey

Thats really a fast response. Ideally this should work. Any other ideas to make this logic fast enough to process a 5 million records with 30 columns in a minimum time of 1-3 mins

How about using grep and grep -v, might be something like below can work
grep "^[a-z0-9]*-[a-z0-9]*-[0-9]*$" <inputfile> >goodFile
grep -v "^[a-z0-9]*-[a-z0-9]*-[0-9]*$" <inputfile> >badFile

Thanks Avis,

I will try that too tommorow, Now taking this to one step further

My actual delimiter is non ascii character. It is from extended ascii set.

My input file is delimited by "Ç". i.e. CHR(199).

Will that matter to grep or sed as replied above?

Code:

nawk -F"-" '{
if(NF!=3)
	print $0 >> "bad.txt"
else
	print
}' file

Joey,

I have tried your logic. It works but involves huges amount of processing & time while processing millions of records.

#!bin/ksh
date
delimiter_char=","
last_field_num=6
file_name=2.txt
col_count=$last_field_num
while read line; do
act_count=`echo $line | tr ${delimiter_char} "\n" | wc -l | awk '{print $1}'`
if [ $col_count -ne $act_count ]; then
echo $line >> ${file_name%.txt}.bad
else
echo $line >> ${file_name%.txt}.good
fi
done < ${file_name}
---------------------------------------------------------------

Avis, Your logic works fine, But i couldn't use it either. Coz i couldn't make UDF out of it. Say i have many files, Number of columns vary

Yours is good at performance
grep -e "^[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,[^,]*$" 2.txt > 3.good &
pid_good=$!
grep -v "^[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,[^,]*$" 2.txt > 3.bad &
pid_bad=$!
wait $pid_good
wait $pid_bad

Note: Worked on a CSV File. Any unicode character might appear in my data except the delimiter. ;-)


Any other ideas??????