CODEWITHSUNDEEP
linuxshellawk
Nithin justin
Nithin justin

Reputation: 51

Awk - Match Values Between Two Files

I have two files that I am trying to compare, and create a final.txt file with data that exists from both of those files.

File1 - column 1 and File2 - columns 2 contain the value that I need to match between the two files.

So essentially, I am trying to -> take column1 from File1, if there is a match in column2 of file2, then write File1Column1, File1Column2 and File2Column1 to a new file called final.txt.

EXAMPLE

File 1

1000,Brian
1010,Jason
400,Nick

File 2

3044 "1000"
4466 "400"
1206 "1010"

output file to look like

1000,Brian,3044
1010,Jason,1206
400,Nick,4466

my test code not showing any result

awk -F"[,]" 'NR==FNR{a[$1]=$1","$2;next} ($2 in a){print a[$2]","$1}' file1.txt file2.txt

I believe I should be able to do this with awk, but for some reason I am really struggling with this one. Any help would be greatly appreciated.

Thanks

Upvotes: 1

Views: 517

Answers (2)

Ed Morton
Ed Morton

Reputation: 204468

You didn't include any lines that don't match between the 2 input files in your sample input/output so this may or may not do what you want for those cases:

$ cat tst.awk
BEGIN { FS="[[:space:]\",]+"; OFS="," }
NR==FNR {
    map[$2] = $1
    next
}
{ print $0, map[$1] }


$ awk -f tst.awk file2 file1
1000,Brian,3044
1010,Jason,1206
400,Nick,4466

Upvotes: 0

RavinderSingh13
RavinderSingh13

Reputation: 133750

Could you please try following, written and tested with your shown samples in GNU awk.

awk '
FNR==NR{
  gsub(/"/,"",$2)
  arr[$2]=$1
  next
}
FNR==1{
  FS=","
  OFS=","
  $0=$0
}
($1 in arr){
  print $0,arr[$1]
}
'  Input_file2  Input_file1

Explanation: Adding detailed explanation for above.

awk '                ##Starting awk program from here.
FNR==NR{             ##Checking condition FNR==NR which will be TRUE when Input_file1 is being read.
  gsub(/"/,"",$2)    ##globally substituting " in 2nd field with NULL.
  arr[$2]=$1         ##Creating array arr with index of 2nd field and value of 1st field.
  next               ##next will skip all further statements from here.
}
FNR==1{              ##Checking condition if this is first line of Input_file1.
  FS=","             ##Setting FS as comma here.
  OFS=","            ##Setting OFS as comma here.
  $0=$0              ##Reassigning current line to itself so that field separator values will be implemented to current line.
}
($1 in arr){         ##Checking condition if 1st field is present in arr then do following.
  print $0,arr[$1]   ##Printing current line and value of array arr.
}
' file2 file1        ##Mentioning Input_file names here.

Upvotes: 2

Related Questions