Shell Programming and Scripting

Hi

I have a file, with format like:

column1|coulumn2|column3|column4
A|X|K|18
L|O|R|31,42,25
G|H|I|55,66
L|E|Q|25,31,94

output required:

column1|coulumn2|column3|column4
A|X|K|18
L|O|R|31,25
L|E|Q|25,31

Input File Format: All columns are seperated using |, last column might have multiple values spearated with ,

Output file rules:

1. Only keep rows which has one of the 18,25 or 31 (in its comma separated values) in the last column's comma separated values.
2. if last column contains any of the 18,25,31 values and some any other number, then remove numbers other than 18,25,31 as in the example output file - 1st row remains as it is, in 2nd 42 removed as this does not belong to the set (18,25,31), 3rd line remove altoghther as this does not have any of the (18,25,31) in its comma seperaetd values, 4th row remains but 94 goes away.

Please help writing one liner using awk or sed.

Thanks.

Why don't you show us the code you have so far, and tell us how it misbehaves?

Regards

I managed to write when I had to make values null in the last column:

awk -v numLines=`cat data.out.20090526 | wc -l` 'BEGIN { FS=OFS="|" } {if(NR > 1 && NR < numLines) {$4="null"} } 1' data.out.20090526 > data_new

but I am not sure how to check the values in last column against the given set of values (problem is with spliting the values in last column and then checking those and then removing row or values). Not sure if all this is possible in one awk/sed statement.

Please help, if you know how this can be done!!!!

Try this:

Code:

awk 'BEGIN{FS=OFS="|"; b[18]=b[25]=b[31]}{
  n=split($4,a,",")
  for(i=1;i<=n;i++){
    if(a[i]in b) {
      s=s?s "," a[i]:a[i]
    }
  }
}
s{print $1,$2,$3,s;s=""}
' file > newfile

Thanks a lot, this works (When I create a shell script with this content).

Is there any better way to print everything here rather than giving columns like $1,$2 etc, because I have a file having more than 100 coulmns in it?

One more question (That's a dumb one though): When I copy this and move everything in one line, I get syntax error, I tried to play around with the spaces before curly braces and all but could not make this work.

But thanks a lot for the help.

Thanks.

You should use nawk or gawk if you have more than 100 columns.
If you have more columns you can use this code, assuming the multiple values separated by commas is in the last column:

Code:

awk 'BEGIN{FS=OFS="|"; b[18]=b[25]=b[31]}{
  n=split($NF,a,",")
  for(i=1;i<=n;i++){
    if(a[i]in b) {
      s=s?s "," a[i]:a[i]
    }
  }
}
s{$NF=s;s="";print}
' file > newfile

awk:

Code:

nawk  'BEGIN{FS=OFS="|"}
/(18|31|25)/{
	tmp=""
	n=split($4,arr,",")
	for(i=1;i<=n;i++)
		if(arr[i]==18 || arr[i]==31 || arr[i]==25)
			if(tmp=="")
				tmp=arr[i]
			else
				tmp=sprintf("%s,%s",tmp,arr[i])
	$4=tmp
	print
}' yourfile

perl:

Code:

while(<DATA>){
	chomp;
	if(/[^|]*(18|25|31)[^|]*$/){
		my @tmp=split("[|]",$_);
		my @t=split(",",$tmp[3]);
		$tmp[3] = join ",", grep {$_==18 or $_==25 or $_==31} @t;
		print join "|", @tmp;
		print "\n";
	}
}
__DATA__
A|X|K|18
L|O|R|31,42,25
G|H|I|55,66
L|E|Q|25,31,94