Comparing csv files to lookup column 1 then check values in column 2

Question

OK, I will try and explain what I need to do as best as possible. Basically I have two CSV files, as per the examples below:

File 1:

Column 1, Column 2
abc     , 123
def     , 234
adf     , 567

File 2

Column 1, Column 2
abc     , 123
def     , 234
adf     , 578

I need to write either a shell script or simple command that will do the following:

1. Sort both files by column 1
2. Row by row, do the following:
   • Using column 1 in file 1, search for this value in column 1 in file 2.
     1. if found, compare the value in column 2 in file 1 against the value in column 2 of file 2
     2. if it matches, write column 1, column 2 and "Validated" in column 3 to a separate file
     3. if it does not match, write column 1, column 2 and "Failed" to a separate file

This results in two output files: the first with everything that was found in column 1 and column 2 matches, and a second file containing either column 1 lookups that failed or where column 1 was found, where column 2 did not match, so, essentially, using column 1 as the key to check column 2.

Answer 1

Given the following input files:

$ cat in1 in2
Column 1, Column 2
abc     , 123
def     , 234
adf     , 567
Column 1, Column 2
abc     , 123
def     , 234
adf     , 578

First, we sort them; we can then stitch them together into one file:

$ sort in1 > in1.sorted; sort in2 > in2.sorted; paste in{1,2}.sorted
Column 1, Column 2  Column 1, Column 2
abc     , 123   abc     , 123
adf     , 567   adf     , 578
def     , 234   def     , 234

awk will help us here, but the commas get in our way; we can get rid of them with sed first:

$ paste in{1,2}.sorted | sed s/,//g
Column 1 Column 2   Column 1 Column 2
abc      123    abc      123
adf      567    adf      578
def      234    def      234

And then we can dump that through a quick awk:

$ paste in{1,2}.sorted | sed s/,//g | awk '$2 == $4 {print $1,"Validated"}; $2 != $4 { print $1,"Failed"}'
Column Failed
abc Validated
adf Failed
def Validated

This can also be done with raw awk, with the advantage of being able to strip out the header rows and not rely on the same data being in the same order, thus removing the need for sorting:

$ awk 'FNR != 1 && NR == FNR {data[$1]=$3} FNR != 1 && NR != FNR {if(data[$1]==$3) {print $1, "Validated"} else {print $1, "Failed"} }' in{1,2}
abc Validated
adf Failed
def Validated

This relies on a few magic awk built-in variables and tricks related to them:

• NR - the total number of records processed
• FNR - the total number of records in the current file processed
• FNR != 1 - skips the first row of each file (does not treat the headers as data)
• NR != FNR - runs only after the first file has been completely read and we have started reading subsequent files. This allows us to prepopulate the data array for testing once we start chewing on the second file.

Answer 2

I think I have this sorted out now with the following, in case any else reads this and needs this. Thanks again.

FNR == NR {
    for (i = 2; i <= NF; i++) { a[i,$1] = $i }
    b[$1];
    next;
}
($1 in b) {                   # check if row in file2 existed in file1
    for (i = 2; i <= NF; i++) {
        if (a[i,$1] == $i)
            printf("%s->col%d: %s vs %s: Valid\n", $1, i-1, a[i,$1], $i);
        else
            printf("%s->col%d: %s vs %s: Failure\n", $1, i-1, a[i,$1], $i);
    }
    delete b[$1];   # delete entries which are processed
}

END {
    for (left in b) {   # look which didn't match
        for (i = 2; i <= NF; i++) 
            printf("%s->col%d: %s vs (blank): Not Equal\n", left, i-1, a[i,left])
    }
}