[ACCEPTED]-Replace Column if equal to a specific value-sed

Accepted answer

Score: 18

You can use the following awk:

awk -F, '{ $4 = ($4 == "N/A" ? -1 : $4) } 1' OFS=, test.csv

We set the input and output field separators to , to preserve the delimiters in your csv file
We check the forth field if it is equal to "N/A" then we assign it the value -1 if not we retain the value as is.
1 at the end prints your line with or without modified 4th column depending if our test was successful or not.
($4=="N/A"?-1:$4) is a ternary operator that checks if the condition $4=="N/A" is true or not. If true ? then we assign -1 and if false : we keep the field as is.

$ cat file
a,b,c,d,e,f
1,2,3,4,5,6
44,2,1,N/A,4,5
24,sdf,sdf,4,2,254,5
a,f,f,N/A,f,4

$ awk -F, '{ $4 = ($4 == "N/A" ? -1 : $4) } 1' OFS=, file
a,b,c,d,e,f
1,2,3,4,5,6
44,2,1,-1,4,5
24,sdf,sdf,4,2,254,5
a,f,f,-1,f,4

Score: 4

Here is another awk (using example data from 1 jaypal)

awk -F, '$4=="N/A" {$4=-1}1' OFS=, file
a,b,c,d,e,f
1,2,3,4,5,6
44,2,1,-1,4,5
24,sdf,sdf,4,2,254,5
a,f,f,-1,f,4

Score: 0

You could also do this in sed,

$ sed -r 's/^([^,]*),([^,]*),([^,]*),N\/A,(.*)$/\1,\2,\3,-1,\4/g' file
a,b,c,d,e,f
1,2,3,4,5,6
44,2,1,-1,4,5
24,sdf,sdf,4,2,254,5
a,f,f,-1,f,4

Score: 0

Here's an alternative for awk, and a shorter 5 regex for sed: I used a regex containing 4 the separator characters on both sides.

If 3 your file is tab separated you can use \t instead 2 of ,.

sed 's/,N\/A,/,-1,/g' infile.csv >  outfile.csv

Replace on the same file, good for big 1 files, test before using !!

sed -i 's/,N\/A,/,-1,/g' infile.csv

Cookie Warning

We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.