Match the columns between 2 files

Shell Programming and Scripting
1

I have two files I want to match ids in the 5th column of the file 1 with the first column of the file 2 and get the description for the matched ids as shown in the output

sno    nm    no    nm2    ID
1    cc    574372    yyyi    |6810|51234|
2    bb    119721    nmjk    |6810|51234|51179|    
3    ab    54161    mkkjn    |48193|46907|

ID        Description
6810    verygood_output
51234    Awesome work
46907    notbad
51179    excellent effort
48193    can do better

output format

sno    nm    no    nm2    Description    
1    cc    574372    yyyi    verygood_output;Awesome work
2    bb    119721    nmjk    verygood_output;Awesome work;excellent effort
3    ab    54161    mkkjn    notbad;can do better
2

Try:

awk 'NR==FNR{for (i=2;i<=NF;i++) a[$1]=a[$1](i==2?"":" ")$i}NR!=FNR{for (i in a) gsub(i,a,$5);gsub("\\|",";",$5);gsub("^;|;$","",$5);print}' file2 file1
3 
awk 'NR==FNR{match($0,"([^ ]+) +(.+)",x);_[x[1]]=x[2];next}FNR==1{$NF=_[$NF]}
FNR>1{n=split($NF,x,"|");$NF="";for(i=1;++i<n;){z=_[x];$NF=$NF?$NF";"z:z}}1' file2 file1

--ahamed

4

This one makes an effort to keep the spacing

awk 'NR==FNR {
  id=$1
  sub("^[[:alnum:]]+[[:space:]]+","")
  a[id]=$0
  next
}
{
  n=split($NF,b,"|")
  sub("[[:alnum:]|]+ *$","")
  printf "%s",$0
  sep=""
  for (i=1; i<=n; ++i) if (b in a) {printf "%s",sep a[b]; sep=";"}
  print ""
}' file2 file1
5

Another option:

awk '
  NR==FNR{
    A[$1]=$2
    next
  } 
  FNR==1{
    sub("ID",A["ID"])
  }
  {
    for(i=2; i<NF; i++) $i=A[$i]
    sub(/;/,x)
    sub(/; *$/,x)
  }
  1
' FS='   +' file2 FS=\| OFS=\; file1