I have the following CSV files:

File 1


File 2


How do I create a new file that combines the two by matching column 4 of file 1 with column 1 of file 2? The result should be:

  • 2
    Line3 of File1 has 5 fields, whereas Line1&Line2 both have 6. My solution below doesn't care, but you may want to fix that input data sample, or specify that you did this on purpose. @xhienne also noticed. Dec 5, 2016 at 19:53

2 Answers 2


Joining two files by a common field being a typical task for the join command, allow me to answer with something else than awk. This is bash code:

join -t, -1 4 -2 1 -o 1.1,1.2,1.3,1.4,1.5,1.6,2.2,2.3 <(sort -t, -k4 file1.csv) <(sort -t, -k1 file2.csv)
  • Good answer. But it shows two commas after 0 at last line for me: 34556,20161108,europe,state,0 ,,canada,12 Dec 5, 2016 at 19:07
  • 2
    @MohammadEtemaddar This is due to a missing field in line 3, which I assumed was a mistake
    – xhienne
    Dec 5, 2016 at 19:28

How about

awk -F, 'NR==FNR {l[$4]=$0 ; next} {print l[$1]","$2","$3}' file1 file2

1st block NR==FNR {l[$4]=$0 ; next} is active while the first file is read, and puts the entire line into an associative array called l, using the 4th field as the key. next causes skipping of the following instructions for that line.

2nd Block {print l[$1]","$2","$3} is only active when the second file is read, and uses the key given in the 1st column of file2 to lookup the entire stored line from file1. That, along with 2 commas, and fields $2 and $3 from file 2 then gets printed.

You must log in to answer this question.

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