CSV file REPLACE COLUMN if it matches

Shell Programming and Scripting
1

I have a file

cat 1.txt
AAAA , BBBB , CCCC , DDDD
DFDF , DFDF , DFDF , FDDD
AA11 , DFDF , 0000 , UTIO
ADSD , WERT, 0000 , JKJL

If the 3rd column is not equal to "0000" , then it should replace "0000" with "XXXX" and if its equal to "0000" then print the line as it is.

need help.

2

Hello Aravindj80,

Could you please use code tags for commands and codes which you are using in your post. This is one of the very important rule for this forum which you have agreed during your user account creation. you can refer the forum rule guidlines also for same as follows.

Following may help you in your requirement.

awk -F"," '($3 !~ 0) {gsub(/.*/," XXXX ",$3);} 1' OFS=","   filename

Output will be as follows.

AAAA , BBBB , XXXX , DDDD
DFDF , DFDF , XXXX , FDDD
AA11 , DFDF , 0000 , UTIO
ADSD , WERT, 0000 , JKJL

Thanks,
R. Singh

3

I tried it , but all the rows are getting replaced with XXXX

4 
awk -F '[ ]*,[ ]*' '$3 != "0000" {$3="XXXX"}1' OFS=, file

If you want spaces before and after ,

awk -F '[ ]*,[ ]*' '$3 != "0000" {$3="XXXX"}1' OFS=' , ' file
5

Hi Aravind,

Code which I have given is working fine for me, could you please show us which output you are getting along with your OS details and please use code tags while using commands/codes.

Thanks,
R. Singh

6

Thanks it worked great , if i want to start the replacement from the 2nd line as first line has header , how it is possible ?

7

Hi Aravind,

If you are referring to my solution then following may help.

awk -F"," ' NR>1 && ($3 !~ 0) {gsub(/.*/," XXXX ",$3);} 1' OFS=","   filename

if you are reffering to Srini's solution then kindly add NR>1 in the starting of the solution.

Thanks,
R. Singh

8

@RavinderSingh13: You certainly meant to write NR>1 && ($3 !~ 0) ?

1 Like
9

Thanks Ravinder your code worked perfect , but i was trying with the srini code also but where to add NR>1 ?

10 
awk -F '[ ]*,[ ]*' 'NR > 1 && $3 != "0000" {$3="XXXX"}1' OFS=' , ' file