Match values and print sum based on repetitiveness in awk

Question

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

Answer 1

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)

Answer 2

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.