I have 2 files. File1 is:chr19   4124051 4124250 1chrX    154458151   154458200   2chr22   37019451    37019600    3chr15   74995401    74995550    4chr12   128823901   128824100   5and File2 is:chr19   4124051 4124250 1   CUPchr15   74995401    74995550    4   CUPchr12   128823901   128824100   5   CUPchr12   122752651   122752950   8   CUPchr13   113297001   113297350   9   CUPand I would like to have a File3 like this:chr19   4124051 4124250 1 CUPchrX    154458151   154458200   2chr22   37019451    37019600    3chr15   74995401    74995550    4 CUPchr12   128823901   128824100   5 CUPI'd like to merge the two files according to column 4 of File1, and adding values of column 5 from File2 to the last column of File1 whenever there is a match. I tired this:awk 'FNR==NR{a[$4]=$5;next} {print $1,$2,$3,$4,a[$4]}' File2 File1 > file3but it didn't work, it creates an extra row under each row.And I tried join command as well:join -1 4 -2 4 -o'1.1,1.2,1.3,1.4,2.5' File1 File2and it creates an empty file.Any suggestions?
merging two files according to a common column
text processing;awk;join;bioinformatics
Your files were created in Windows so they have Windows style line-endings (\r\n). Remove the \r and everything should work as you expect:sed -i 's/\r//' File1sed -i 's/\r//' File2awk 'FNR==NR{a[$4]=$5;next} {print $1,$2,$3,$4,a[$4]}' File2 File1 > file3