Comparing two files and writing mismatched rows along with mismatched columns. Pointing out the mism

UNIX for Advanced & Expert Users
1

I got a requirement where I need to compare two files wrt to each columns and write the corresponding difference in another file along with some identification showing mismatched columns. Pointing out the mismatched columns is my main problem statement. For example we have files like:
File 1

1|piyush|bangalore|dev 
1|piyush|bangalore|QA 
2|pankaj|bangalore|dev 
3|rohit|delhi|QA

File 2

 1|piyush|bangalore|QA 
1|piyush|bangalore|QA 
2|pankaj|bangalore|dev 
3|rohit|bangalore|dev

The expected output file looks somewhat like.

 File 1 
1|piyush|bangalore|**dev**
File 2 
1|piyush|bangalore|**QA**
File 1 
3|rohit|**delhi**|**QA**
File 2 
3|rohit|**bangalore**|**dev**

I want to achieve something like this where i can see the mismatched columns as well along with mismatched rows. I have tried

diff File1 File2 > Diff_File

But this is giving me only the mismatched records or rows. I am not getting any way to point out the mismatched columns as well. Please help me out if its possible to do is using shell script or awk command as i am very new to this. Thanks in advance.

2

Are the corresponding records identified by line No., or by a key value?

3

Before writing any code: notice that you are implicitly working context-oriented and - as a principle - you cannot do this with regexps. You will need a parser! (see here for an in-depth discussion about this). For instance, you break down this:

3|rohit|**delhi**|**QA**

Into two differences, "delhi/bangalore" and "QA/dev". But without your (outside) knowledge of "delimiters" and "fields", etc. - that is: you involuntarily parsing the input as you read it - there is only one different part. The fact that "|" occurs once in the first variant (from file 1) and once in the second variant (from file 2) means only as much as the "e" in "delhi" occurring in "bangalore" too.

That means, you need to "build into" the script you are about to write this knowledge. You can start by simply reading line for line and splitting it into fields. These fields can then be compared.

Notice that you still need to define how different lines are to be matched. Consider these two file contents:

a|b|c     a|b|c
d|e|f     d|x|f
d|x|f     d|e|f

will these files be "identical" per your definition because the sequence of the lines doesn't matter or will these be considered two differences because it does?

I hope this helps.

bakunin

4

Based on the assumption that you identify records by their line No., how about

awk '
        {getline TL < FN
         n = split (TL, T)
         for (i=1; i<=NF; i++) if ($i != T)  {$i   = "**" $i "**"
                                                 T = "**" T "**"
                                                }
         print > (FILENAME ".res")
         for (i=1; i<=n; i++) printf "%s%s", T  , i==n?ORS:OFS > (FN ".res")
        }
' FS="|" OFS="|" FN=file1 file2
cf *.res
file1.res:
1|piyush|bangalore|**dev **
1|piyush|bangalore|QA 
2|pankaj|bangalore|dev 
3|rohit|**delhi**|**QA**
file2.res:
 1|piyush|bangalore|**QA **
1|piyush|bangalore|QA 
2|pankaj|bangalore|dev 
3|rohit|**bangalore**|**dev**
5

Hi, no we don't have any line no or key in my case

6

HI.

Using your sample data, here is a solution using a GNU utility, dwdiff . Basically, it operates on space-separated words, so I modified your sample files to be that instead of | separated:

#!/usr/bin/env bash

# @(#) s1       Demonstrate field/word diff, dwdiff.

# Utility functions: print-as-echo, print-line-with-visual-space, debug.
# export PATH="/usr/local/bin:/usr/bin:/bin"
LC_ALL=C ; LANG=C ; export LC_ALL LANG
pe() { for _i;do printf "%s" "$_i";done; printf "\n"; }
pl() { pe;pe "-----" ;pe "$*"; }
em() { pe "$*" >&2 ; }
db() { ( printf " db, ";for _i;do printf "%s" "$_i";done;printf "\n" ) >&2 ; }
db() { : ; }
C=$HOME/bin/context && [ -f $C ] && $C dwdiff

FILE=${1-data1}

pl " Input data files:"
head data?

pl " Amended data files::"
sed 's/|/ /g' data1 > t1
sed 's/|/ /g' data2 > t2
head t?

pl " Results:"
dwdiff t1 t2

pl " More details for dwdiff:"
dixf dwdiff

exit 0

producing:

$ ./s1

Environment: LC_ALL = C, LANG = C
(Versions displayed with local utility "version")
OS, ker|rel, machine: Linux, 3.16.0-4-amd64, x86_64
Distribution        : Debian 8.7 (jessie) 
bash GNU bash 4.3.30
dwdiff 2.0.9

-----
 Input data files:
==> data1 <==
1|piyush|bangalore|dev 
1|piyush|bangalore|QA 
2|pankaj|bangalore|dev 
3|rohit|delhi|QA

==> data2 <==
1|piyush|bangalore|QA 
1|piyush|bangalore|QA 
2|pankaj|bangalore|dev 
3|rohit|bangalore|dev

-----
 Amended data files::
==> t1 <==
1 piyush bangalore dev 
1 piyush bangalore QA 
2 pankaj bangalore dev 
3 rohit delhi QA

==> t2 <==
1 piyush bangalore QA 
1 piyush bangalore QA 
2 pankaj bangalore dev 
3 rohit bangalore dev

-----
 Results:
1 piyush bangalore [-dev-] {+QA+} 
1 piyush bangalore QA 
2 pankaj bangalore dev 
3 rohit [-delhi QA-] {+bangalore dev+}

-----
 More details for dwdiff:
dwdiff  a delimited word diff program (man)
Path    : /usr/bin/dwdiff
Version : 2.0.9
Type    : ELF 64-bit LSB executable, x86-64, version 1 (SYSV ...)
Help    : probably available with --help
Repo    : Debian 8.7 (jessie)

Note that the braces enclose the markers + for an addition, - for a deletion.

There are a number of additional options. See man dwdiff for details.

Best wishes ... cheers, drl

7

Aaaahh - so why don't you compare 3 rohit delhi QA to 1 piyush bangalore QA ?

8

Hi.

I overlooked a useful option, so this could eliminate the intermediate step:

$ dwdiff -d '|' data1 data2
1|piyush|bangalore|[-dev-]{+QA+} 
1|piyush|bangalore|QA 
2|pankaj|bangalore|dev 
3|rohit|[-delhi-]{+bangalore+}|[-QA-]{+dev+}

Best wishes ... cheers, drl

1 Like
9

Hi Thanks a lot drl and others for helping me out with the solutions.