1

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"
2

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"
0

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
  • My understanding is that the header of file1.csv shows which column of file2.csv you should inspect. It's not all columns. – Sparhawk Apr 11 at 4:44
  • You might be right, thanks! I've edited my answer. – finswimmer Apr 11 at 5:33
0

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"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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