Find and replace a column that has '' to NULL in a comma delimited using awk or sed

Shell Programming and Scripting
1

Hi this is my first time posting ever. I'm relatively new in using AWK/SED, I've been trying many a solution. I'm trying to replace the 59th column in a file where if I encounter '' then I would like to replace it with the word NULL.

example

0 , '' , '' , 0 , 195.538462

change it to

0 , NULL , '' , 0, 195.538462

Any ideas Cheers

2

for your example here:

#  nawk -F, '{$2="\""?"NULL":$2;print}' OFS="," infile
0 ,NULL, '' , 0 , 195.538462

for 59th column:

#  nawk -F, '{$59="\""?"NULL":$59;print}' OFS="," infile

HTH

3

Thanks it worked but I had other values in that column it was only if it encountered '' that it had to be changed to NULL my apologies for that

4

You can try this :wink:

#!/bin/bash
#goal is the change value in column 59 if equal ''
oIFS=$IFS
cp mydata mydatatmp
IFS=' , '
while read char
     do
       changecharacter=$( (echo "$char" | tr -d , | awk {'print $59'}) )

        if [ $changecharacter = "''" ]
            then
                changelineport=$( (cut -d , -f1-59 mydata) )
                newlineport=$( (cut -d , -f1-58 mydata) )
                sed -i "s/$changelineport/$newlineport, NULL /" mydata
        fi
     done < mydatatmp
cat mydata
rm mydatatmp
IFS=$oIFS
5

Hello, gumal901, and welcome to the forums.

There is whitespace around the single quotes to be replaced, in the sample data. Since the problem statement does not specify that the whitespace should be removed, the following code matches a field that contains two consecutive single-quotes with optional leading and trailing whitespace. When the quotes are replaced with "NULL", the whitespace is left intact.

awk -F, '$59 ~ /^[[:blank:]]*'"''"'[[:blank:]]*$/ {sub(/'"''"'/,"NULL", $59)}; {print}' OFS=, file

Regards,
Alister

---------- Post updated at 04:54 PM ---------- Previous update was at 04:51 PM ----------

That code will always replace the value of $59 with "NULL", which I doubt is what was intended.

6

Thanks Ygemici and Alister, I did not mention it however I did require the whitespace to stay intact and it did work perfectly. Thanks again.