0

I would like to compare 2 similar files on a common column and output the difference. The files will have identical headers. And i am looking to see the difference of particular column.

Second file File2 can have extra rows but i am looking to compare only file1 column-Column2 value match with file2 column-Column2 and print if there is difference in Column3 values.

File1

Column1,Column2,Column3
Demosvc1,Demopod1,70647625e1b016f8c2b21eae7465b30e0b0375ea
Demosvc2,Demopod2,<none>
Demosvc3,Demopod3,0b12b9218e1a572e994a6b616059a618e8f5d9a0
Demosvc4,Demopod4,ca9802f0636c23d20daad17498b26df000205626
Demosvc6,Demopod6,ca9802f0636c23d20daad17498b26df000205626

File2

Column1,Column2,Column3
Demosvc1,Demopod1,70647625e1b016f8c2b21eae7465b30e0b0375ea
Demosvc2,Demopod2,20fd5948a80c5b06989f34b1c23a8e64afb7943e
Demosvc3,Demopod3,0b12b9218e1a572e994a6b616059a618e8f5d9a0
Demosvc4,Demopod4,551fbe73e835528da8269bac3652c6a64ecf4097
Demosvc5,Demopod5,4ae6230593c13b3a50e7b8e6cf0fe5fcc4a0fda2
Demosvc6,Demopod6,ca9802f0636c23d20daad17498b26df000205626

I want the difference to be printed like this

Desired output

Demopod2,<none>,20fd5948a80c5b06989f34b1c23a8e64afb7943e
Demopod4,ca9802f0636c23d20daad17498b26df000205626,551fbe73e835528da8269bac3652c6a64ecf4097
1
  • What have you tried so far? (Please edit your question to include this.) Commented Oct 8, 2019 at 22:08

3 Answers 3

2
awk -F',' '{
  if (NR==FNR) a[$1$2]=$3
  else if (a[$1$2]!="" && a[$1$2]!=$3) print $2","a[$1$2]","$3
}' file1 file2

When the first file is processed, save the value of the third field in array a with the first and second field as key.

When the second file is processed, compare if a a non-empty array value exists for the key which doesn't match the value of the third field. If this is the case, print the second field, array value and third field.

1

using Miller (https://github.com/johnkerl/miller) you can run

mlr --headerless-csv-output --csv join --lp l_ --rp r_ -f input_01.csv -j Column2 then \
    cut -f Column2,l_Column3,r_Column3 then \
    filter '$l_Column3!=$r_Column3' \
input_02.csv

to obtain

Demopod2,<none>,20fd5948a80c5b06989f34b1c23a8e64afb7943e
Demopod4,ca9802f0636c23d20daad17498b26df000205626,551fbe73e835528da8269bac3652c6a64ecf4097

Some notes:

  • join --lp l_ --rp r_ -f input_01.csv -j Column2 to set the join and the output field suffix name of the output
  • cut -f Column2,l_Column3,r_Column3 to extract Column2 and left and right Column 3
  • filter '$l_Column3!=$r_Column3' to filter all the rows where left Column3 not equal to right Column3
0
join --header -j2 -t, -o 1.2,1.3,2.3 FILE1 FILE2
  • --header treats first line as header
  • -j2 joins on column 2
  • -t separate columns on ,
  • -o ... prints file1-column2 file1-column3 file2-column3

Note: join requires that the input files are sorted on the joined field.

1
  • 1
    And then the desired output can be achieved by piping the join output to awk -F, '$2 != $3' Commented Oct 9, 2019 at 14:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .