compare two files at key level (id) and display the differences at column level

Question

I am looking for a way to compare two files at a key level (id) and display the changes at the column level

file_1.txt  
id|description|name|date  
1|Row 1|a|2019-06-15 00:20:15:00  
2|Row 2|b|2019-06-16 15:18:10:00  
3|Row 3|c|2019-06-17 07:02:17:00  
4|Row 4|d|2019-06-25 09:00:01:00  
5|Row 5|e|2019-06-25 22:00:00:00  


file_2.txt  
id|description|name|date  
1|Row 1|a|2019-06-15 00:20:15:00  
2|Row 2|c|2019-06-16 15:18:10:00  
4|Row 4|d|2019-06-25 09:00:01:00  
5|ROW 5|b|2019-06-25 22:00:00:00  
7|Row 7|f|2019-06-17 07:02:17:00

The output should look like:

1|Row 1|a|2019-06-15 00:20:15:001|Row 1|a|2019-06-15 00:20:15:00,Match  
2|Row 2|c|2019-06-16 15:18:10:00|Row 2|b|2019-06-16 15:18:10:00No Match  
3|Row 3|c|2019-06-17 07:02:17:00,No Match  
4|Row 4|d|2019-06-25 09:00:01:004|Row 4|d|2019-06-25 09:00:01:00,Match  
5|ROW 5|b|2019-06-25 22:00:00:00|Row 5|e|2019-06-25 22:00:00:00,No Match  
7|Row 7|f|2019-06-17 07:02:17:00,No Match

tried using below where file2 is being used as driver file for diaplying the ouput therefore its not printing and ignoring row with id 3 as its not present in file2.txt

awk -F, 'NR==FNR{ arr[$1]=$0; next } { print $0 (arr[$1]==$0?arr[$1]",Match":arr[$1]",No Match") }' OFS=, file1.txt file2.txt

id|description|name|date,Match
1|Row 1|a|2019-06-15 00:20:15:001|Row 1|a|2019-06-15 00:20:15:00,Match
2|Row 2|c|2019-06-16 15:18:10:00,No Match
4|Row 4|d|2019-06-25 09:00:01:004|Row 4|d|2019-06-25 09:00:01:00,Match
5|ROW 5|b|2019-06-25 22:00:00:00,No Match
7|Row 7|f|2019-06-17 07:02:17:00,No Match

Not sure why its only printing records from file1 & file2 when there is a match.

To give some more background on this - I am trying to use this awk command to find differences b/w files and then create a report which basically shows which columns have different values . idealy the end output will look like this

id|Change| Columns  
1|No Change|NA  
2|Change|name  
3|Exists only in file 1|NA  
4|No Change|NA  
5|Change|description,name  
7|Exists only in file 2|NA

Would really appreciate the guidance from all experts out here to achieve this.

You're not using the right Field Separator -- your files are pipe-separated, not comma-separated. — glenn jackman, Jun 20 '19 at 16:12

Ed Morton · Answer 1 · 2019-06-20T21:49:12.367

With GNU awk for arrays of arrays, gensub(), sorted_in, and ARGIND:

$ cat tst.awk
BEGIN { FS=OFS="|" }
FNR==1 { next }
{ vals[$1][ARGIND] = gensub("^[^"FS"]+["FS"]","",1) }
END {
    PROCINFO["sorted_in"] = "@ind_num_asc"
    for (id in vals) {
        print id, \
           (1 in vals[id] ? vals[id][1] : "N/A"),
           (2 in vals[id] ? vals[id][2] : "N/A"),
           (vals[id][1] == vals[id][2] ? "" : "No ") "Match"
    }
}

$ awk -f tst.awk file1 file2
1|Row 1|a|2019-06-15 00:20:15:00|Row 1|a|2019-06-15 00:20:15:00|Match
2|Row 2|b|2019-06-16 15:18:10:00|Row 2|c|2019-06-16 15:18:10:00|No Match
3|Row 3|c|2019-06-17 07:02:17:00|N/A|No Match
4|Row 4|d|2019-06-25 09:00:01:00|Row 4|d|2019-06-25 09:00:01:00|Match
5|Row 5|e|2019-06-25 22:00:00:00|ROW 5|b|2019-06-25 22:00:00:00|No Match
7|N/A|Row 7|f|2019-06-17 07:02:17:00|No Match

or if you prefer:

$ awk -f tst.awk file2 file1
1|Row 1|a|2019-06-15 00:20:15:00|Row 1|a|2019-06-15 00:20:15:00|Match
2|Row 2|c|2019-06-16 15:18:10:00|Row 2|b|2019-06-16 15:18:10:00|No Match
3|N/A|Row 3|c|2019-06-17 07:02:17:00|No Match
4|Row 4|d|2019-06-25 09:00:01:00|Row 4|d|2019-06-25 09:00:01:00|Match
5|ROW 5|b|2019-06-25 22:00:00:00|Row 5|e|2019-06-25 22:00:00:00|No Match
7|Row 7|f|2019-06-17 07:02:17:00|N/A|No Match

