Thomas Paine Thomas Paine - 4 months ago 9
Linux Question

Match data in Any Column, and change data over all Other Columns in Same Row

I'm trying to figure out how to search a csv file for a value, in this case "---", and change all proceeding columns in the same row to "---".

I have been looking to do this with awk, but I can only figure out how to check for known fields,

i.e.-

awk '{if ($(NF-1)=="---")$NF="---"}{print $0}' file


I need to find a way to use a for loop, I think(that's why I'm asking) to:

1) Search all the fields for a value

2) Find the value in a field, and change all proceeding fields of the same record to a specific value (i.e.- "---" )

Any ideas will be highly appreciated. And I apologize if my wording doesn't convey all the different trial and error attempts I have made at this, I would like to know what does work instead of showing everybody what does not.

Answer

Let's consider this test file:

$ cat file.csv
a,b,---,d,e
1,---,3,4,5

To look for --- and change all preceding columns in the same row to ---:

$ awk -F, '{f=0; for (i=NF;i>=1;i--) {$i=(f?"---":$i); f=($i=="---")}} 1' OFS=, file.csv
---,---,---,d,e
---,---,3,4,5

Alternatively, to look for --- and change all subsequent (succeeding) columns in the same row to ---:

$ awk -F, '{f=0; for (i=1;i<=NF;i++) {$i=(f?"---":$i); f=($i=="---")}} 1' OFS=, file.csv
a,b,---,---,---
1,---,---,---,---