Compare two pipe delimited files using Unix

user_7040556 0
Viewed 97

File_1.txt:

emp_id|emp_name|emp_dob
111|Alex|08/29/1994
222|John|05/12/1997
333|Sam|08/24/1987

File_2.txt:

emp_id|emp_dob|emp_name
111|08/29/1994|Alex
444|05/12/2000|John
222|05/12/1997|Brad

Need to compare File_1.txt and File_2.txt using columns which are mention in header(head -1 File_1.txt), emp_id can be used for join condition

After comparison, below files should be generated

Mismatch.txt ## Contains mismatch values as per emp_id

File_name|column_name|emp_id|File_1_value|File_2_value
File 1|emp_name|222|John|Brad

Missing_Rows.txt ## Contains emp_ids which are missing in File 1 or File 2

File_name|emp_id
File 1|444
File 2|333

I'm able to join the 2 files using join command but unable to find the Missing and Mismatch rows details.

join -j1 -t'|' -o1.1,1.2,1.3,2.1,2.2,2.3 <(cat  File_1.txt|awk -F'|' '{print $0}'|sort  -t '|' -k1,1)<( cat  File_2.txt|awk -F'|' '{print $0}'|sort -t '|' -k1,1) > joinfile.txt`
sh unix scripting
1 Answers

With your shown samples, please try following awk code. It will create 2 output files named Mismatch.txt and Missing_Rows.txt as per requirement.

awk '
BEGIN{
  FS=OFS="|"
  print "File_name|column_name|emp_id|File_1_value|File_2_value" > "Mismatch.txt"
  print "File_name|emp_id" > "Missing_Rows.txt"
}
FNR==NR{
  empId1[$1]=FILENAME OFS $0
  empId2[$1]=$2
  empVal[$1,$2]=$0
  next
}
{ empId3[$1] }
!($1 in empId1){
  print (FILENAME,$1) > "Missing_Rows.txt"
  next
}
($1 in empId1) && !(($1,$3) in empVal){
  print (FILENAME,empId2[$1],$NF) > "Mismatch.txt"
}
END{
  for(i in empId1){
    if(!(i in empId3)){
      split(empId1[i],arr,"|")
      print (arr[1],arr[2]) > "Missing_Rows.txt"
    }
  }
}
' file1.txt file2.txt
user_58665800
Related