Shell Programming and Scripting

ladies and gents..

im trying to do a surgical search and replace though a 3 GB file;

any timestamp fields that are spaces need to be changed to null
any numerics that are spaces need to be changed to null


so...

say i have a flat file such as this

numeric, char, numeric, date, char

pk|fname|weight|bday|city

1|john| |10-JAN-08|raleigh
2|steph|12| |denver
3|jane|8|21-MAR-01|atlanta
4|eric||06-APR-82|atherton


for any numeric or date field that contains all spaces, i.e.:
row 1 field 3, row 2 field 4

replace the spaces to make a null, such as:

1|john||10-JAN-08|raleigh
2|steph|12||denver
3|jane|8|21-MAR-01|atlanta
4|eric||06-APR-82|atherton




i was thinking something like:

BEGIN {
RS="\n"
FS="|"
}

{
if ($3==" ") {
$3="";
}
if ($4==" ") {
$4="";
}
print $1","$2","$3","$4","$5
}


is awk the way to go with this, or sed with a regex the way i should do it...


thanks much..

If it is ok to remove any blanks in any fields, you could simply use:

Code:

tr -d ' ' < infile > outfile

This might be fastest with a 3 GB input file.

If it is definetly only field 3 and 4 that may be touched, try this:

Code:

awk -F"|" '{sub(/ /,"",$3); sub(/ /,"",$4); print; next}' OFS="|" infile > outfile

thanks zaxxon..

ill tinker around with your second idea today

much appreciated

I don't see a problem with putting all similar character spaces to null, also?
What would the problem with that be? A single space in a character field is
essentially the definition of no-data...

So, with that premise, why wouldn't this work fine?

Code:

sed -e 's/| |/||/g' file_in > file_out

What if for a varchar field for example has a name that consists of two words divided by a blank? It would be deleted and the value will be simply wrong.
As we only have a short snippet of his data to be processed, we can only guess and when he states it is important to work only on field 3 and 4 when he already got a cmd to work the whole line which is apparently not wanted...

zaxxon..

i went with your suggestion and it worked great...

but the more i dug into the data, the more i saw how crappy it was..

lots of leading and trailing whitespace on most of the fields, so i ended up using this approach..

awk 'BEGIN{FS=OFS="\t"};{ for (i=NF; i>0; i--) gsub(/^[ \t]+|[ \t]+$/, "",$i); print}' < SALES_data.dat> SALES_data_cleansed.dat

thanks for the help