CODEWITHSUNDEEP
regexbashawksed
hismart
hismart

Reputation: 405

How to use sed or awk to replace string in csv file

Sorry for a really basic question. How to replace a particular column in a csv file with some string?

e.g.

id, day_model,night_model
===========================  
1 ,          ,          
2 ,2_DAY     ,          
3 ,3_DAY     ,3_NIGHT         
4 ,          , 
(4 rows)

I want to replace any string in the column 2 and column 3 to true others would be false, but not the 1,2 row and end row.

Output:

id, day_model,night_model
===========================      
1 ,false     ,false
2 ,true      ,false     
3 ,true      ,true         
4 ,false     ,false
(4 rows)

What I tried is the following sample code( Only trying to replace the string to "true" in column 3):

#awk -F, '$3!=""{$3="true"}' OFS=, file.csv > out.csv

But the out.csv is empty. Please give me some direction. Many thanks!!

Upvotes: 1

Views: 3004

Answers (2)

user2705585
user2705585

Reputation:

Well since you added sed in tag and you have only three columns I have solution for your problem in four steps because regex replacement was not possible for all cases in just one go.

Since your 2nd and 3rd column is having blank space. I wrote four sed commands to do the replacement for each kind of row.

  1. sed '/^(\d+\s+,)\S+\s*,\S+\s*$/\1true,true/gm' file.txt

    This will replace rows like 3 ,3_DAY ,3_NIGHT

    Regex101 Demo

  2. sed '/^(\d+\s+,)\S+\s*,\s*$/\1true,false/gm' file.txt

    This will replace rows like 2 ,2_DAY ,

    Regex101 Demo

  3. sed '/^(\d+\s+,)\s*,\S+\s*$/\1false,true/gm' file.txt

    This will replace rows like 5 , ,2_Day

    Regex101 Demo

  4. sed '/^(\d+\s+,)\s*,\s*$/\1false,false/gm' file.txt

    This will replace rows like 1 , ,

    Regex101 Demo

Upvotes: 1

glenn jackman
glenn jackman

Reputation: 247192

Since your field separator is comma, the "empty" fields may contain spaces, particularly the 2nd field. Therefore they might not equal the empty string.

I would do this:

awk -F, -v OFS=, '
    # ex
    NR>2 && !/^\([0-9]+ rows\)/ {
        for (i=2; i<=NF; i++) 
            $i = ($i ~ /[^[:blank:]]/) ? "true" : "false"
    }
    { print }
' file

Upvotes: 2

Related Questions