Compare two columns of a csv file and show unmatched records only

Question

I have a csv file in below format

1311,0008,a131,a131,7039
1311,0008,a131,a132,7039
1711,2046,a626565,a626566,7099
1711,2048,a626565,a626565,7035

What I want is to compare column 3 and 4 only and if they do not match, print that line in a new file and if they match print that line in another file

Expected File1 output (where col 3 & 4 do not match) :

1311,0008,a131,a132,7039
1711,2046,a626565,a626566,7099

Expected output in File2 (where col 3 & 4 match) :

1311,0008,a131,a131,7039
1711,2048,a626565,a626565,7035

So far I have tried the comparison with multiple files only.

Answer 1

awk -F, '{ print >($3==$4?"matchedFile":"notMatchedFile") }' infile

that's selectively redirecting lines to one of the two output files, if column#3 was equally full string match with column#4 (columns are delimited by comma character, it's specified by -F,), write to matchedFile output file, else writes to notMatchedFile.

see other matching options here.

---

$ head matchedFile notMatchedFile
==> matchedFile <==
1311,0008,a131,a131,7039
1711,2048,a626565,a626565,7035

==> notMatchedFile <==
1311,0008,a131,a132,7039
1711,2046,a626565,a626566,7099

---

or similarly but make command more compact:

awk -F, '{ print >"file"($3==$4) }' infile

---

$ head file[01]
==> file0 <==
1311,0008,a131,a132,7039
1711,2046,a626565,a626566,7099

==> file1 <==
1311,0008,a131,a131,7039
1711,2048,a626565,a626565,7035

Answer 2

Two really short commands, just to keep it simple:

awk -F, '$3 != $4' file.csv >file1

awk -F, '$3 == $4' file.csv >file2

Both of these two commands treat the lines in file.csv as a set of comma-delimited fields. Whenever the the third field is not equal to the fourth field, the first command writes the current line (and it is written to file1 via an output redirection). The second command does the same but with the opposite logic (and the output goes to file2).

Using a single command is slightly more complex, but allows you to name the output files on the command line with simple redirections from file descriptors 3 and 4:

$ awk -F, '{ fd = $3 == $4 ? 4 : 3;  print >("/dev/fd/" fd) }' file.csv 3>file1 4>file2
$ cat file1
1311,0008,a131,a132,7039
1711,2046,a626565,a626566,7099
$ cat file2
1311,0008,a131,a131,7039
1711,2048,a626565,a626565,7035

Or the same thing, but more obfuscated,

awk -F, '{ print >("/dev/fd/" 3 + ($3 == $4)) }' file.csv 3>file1 4>file2

Answer 3

GNU sed method:

F="[^,]*,"
sed -En "
  /^($F){2}($F)\\2/w match.csv"'
  //!w nomatch.csv
' file.csv

Result are stored in match/nomatch .CSV files in the current directory.