0

i am trying to compare two csv files and update each matching line in first file

Example :

file1.csv

col1,col2,col3,col4
1,11,111,1111
2,22,222,2222
3,33,333,3333

file2.csv

col1,col2,col3,col4
X,11,111,XXXX
Y,22,222,YYYY
Z,ZZ,ZZZ,ZZZZ

Now i want to compare col2 and col3 between these two files, if match found then update file one for matching lines.

Output file1.csv:

col1,col2,col3,col4
1,11,111,1111 match found
2,22,222,2222 match found
3,33,333,3333 match not found
4
  • 1
    If col2 and col3 match, or if either of them matches?
    – Philippos
    Aug 17, 2022 at 10:16
  • 1
    csv would allow a four column line like 1,"1,1",111,1111. Can this happen here?
    – Philippos
    Aug 17, 2022 at 10:20
  • want to match both col2 and col3
    – amit yadav
    Aug 17, 2022 at 14:43
  • 1
    Wouldn't adding a 5th comma-separated column named "match" to contain just "found" or "not found" make more sense (and be easier to do further processing with) than appending " match found" or " match not found" onto the end of the existing 4th comma-separated field on each data line?
    – Ed Morton
    Aug 17, 2022 at 14:48

2 Answers 2

6

Using awk

$ awk -F, 'NR==FNR {a[$2,$3];next} FNR>1 {$0=$0 (($2,$3) in a?" match found" : " match not found")}1' file2.csv file1.csv
col1,col2,col3,col4
1,11,111,1111 match found
2,22,222,2222 match found
3,33,333,3333 match not found
1
  • 2
    a few comments would help newcomers to the beauty of awk: NR=number of lines read from input so far, FNR=number of lines read from current file only. So NR==FNR is only true when reading the first txt file, and for the next files NR>FNR. The NR==FNR { actions ; next } : allow one to describe what to do when reading the first file (the "next" instruction bypasses the rest of the awk instructions and forces awk to process the "next" line). so the rest of the instructions will only be done when NR!=FNR (as there will be no "next" bypassing them). Aug 17, 2022 at 12:39
0

If (1) there will be no commas in the files other than the field-separating ones and (2) both fields must match, you can paste both files and handle matches with sed:

paste file1.csv file2.csv | sed '1s/\t.*//p;1d;s/\(,.*,.*,\)\(.*\)\t.*\1.*/\1\2 match found/;s/\t.*/ match not found/'

(\t stands for a literal tab, entered by ctrl-v, followed by the tab key)

  • 1s/\t.*//p prints the first line, first removing the dupication; 1d then stops further operations on the first line
  • s/\(,.*,.*,\)\(.*\)\t.*\1.*/\1\2 match found/ does a substitution if the pattern with the three commas (always second and third field) is repeated (\1) after the tab
  • s/\t.*/ match not found/ does handle the not-matching case

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .