Join using awk

Shell Programming and Scripting
1

Hi -

I want to join 2 files on matching keys ( column 1 in both files ) and check for unmatched columns, If for each record print the first unmatched column from both files and then proceed to next record and do the same.

File 1:

123|Roy|jj|20/07/3000|25.48
125|Victor|kk|30/07/2009|34.56
234|Nag|aa|15/03/2009|234.45

File2:

123|Ray|jj|20/07/2009|25.48
125|Victor|kick|30/07/2009|34.56
234|Nag|aa|15/03/2009|434.45
132|rita|bb|15/03/2009|134.45

Output:

123|Roy|Ray
125|kk|kick
234|234.45|434.45

I was able to do following but couldn't get to stop processing after first mismatched column for each record. this is what I did

awk -v File1="$File1" -v File2="$File2" -v key="$key" -v col="$col" -F "|" '
NR==FNR  { PE[$key] = $col ;next } 
{
        if ( $key in PE && $col!="1")
        { if (PE[$key]!=$col ) 
                { print $key"|"$col"|"PE[$key] }
                } 
        else  {
        print "key: "$key " is missing in " File2
        }
}' $File2 $File1
2

Hi, perhaps you need to switch the reading order of $File1 and $File2 ?
Did you inititialize the shell variables correctly?
I am unsure what you mean by "I couldn't get to stop processing after first mismatched column for each record""

3

perl:

open FH,"<1.txt";
while(<FH>){
	chomp;
	my @tmp = split("[|]",$_);
	$hash{$tmp[0]}=[@tmp[1..$#tmp]];
}
LEO:
while(<DATA>){
	chomp;
	my @tmp = split("[|]",$_);
	my @pre=@{$hash{$tmp[0]}};
	my @cur=@tmp[1..$#tmp];
	for (my $i=0;$i<=$#pre;$i++){
		if($pre[$i] ne $cur[$i]){
			print $tmp[0]," ",$pre[$i]," ",$cur[$i],"\n";
			next LEO;
		}
	}
}
__DATA__
123|Roy|jj|20/07/3000|25.48
125|Victor|kk|30/07/2009|34.56
234|Nag|aa|15/03/2009|234.45
4

use nawk,gawk or /usr/xpg4/bin/awk.

code:-

nawk -F"\|"  '
NR==FNR{for (i=1;i<=NF;i++) {a[$1]=a[$1]" "$i} ; next  }
($1 in a){
split(a[$1],b," ")
for (j=1;j<=NF;j++) {
if ( $j != b[j] ) {print $1,b[j],$j ;next}
}
}
' OFS="\|" File1 File2


File 1:
123|Roy|jj|20/07/3000|25.48
125|Victor|kk|30/07/2009|34.56
234|Nag|aa|15/03/2009|234.45


File2:

123|Ray|jj|20/07/2009|25.48
125|Victor|kick|30/07/2009|34.56
234|Nag|aa|15/03/2009|434.45
132|rita|bb|15/03/2009|134.45

o/p:-

123|Roy|Ray
125|kk|kick
234|234.45|434.45

:D:D:D
:b::b::b:

5

What about :

NR==FNR{a[$1]=$0}
($1 in a){
split(a[$1],b,"|")
6

It will work as well....:b::b:

7

Thanks a lot it worked like a charm.

8

I get:

123\|Roy\|Ray
125\|kk\|kick
234\|Nag\|Nbg

It works if I change

OFS="\|" File1 File2

to

OFS="|" File1 File2

But what about the missing record?

---------- Post updated at 17:10 ---------- Previous update was at 16:41 ----------

Incorporating Damero's suggestion into Ahmad's solution and a few changes:

awk -F '|' '
  NR==FNR { a[$1]=$0;next }
  { if ($1 in a) {
      split(a[$1],b,"|")
      for (j=1;j<=NF;j++)
        if ( $j != b[j] ) { print $1,b[j],$j ; next }
    } else
        print "key: "$1 " is missing in " FILENAME
  }
' OFS="|" File1 File2

output:

123|Roy|Ray
125|kk|kick
234|Nag|Nbg
key: 132 is missing in File2