1

I want to compare two csv files with one common column and then replace the data from first file to second file at particular location.

file1.csv:

"id","name","marks","subject","result"
"1","a","","art",""
"4","c","","math",""
"3","b","","history",""
"5","d","","comerce",""

file2.csv:

"id","marks","result"
"4","40","pass"
"3","30","fail"
"1","20","fail"
"6","10",""
"5","10",""

output should be:

"id","name","marks","subject","result"
"1","a","20","art","fail"
"4","c","40","math","pass"
"3","b","30","history","fail"
"5","d","10","comerce",""

I am using the awk statement as below but it merging the value at last not adding at particular location:

awk 'NR==FNR{a[$1]=$2; b[$1]=$3; next}  {print $0, a[$2], b[$2] } file1.csv file2.csv 
  • "it merging the value at last" doesn't make sense to me, perhaps an example wrong output to save people time. Also, it's "commerce" FWIW. This question seems very similar, and the suggestion to use join [then awk, perhaps] seems a good one, especially if you have a common key. – pbhj May 9 '18 at 21:27
1
awk '
  BEGIN{OFS=FS=","} 
  NR==FNR {
    marks[$1]=$2; status[$1]=$3; next
  } 
  $1 in marks {$3 = marks[$1]} 
  $1 in status {$5 = status[$1]}
  1' file2.csv file1.csv
"id","name","marks","subject","result"
"1","a","20","art","fail"
"4","c","40","math","pass"
"3","b","30","history","fail"
"5","d","10","comerce",""
  • Ok Thanks for your answer. Will it work for more number of columns say 45 ? – user828007 May 10 '18 at 11:37
  • @user828007 I can't answer that - whether it "works" will depend on what you want to happen to the additional columns – steeldriver May 10 '18 at 11:41

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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