Vlookup using awk command -2 and printing into des

问题:

I have two files in my linux server:

File_1

2018-09-01-00:00:03|911234567899|919535144580
2018-09-01-00:00:06|916724367238|919535144580
2018-09-01-00:00:07|911673617378|919535144580
2018-09-01-00:00:09|916721377382|919535144580
2018-09-01-00:00:13|910933214512|919535144580
2018-09-01-00:00:13|919777823434|919535144580
2018-09-01-00:00:15|919562343456|919535144580
2018-09-01-00:00:16|918867558865|919535144580

File_2

S NO.,Column1,Column2,Column3
72070,9112345,TGM,AP
72071,9167243,BGM,MP
72072,9116736,AGM,KN
72073,9167213,TGM,AP

Expected Output :

911234567899,TGM,AP
916724367238,BGM,MP
911673617378,AGM,KN
916721377382,TGM,AP
910933214512,NA,NA

I want a one line command using awk or a bash script that should check the column1 from File_2 and check the same into first 6 digits of 2nd column of File_1 and print the expected output.

回答1:

Not completely sure, since your output shown is not exactly same as your description is, could you please try following.

awk 'FNR==NR{a[$2]=$3","$4;next} {$2=substr($2,1,7) in a?$2","a[substr($2,1,7)]:$2",NA,NA";print $2}' FS="," Input_file_2  FS="|"  Input_file_1

OR adding a non-one liner form of solution too now.

awk '
FNR==NR{
  a[$2]=$3","$4
  next
}
{
  $2=substr($2,1,7) in a?$2","a[substr($2,1,7)]:$2",NA,NA"
  print $2
}
' FS="," File_2  FS="|" File_1