How to match and change strings in a column of a semicolon separated file?

Question

I have a semicolon separated csv-file which looks like this:

column1;column2;;123564;128;;IJL;value;;;;;3705;;;;;;;;
column1;column2;;26789786413423;;CCE;value value;;;;;;3705;;;;;;;;
column1;column2;;4564564;128;;SSE;value;;;;;;;;;;;;;
column1;column2;;4645646;128;;JJY;someting X;;;;;;;;;;;;;
column1;column2;;123132;128;;ASA;X value;;;;;;;;;;;;;
column1;column2;;45643123;128;;TT;9 someting;;;;;;;;;;;;;
column1;column2;;456464;128;;KK;VALUE 9 VALUE;;;;;;;;;;;;;
column1;column2;;4646;128;;ST;value 6;;;;;;;;;;;;;
column1;column2;;456464;128;;NX;7 something;;;;;;;;;;;;;

I want to find a specific value/string in column 8, and replace that with something else. The problem I have is I can't find a sed or awk to work that edits column 8 only if it gets an exact match.

I want to achive something like this (does not work):

awk -F";" '$8=="value" {gsub(/$8/,"column 8");print;}' infile.csv >outfile.csv

I want the entire column 8 to be edited if I get an exact match in column 8 for the string "value". So I don't want column 8 which has "value value" or "X value" to change.

It doesn't matter if it's a sed or awk command, and if possible I prefer to edit the file directly instead of using a in/out-file. There is a chance the matching string occurs in other columns that is why it is also important I only search in column 8.

Any ideas how this could be done?

Answer 1

There's no need for a separate condition with gsub - you can just apply it to each record and it won't do anything for those that don't match:

awk -F\; -v OFS=";" '{gsub(/value/,"column 8",$8)}1' infile.csv > outfile.csv

It is very important that you escape/quote the ; so that it isn't interpreted by the shell! Also, as pointed out in the comments (thanks), you also need to set the output field separator so that lines touched by awk remain semicolon-separated.

If you want, you can add anchors to the start and end of the field for an exact match, by changing the pattern to /^value$/.

The 1 at the end is just a shorthand for print (as it is always true and the default action is to print the record).

Answer 2

With sed

sed -i 's/^\(\([^;]*;\)\{7\}\)value;/\1column 8;/' file

This capture the first seven fields in a capture group, checks the 8th is exactly value, then replaces the string with the capture string and the replacement text.

-i is for inplace