0

This question already has an answer here:

I have two pipe delimited files as below

File 1

TEST|123456
TEST|123457
TEST|123458
TEST|123459
TEST|123460

File 2

TEST|123456|RAY|DOE||
TEST|123457|JANE|DOE||
TEST|123458|ROSE|DAWN||
TEST|123459|GEORGE|BRID||
TEST|123460|RALPH|DOE||
TEST|123461|JOHN|DOE||
TEST|123462|STEVE|SMITH||

File 3 - Desired Output

TEST|123461|JOHN|DOE||
TEST|123462|STEVE|SMITH||

I want to use the first and second column of file1 and file2 as key to getting the unmatched rows from file 2.

marked as duplicate by Sparhawk, Mr Shunz, Thomas, msp9011, Isaac Jan 25 at 15:12

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 1
    Is there a reason why you want to include the first column in your key? It's unnecessary if the entries are all the same (i.e. "TEST") in the first column. – Niko Gambt Jan 25 at 6:31
2

join from GNU coreutils has a -v option which prints un-pairable lines from one of the two source files. Using that to invert the matches works well:

$ cat file1.txt
TEST|123456
TEST|123457
TEST|123458
TEST|123459
TEST|123460

$ cat file2.txt
TEST|123456|RAY|DOE||
TEST|123457|JANE|DOE||
TEST|123458|ROSE|DAWN||
TEST|123459|GEORGE|BRID||
TEST|123460|RALPH|DOE||
TEST|123461|JOHN|DOE||
TEST|123462|STEVE|SMITH||

$ join -t '|' -j 2 -o '2.1,2.2,2.3,2.4' -v 2 file1.txt file2.txt
TEST|123461|JOHN|DOE
TEST|123462|STEVE|SMITH
1

Try:

$ awk -F'|' 'FNR==NR{a[$1,$2]=1; next}  !a[$1,$2]' file1 file2
TEST|123461|JOHN|DOE||
TEST|123462|STEVE|SMITH||

How it works

  • -F'|' tells awk to use | as the field separator.

  • FNR==NR{a[$1,$2]=1; next}

    For lines belonging to the first file (FNR==NR), this tells awk to add an entry to associative array a under the key of $1,$2 with a value of 1.

  • !a[$1,$2]

    This tells awk to print any line for which !a[$1,$2] is true. Since ! is logical negation, this means that any line is printed for which a[$1,$2] is false (zero).

    This means that the lines for which we set a[$1,$2]=1 will be skipped (not printed). Other lines will be printed.

0

Grep has an option to obtain the regexes to match from a file.
To make the firtst file a regex we need only to add a ^ to the start of each line. That could be done with a sed command like sed 's/.*/^&/' file1.

Then we supply the output of sed to grep like this (ksh, zsh, bash):

grep -vf <(sed 's/.*/^&/' file1) file2
-1

Software tools method:

sort -r file[12] | uniq -u -w11 | tac

Not the answer you're looking for? Browse other questions tagged or ask your own question.