0

I have a requirement to match column 4 (Grade) from File1 with column 1 (Grade) of File2. If it matches, replace column 4 (Grade) of File1 with column2 (Marks) of File2. If it doesn't match, then leave the value of column 4 (Grade) as is.

File1

"RollNo","Name","Age","Grade","Class","Teacher"
"1","Asha","7","A","1","David"
"2","Mona","7","B","1","David"
"3","Sonali","7","C","1","David"
"4","Rani","7","D","1","David"
"5","Raj","7","B","1","David"

File2

"Grade","Marks"
"A","90"
"B","80"
"C","70"

Expected output:

"RollNo","Name","Age","Grade","Class","Teacher"
"1","Asha","7","90","1","David"
"2","Mona","7","80","1","David"
"3","Sonali","7","70","1","David"
"4","Rani","7","D","1","David"
"5","Raj","7","80","1","David"

Can you please help me with an awk command for this output?
Looking ahead for your response.

Improve this question
1
  • Can any field contain ,, ", or newline (\n)? Do your records end with \n (common in Unix) or \r\n(common in files created on Windows, e.g. a CSV exported from MS-Excel)? Please tag your question with the OS and shell you use.
    – Ed Morton
    Commented Feb 7, 2021 at 15:39

1 Answer 1

Reset to default
2

Assuming the fields in your CSV can't contain ,s or newlines (\n) and you're running a Unix shell:

$ awk '
    BEGIN { FS=OFS="," }
    NR==FNR { map[$1] = $2; next }
    (FNR>1) && ($4 in map) { $4 = map[$4] }
1' file2 file1
"RollNo","Name","Age","Grade","Class","Teacher"
"1","Asha","7","90","1","David"
"2","Mona","7","80","1","David"
"3","Sonali","7","70","1","David"
"4","Rani","7","D","1","David"
"5","Raj","7","80","1","David"
Improve this answer

You must log in to answer this question.

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