conditional based find and replace values in fields of csv file

Question

I want to find & replace values in column 5 & 6 based on condition given to column 1. If first column has value 2159 then it should replace value in column 5 by 13.49694 if it has value 13.512034 and replace value in column 6 by 78.22772 if it has value 78.226233. I tried with following command but it replacing all occurrences of 78.226233 by 78.22772 in column 6. What I want is it should replace only for 2159 value in column 1.

awk -F ',' -v OFS=',' '$1 ~ /^2159/ && $5=="13.512034"{$5="13.49694"}1 && $6=="78.226233"{$6="78.22772"}1' 14update1.csv > 14update2.csv

Is there any way to update the change in same file? I am pretty new to awk and shell scripting so I apologize if this is an easy fix.

The datafile I have is something like:

2159,KOLAR,SRINIVASAPURA,GAUNIPALLI (KODIPALLI) (GP),13.49694,78.22772,14-08-17,22,0
2159,KOLAR,SRINIVASAPURA,GAUNIPALLI (KODIPALLI) (GP),13.49694,78.22772,14-08-17,23,0
2159,KOLAR,SRINIVASAPURA,GAUNIPALLI (KODIPALLI) (GP),13.49694,78.22772,14-08-17,23,0
2159,KOLAR,SRINIVASAPURA,GAUNIPALLI (KODIPALLI) (GP),13.49694,78.22772,14-08-17,23,0
2159,KOLAR,SRINIVASAPURA,GAUNIPALLI (KODIPALLI) (GP),13.49694,78.22772,14-08-17,23,0
3358,KOLAR,SRINIVASAPURA,Gownipalli (GP),13.512034,78.22772,14-08-17,0,0
3358,KOLAR,SRINIVASAPURA,Gownipalli (GP),13.512034,78.22772,14-08-17,0,0
3358,KOLAR,SRINIVASAPURA,Gownipalli (GP),13.512034,78.22772,14-08-17,0,0
3358,KOLAR,SRINIVASAPURA,Gownipalli (GP),13.512034,78.22772,14-08-17,0,0
3317,KOLAR,SRINIVASAPURA,Hodali (GP),13.358552,78.269189,14-08-17,0,0
3317,KOLAR,SRINIVASAPURA,Hodali (GP),13.358552,78.269189,14-08-17,0,0
3317,KOLAR,SRINIVASAPURA,Hodali (GP),13.358552,78.269189,14-08-17,0,0
3317,KOLAR,SRINIVASAPURA,Hodali (GP),13.358552,78.269189,14-08-17,0,0
3317,KOLAR,SRINIVASAPURA,Hodali (GP),13.358552,78.269189,14-08-17,1,0
3317,KOLAR,SRINIVASAPURA,Hodali (GP),13.358552,78.269189,14-08-17,1,0
3317,KOLAR,SRINIVASAPURA,Hodali (GP),13.358552,78.269189,14-08-17,1,0
3317,KOLAR,SRINIVASAPURA,Hodali (GP),13.358552,78.269189,14-08-17,1,0

Jotne · Accepted Answer · 2019-09-26T06:40:58.493

2

This awk should do:

cat file
2159,23,45,45,13.512034,78.226233

awk -F, -v OFS="," '$1==2159 && $5==13.512034 {$5="13.49694"} $1==2159 && $6==78.226233 {$6="78.22772"} 1' file
2159,23,45,45,13.49694,78.22772

This $1 ~ /^2159/ does starts with, not equal to. $1=2159 or $1~/^2159$/

edited Sep 26 '19 at 06:40

answered Sep 26 '19 at 06:27

Jotne

40,548
12
51
55

Is there any way to update the output into same file instead of directing console output to new file. – Ajay Sep 26 '19 at 06:55
@Ajay Update file is done like this `awk 'comands' file > tmp && mv tmp file` – Jotne Sep 26 '19 at 07:51
@Ajay Also, if you are using recent enough GNU awk version: https://stackoverflow.com/a/16531920/4162356 . – James Brown Sep 26 '19 at 09:33

score 1 · Answer 2 · edited Sep 26 '19 at 08:15

1

Using sub():

$ awk '
BEGIN {
    FS=OFS=","
}  
$1==2159 {                             # only one condition 
    sub(/^13\.512034$/,"13.49694",$5)   # or if($5=="13.512034") $5="13.49694"
    sub(/^78\.226233$/,"78.22772",$6)   # ditto
} 
1' file

Output:

2159,23,45,45,13.49694,78.22772

edited Sep 26 '19 at 08:15

RavinderSingh13

130,504
14
57
93

answered Sep 26 '19 at 06:36

James Brown

36,089
7
43
59

conditional based find and replace values in fields of csv file

2 Answers2