The "N/A"s will help you identify which of the 2 files didn't have a line for a given id. If you don't like that though then massage to suit.

Update: here's how to do it with any awk and sort:

$ cat tst.awk
BEGIN { FS=OFS="|" }
FNR==1 { argind++; next }
{
    id = $1
    ids[id]
    sub("^[^"FS"]+["FS"]","")
    vals[id,argind] = $0
}
END {
    for (id in ids) {
        print id, \
           ((id,1) in vals ? vals[id,1] : "N/A"),
           ((id,2) in vals ? vals[id,2] : "N/A"),
           (vals[id,1] == vals[id,2] ? "" : "No ") "Match"
    }
}

$ awk -f tst.awk file1 file2 | sort -t'|' -k1,1n
1|Row 1|a|2019-06-15 00:20:15:00|Row 1|a|2019-06-15 00:20:15:00|Match
2|Row 2|b|2019-06-16 15:18:10:00|Row 2|c|2019-06-16 15:18:10:00|No Match
3|Row 3|c|2019-06-17 07:02:17:00|N/A|No Match
4|Row 4|d|2019-06-25 09:00:01:00|Row 4|d|2019-06-25 09:00:01:00|Match
5|Row 5|e|2019-06-25 22:00:00:00|ROW 5|b|2019-06-25 22:00:00:00|No Match
7|N/A|Row 7|f|2019-06-17 07:02:17:00|No Match

Thanks a lot Ed. i am a newbie to gawk so really appreciate your help. Tried executing code (commented out few lines for testing) but its giving syntax error BEGIN { FS=OFS="|" } FNR==1 { next } { vals[$1][ARGIND] = gensub("^[^"FS"]+["FS"]","",1) } END { PROCINFO["sorted_in"] = "@ind_num_asc" for (id in vals) { print id } # (1 in vals[id] ? vals[id][1] : "N/A"), # (2 in vals[id] ? vals[id][2] : "N/A"), # (vals[id][1] == vals[id][2] ? "" : "No ") "Match" # } } — tech_sms169, Jun 20 '19 at 18:27
From the error message in [the answer you posted](https://stackoverflow.com/a/56692085/1745001) it looks like you aren't using GNU awk which I'd said was required. What does awk --version output? In any case, I added a version that'll work in any awk. — Ed Morton, Jun 20 '19 at 21:50

score 0 · Answer 2 · answered Jun 20 '19 at 18:33

Reading your request, it is easier to complete the whole task rather the intermediary step.

Here is a awk script that does the final task.

script.awk

BEGIN {FS = OFS = "|"; f[2]="descr"; f[3] = "name"; f[4] = "date "}
FNR == NR {        # read first input file
    lines[$1] = $0;
    next;
}
{                  # read scond input file
    if ($1 in lines) { # index exist in file 1
        if ($0 == lines[$1]) { # compare indexed lines
            print $1, "Same", "NA";
        } else { # indexed lines differ
            split(lines[$1], file1Fields); # read all fields from file 1 line
            unmatchedFields = "";
            for (m = 2; m <= 4; m++) {
                if (file1Fields[m] != $m) { # compare each field
                    fieldsSeparator = length(unmatchedFields) ? "," : "";
                    unmatchedFields = unmatchedFields fieldsSeparator f[m];
                }
            }
            print $1, "change", unmatchedFields;
        }
        delete lines[$1]; # clean handled lines from file1
    } else { # index not seen in file 1, it is only in file 2
        print $1, "only in file 2", "NA";
    }
}
END {
    for (j in lines) {  # index only in file 1
        print j, "only in file 1", "NA";
    }
}

input.1.txt

id|description|name|date  
1|Row 1|a|2019-06-15 00:20:15:00  
2|Row 2|b|2019-06-16 15:18:10:00  
3|Row 3|c|2019-06-17 07:02:17:00  
4|Row 4|d|2019-06-25 09:00:01:00  
5|Row 5|e|2019-06-25 22:00:00:00

input.2.txt

id|description|name|date  
1|Row 1|a|2019-06-15 00:20:15:00  
2|Row 2|c|2019-06-16 15:18:10:00  
4|Row 4|d|2019-06-25 09:00:01:00  
5|ROW 5|b|2019-06-25 22:00:00:00  
7|Row 7|f|2019-06-17 07:02:17:00

running:

awk -f script.awk input.1.txt input.2.txt |sort

output:

1|Same|NA
2|change|name
3|only in file 1|NA
4|Same|NA
5|change|descr,name,date
7|only in file 2|NA
id|Same|NA

Thanks Dudi. This works perfectly fine. Any thoughts on earlier solution of receiving syntax error on ARGIND ? — tech_sms169, Jun 20 '19 at 19:21
Tahnks @tech_sms169. Please express your gratitude with upvoting contributors and select an answer. — Dudi Boy, Jun 20 '19 at 21:57
I did not investigate other solutions to your quest, prior to contributing mine. Maybe I should. — Dudi Boy, Jun 20 '19 at 21:58

compare two files at key level (id) and display the differences at column level

2 Answers2