unix command to replace a string with null in a csv file

Question

I have a csv file with multiple headers and their corresponding records as below-

NA,A,B,C
$H,1,2,3
NA,D,E,F
$R,4,5,6
NA,G,H,I
$R,7,8,9
NA,J,K,L
$R,10,11,12

Please note the rows starting with NA are headers. need to replace 'NA' with null. So my expected output will be-

,A,B,C
$H,1,2,3
,D,E,F
$R,4,5,6
,G,H,I
$R,7,8,9
,J,K,L
$R,10,11,12

I have tried the below code , but did not work-

awk -F '|' -v OFS='|' '$1 == "NA" { $1 = "" }1' test.csv >test_n.csv

it's always the first line, you don't need to check the entire file, right? — Diego Torres Milano
– Diego Torres Milano, Commented Mar 31, 2022 at 20:16
Please look up FS and OFS in the awk man page and ask yourself why you're setting them to |. If sed 's/NA//' isn't all you need then edit your question to show NAs is some other contexts where you don't want them removed. — Ed Morton
– Ed Morton, Commented Apr 1, 2022 at 1:43

anubhava · Accepted Answer · 2022-03-31 21:22:22Z

2

Using a fairly simple sed:

sed 's/^NA,/,/' file

,A,B,C
$H,1,2,3
,D,E,F
$R,4,5,6
,G,H,I
$R,7,8,9
,J,K,L
$R,10,11,12

answered Mar 31, 2022 at 21:22

anubhava

790k67 gold badges603 silver badges671 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sseLtaH · Accepted Answer · 2022-03-31 20:16:29Z

1

Changing the FS in your code would have worked

$ awk 'BEGIN{FS=OFS=","}$1=="NA"{$1=""}1' input_file
,A,B,C
$H,1,2,3
,D,E,F
$R,4,5,6
,G,H,I
$R,7,8,9
,J,K,L
$R,10,11,12

answered Mar 31, 2022 at 20:16

sseLtaH

11.3k5 gold badges17 silver badges34 bronze badges