mjhjmtu

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.

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 in1,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 in1,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 in1,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" ' in1,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.

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: Validn", $1, i-1, a[i,$1], $i);
else
printf("%s->col%d: %s vs %s: Failuren", $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 Equaln", left, i-1, a[i,left])