Merge two CSV files into one using AWK

Question

I am trying to merge two csv files using AWK. If the 1st column in file 1 matches the 1st column in file 2 I want to change file1's 3rd column to the value of file2's 2nd column. If there is no match, make no changes and move on to next row.

file1.csv

alice,soccer,24
bob,baseball,21
jessica,swimming,22
mike,running,20

file2.csv

alice,5
jessica,9

desired output:

alice,soccer,5
bob,baseball,21
jessica,swimming,9
mike,running,20

My code so far:

#!/bin/bash

    awk -F"," 'BEGIN{OFS=","}
        { if (NR==NFR){ 
         array[$1]=$1; 
        } 
        if ( $1 in array ){
         $3=array[$2]
        }
         print
        }' file2.csv file1.csv

Answer 1

Try:

$ awk -F, 'FNR==NR{a[$1]=$2;next} $1 in a{$3=a[$1]} 1' OFS=, file2.csv file1.csv
alice,soccer,5
bob,baseball,21
jessica,swimming,9
mike,running,20

How it works

• -F,

  This sets the input field separator to a comma.

• FNR==NR{a[$1]=$2;next}

  When reading file2.csv, this saves the second field under the key of the first field in array a.

  Notice the use of next. This tells awk to skip the rest of the commands and start over on the next line.

  For example, after reading your file2.csv, the contents of array a are: a['alice']=5 and a['jessica']=9.

• $1 in a{$3=a[$1]}

  When reading file1.csv, if the first field exists as a key in array a, then this updates the third field to the value stored in a.

  The test $1 in a is true if the content of $1 (which we are reading from file1.csv) is one of the keys (not one of the values) of array a.

• 1

  This is shorthand for {print}.

• OFS=,

  This sets the output field separator to a comma.