CODEWITHSUNDEEP
regexlinuxcsvawksed
gaspero1
gaspero1

Reputation: 129

How to set a column to a value, if a substring appears anywhere on a line in a CSV?

I have a csv file that has entries like this:

id,name,status,taglist
1,nobody,closed,"something,something else,rambling,blah,No Show,blah"
2,quitter,closed,"something,stuff,nonsense,blah"
3,somebody,accepted,"something,more stuff,sure thing,blah"

When 'no show' appears in the taglist column, I need to change 'closed' to 'no show' in the status column. I'm able to find the no show lines like this: 

awk '/No Show/' < myfile.csv > noshowsonly.csv

But I can't seem to figure out how to only update the status column so that the result would look like this:

id,name,status,taglist
1,nobody,No Show,"something,something else,rambling,blah,No Show,blah"
2,quitter,closed,"something,stuff,nonsense,blah"
3,somebody,accepted,"something,more stuff,sure thing,blah"

I've tried lines like:

awk '{if($4 ~ /No Show/) { $3="No Show"}}1' < myfile.csv > updated.csv

But that isn't working and I'm stumped.

Upvotes: 2

Views: 59

Answers (2)

Beta
Beta

Reputation: 99094

A sed solution:

sed '/No Show/s/\([^,]*,[^,]*,\)closed,/\1No Show,/'

To write a new file as in your example:

sed '/No Show/s/\([^,]*,[^,]*,\)closed,/\1No Show,/' myfile.csv > updated.csv

Upvotes: 0

Ed Morton
Ed Morton

Reputation: 203532

With GNU awk for FPAT and assuming you really only want to test the 4th field rather than the whole line:

$ awk -v FPAT='([^,]*)|("[^"]*")' -v OFS=',' '$4 ~ /No Show/{$3="No Show"} 1' file
id,name,status,taglist
1,nobody,No Show,"something,something else,rambling,blah,No Show,blah"
2,quitter,closed,"something,stuff,nonsense,blah"
3,somebody,accepted,"something,more stuff,sure thing,blah"

or if you want to get even more specific of which subfield in the 4th field to test:

$ awk -v FPAT='([^,]*)|("[^"]*")' -v OFS=',' '{split($4,f,OFS)} f[5]=="No Show"{$3="No Show"} 1' file
id,name,status,taglist
1,nobody,No Show,"something,something else,rambling,blah,No Show,blah"
2,quitter,closed,"something,stuff,nonsense,blah"
3,somebody,accepted,"something,more stuff,sure thing,blah"

otherwise it's just:

$ awk 'BEGIN{FS=OFS=","} /No Show/{$3="No Show"} 1' file
id,name,status,taglist
1,nobody,No Show,"something,something else,rambling,blah,No Show,blah"
2,quitter,closed,"something,stuff,nonsense,blah"
3,somebody,accepted,"something,more stuff,sure thing,blah"

Upvotes: 1

Related Questions