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.


File 1


File 2

3044 1000
4466 400
1206 1010

Final.txt file to look like


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.


  • 2
    Search here, plenty of similar questions with answers. Aug 23, 2016 at 13:59
  • @don_crissti the join approach is not quite as straightforward as it might have been since the files have different delimiters. Quite doable, of course, just not all that simple.
    – terdon
    Aug 23, 2016 at 14:09
  • @terdon - the reason I posted the link is that the vast majority of Q there have an answer that uses awk. I bet this is a duplicate or almost a duplicate (if it wasn't for the different delimiter which isn't a big deal imo)... Aug 23, 2016 at 14:16
  • @don_crissti and I bet you're right, too. I'm just pointing out that this is a slightly more complex case than the average.
    – terdon
    Aug 23, 2016 at 15:08

1 Answer 1


Here's one way:

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

The -F"[, ]" sets the field separator to either a space or a comma. FNR is the current line number and NR the current line number of the current file. The two will be equal only while the 1st file is being read. Therefore, NR==FNR{a[$1]=$1","$2; next} will be run only on the lines of the first file and will save the 1st and 2nd fields (with a comma in between) as values in the array a whose keys are the 1st fields. Then, when the 2nd file is being read, if the 2nd field is in a, we print the value associated with it (the 1st and 2nd fields of the first file) and the 1st field of the second file.

That said, there's actually an app for this! This sort of thing is what join was made for. Sadly, since your two files are unsorted and have different delimiters, we need some tricks. If your shell supports <(), you can do:

$ join -t, -1 1 -2 2 <(sort file1) <(sed 's/ /,/g' file2 | sort -t"," -k2) 

The join -t, -1 1 -2 2 means use , as the delimiter and join on the 1st field of file1 and the 2nd field of file2. The sed just replaces spaces with commas so we have the same delimiter in both files. The sort does what it says on the bottle: it sorts its input.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .