2

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.

Bala
  • 221
  • 4
  • 11
  • 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 '19 at 06:31

4 Answers4

4

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
Haxiel
  • 8,201
  • 1
  • 20
  • 30
3

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.

John1024
  • 73,527
  • 11
  • 167
  • 163
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
agc
  • 7,045
  • 3
  • 23
  • 53