[SOLVED] How to replace some cells number of .csv file if specific lines found in Linux

Issue

Lets say I have the following file.csv file content

"US","BANANA","123","100","0.5","ok"
"US","APPLE","456","201","0.1", "no"
"US","PIE","789","109","0.8","yes"
"US","APPLE","245","201","0.4","no"

I want to search all lines that have APPLE and 201, and then replace the column 5 values to 0. So, my output would look like

"US","BANANA","123","100","0.5","ok"
"US","APPLE","456","201","0", "no"
"US","PIE","789","109","0.8","yes"
"US","APPLE","245","201","0","no"

I can do grep search

grep "APPLE" file.csv | grep 201

to find out the lines. But could not figure out how to modify column 5 values of these lines in the original file.

Solution

You can use awk for this:

awk -F, '$2=="\"APPLE\"" { for (i=1;i<=NF;i++) { if ($i=="\"201\"") { gsub($5,"\""substr($5,2,length($5)-1)*1.10"\"",$5) } } }1' file.csv

Set the field delimiter to , and then when the second field is equal to APPLE in quotes, loop through each field and check if it is equal to 201 in quotes. If it is, replace the 5th field with 0 in quotes using Awk's gsub function. Print each line, changed or otherwise with short-hand 1

Answered By - Raman Sailopal