1

I have the following CSV files:

File 1

19997,20161108,FSM,EXCHANGE_2,GLOBE,0  
17541,20161108,TATA_MOBILE,WORLD,TELECOM,1  
34556,20161108,europe,state,0  

File 2

EXCHANGE_2,CANADA,30298  
WORLD,INDIA,1123  
state,canada,2241  

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:

19997,20161108,FSM,EXCHANGE_2,GLOBE,0,CANADA,30298
17541,20161108,TATA_MOBILE,WORLD,TELECOM,1,INDIA,1123
34556,20161108,europe,state,0,canada,2241
1
  • 1
    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 '16 at 19:53
0

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)
2
  • Good answer. But it shows two commas after 0 at last line for me: 34556,20161108,europe,state,0 ,,canada,12 Dec 5 '16 at 19:07
  • 2
    @MohammadEtemaddar This is due to a missing field in line 3, which I assumed was a mistake
    – xhienne
    Dec 5 '16 at 19:28
0

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.

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.