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
Improve this question
1
  • What have you tried so far? (Please edit your question to include this.)
    – Chris Davies
    Oct 8, 2019 at 22:08

3 Answers 3

Reset to default
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.

Improve this answer
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
Improve this answer
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.

Improve this answer
1
  • 1
    And then the desired output can be achieved by piping the join output to awk -F, '$2 != $3'
    – glenn jackman
    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 .