0

I have a file1 with 3 columns and file2 with 4 columns on a Linux system. How do I join the two files based on the string in column 3 of file1 to the string in column 2 of file2? File2 is a large db with many entries. Columns 3 and 2 of file1 and file2 respectively share only few strings. I would like to output file1 with joined file2 rows in case the strings match and output a dash for entries with no matches.

file1:

300 100 a101
450 410 a400
670 710 a20
700 610 a340

file2:

b30  a340 tttttttt 456
b500 a200 llllllll 567
b60  a101 uuuuuuuu 344
b40  a50  kkkkkkkk 223

output:

300 100 a101 b60 a101 uuuuuuuu 344
450 410 a400 -
670 710 a20  -
700 610 a340 b30 a340 tttttttt 456
4

Using GNU awk and GNU join, which are standard on Linux (may or may not work with non-GNU versions):

$ join -a1 -1 3 -2 2 <(sort -k3,3 file1) <(sort -k2,2 file2) | 
    awk '$4 == "" { $4 = "-" }; {t=$1; $1=$2; $2=$3; $3=t; print}' |
    sort
300 100 a101 b60 uuuuuuuu 344
450 410 a400 -
670 710 a20 -
700 610 a340 b30 tttttttt 456

The join command joins file1 and file2 on fields 3 and 2 respectively. It uses process substitution to ensure that both files are sorted by their respective key fields. The -a 1 option is used so that all lines from file1 are printed, even if they are not matched with a line from file2.

Unfortunately, join places file1's key field at the start of each record. This is fixed with awk to move the fields back to their original order, using a variable called t as a temporary holder for the value of $1. The awk script also adds the trailing dash character in field $4 if there was no match between the files (because join itself does not do this).

Finally, the output is sorted.

1
  • Thank you. worked perfectly
    – Arora
    May 27 at 9:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.