How to compare two CSV files and display unique records?

Question

I have two csv file as below, i want to compare file1 with file2 and if records present in file2 that complete row should remove from file1. field to compare here is ID and in original file its at 11th position.

FILE1.CSV

"NAME","CITY","MARKS","ID","C","NAME1","TYPE"
"A","XY","100","12","","31420","TYPE1"
"A","XY","100","13","","31420","TYPE1"

FILE2.CSV

"ID"
"11"
"12"
"25"

EXPECTED OUTPUT

"NAME","CITY","MARKS","ID","C","NAME1","TYPE"
"A","XY","100","13","","31420","TYPE1"

Answer 1

Using the utilities from csvkit (package python3-csvkit on my Ubuntu system):

$ csvsql --query '
  SELECT * FROM FILE1 WHERE ID NOT IN (SELECT ID FROM FILE2)
' FILE1.CSV FILE2.CSV | csvformat -U1
"NAME","CITY","MARKS","ID","C","NAME1","TYPE"
"A","XY","100","13","","31420","TYPE1"

Answer 2

With Miller (https://github.com/johnkerl/miller/releases/tag/5.4.0) is

mlr --csv join --np --ul -j ID -f input_01.csv input_02.csv

Some notes:

• --np to not emit paired records
• --ul to emit unpaired records from the left file

The left file is input_01.csv

"NAME","CITY","MARKS","ID","C","NAME1","TYPE"
"A","XY","100","12","","31420","TYPE1"
"A","XY","100","13","","31420","TYPE1"

Answer 3

grep -v -wf file2.csv file1.csv will print each line of file1.csv which does not contain any word of file1.csv.

Of course the header line is missing now. If you need it do this:

$ head -n1 file1.csv;grep -v -wf file2.csv file1.csv

If the first line of file2.csv defines the column where the words shouldn't match, here's a awksolution:

$ awk -v FS="," '
    NR==FNR && NR==1 {column=$1; next;} # save the column name to which one will compare
    NR==FNR {data[$1]++; next;} # save the list of words to match again
    NR!=FNR && FNR==1 {print; for(i=1;i<=NF;i++) {if($i==column) c=i}; next;}  # print header line of file1, find column number to which one will compare
    {if ($c in data == 0) print} # for any following line check if the word is not in our list
' file2.csv file1.csv