How can I replace all occurrences of a value in a text file just in one column using the Sed command in shell script (columns are seperated by ;)? - Ubuntu

AymanSalama
December 28, 2022
277 views
2 votes
3 Answers

I have a file that has columns seperated by a semi column(;) and I want to change all occurrences of a word in a particular column only to another word. The column number differentiates based on the variable that holds the column number. The word I want to change is stored in a variable, and the word I want to change to is stored in a variable too.

I tried

sed -i "s/<$word>/$wordUpdate/g" $anyFile

I tried this but it changed all occurrences of word in the whole file! I only want in a particular column

the number of column is stored in a variable called numColumn
and the columns are seperated by a semi column ;

Answers

It is much simpler to use awk for column edits, e.g. if your input looks like this:

68;61;83;27;60;70;84;11;46;62;93;97;40;23;19
33;70;17;49;81;21;68;83;16;6;42;38;68;81;89
73;40;95;64;32;33;77;56;23;11;70;28;33;80;24
8;9;74;6;86;78;87;41;11;79;23;28;71;99;15
29;87;77;9;98;12;7;66;60;85;20;14;55;97;17
39;24;21;58;23;61;39;26;57;70;76;16;70;53;8
37;46;18;64;56;28;86;7;80;71;94;46;19;53;43
71;2;47;62;9;21;68;9;9;80;32;59;73;74;72
20;34;89;58;74;92;86;35;48;81;50;6;63;67;90
78;17;6;63;61;65;75;31;33;82;24;5;90;46;12

You can replace 60 in column c with s with something like this:

<infile awk '$c ~ m { $c = s } 1' FS=';' OFS=';' c=5 m=60 s=XX

Output:

68;61;83;27;XX;70;84;11;46;62;93;97;40;23;19
33;70;17;49;81;21;68;83;16;6;42;38;68;81;89
73;40;95;64;32;33;77;56;23;11;70;28;33;80;24
8;9;74;6;86;78;87;41;11;79;23;28;71;99;15
29;87;77;9;98;12;7;66;60;85;20;14;55;97;17
39;24;21;58;23;61;39;26;57;70;76;16;70;53;8
37;46;18;64;56;28;86;7;80;71;94;46;19;53;43
71;2;47;62;9;21;68;9;9;80;32;59;73;74;72
20;34;89;58;74;92;86;35;48;81;50;6;63;67;90
78;17;6;63;61;65;75;31;33;82;24;5;90;46;12

- potong
- December 28, 2022 at 3:46 pm
- 0 votes
0
This might work for you (GNU sed):
```
word=foo wordUpdate=bar numColumn=3
sed -i 'y/;/n/
        s#.*#echo "&" | sed "'${numColumn}'s/<'${word}'>/'${wordUpdate}'/"#e
        y/n/;/' file
```
Convert each line into a separate file where the columns are lines.

Substitute the matching line (column number) with the word for the updated word.

Reverse the conversion.

N.B. The solution relies on the GNU only e evaluation flag. Also the word and updateWord may need to be quoted.
Login or Signup to reply.

- PaulHodges
- December 28, 2022 at 5:52 pm
- 0 votes
0
This can be done with a little creativity…
Note that I’m using double-quotes to embed the logic. This takes a little extra care to double your ‘s on backreferences.
```
$: word=baz; c=3; new=XX; lead="^([^;]*;){$((c-1))}"; sed -E "/$lead$word;/{s/($lead)$word/\1$new/}" file
 1;2;3;4;5;6;7;8;9;0;
 foo;bar;XX;qux;foo;bar;baz;qux;
 a;b;c;d;e;f;g;
```
Explained:
lead="^([^;]*;){$((c-1))}"
- ^ means at the start of a record
- (...) is grouping for the following {...} which specified repetition
- [^;]* mean zero or more non-semicolons
- $((c-1)) does the math and returns one less than the desired column; if you want to look at column 3, it returns two.
- SO, ^([^;]*;){$((c-1))} at the start of the record, one-less-than-column occurrences of non-semicolons followed by a semicolon
thus, sed -E "/$lead$word;/{s/($lead)$word/\1$new/}" file mean read file and on records where $word occurs in the requested column, save everything before it, and put that stuff back, but replace $word with $new.

Even if you MUST use sed, I recommend a function.
```
fix(){ 
  local word="$1" col="$2" new="$3" file="$4"
  local lead="^([^;]*;){$((col-1))}"
  sed -E "/$lead$word;/{s/($lead)$word/\1$new/}" "$file"
}
```
In use –
```
$: fix bar 2 HI file
1;2;3;4;5;6;7;8;9;0;
foo;HI;baz;qux;foo;bar;baz;qux;
a;b;c;d;e;f;g;

$: fix 1 1 XX file
XX;2;3;4;5;6;7;8;9;0;
foo;bar;baz;qux;foo;bar;baz;qux;
a;b;c;d;e;f;g;

$: fix bar 2 '(^_^)' file
1;2;3;4;5;6;7;8;9;0;
foo;(^_^);baz;qux;foo;bar;baz;qux;
a;b;c;d;e;f;g;
```
No changes if no matches –
```
$: fix bar 5 HI file
1;2;3;4;5;6;7;8;9;0;
foo;bar;baz;qux;foo;bar;baz;qux;
a;b;c;d;e;f;g;
```
NOTE –

This logic requires trailing delimiters if you ever want to match the last field –
```
$: fix 0 10 HI file
1;2;3;4;5;6;7;8;9;HI;
foo;bar;baz;qux;foo;bar;baz;qux;
a;b;c;d;e;f;g;
```
delimiters removed:
```
$: fix 0 10 HI file
1;2;3;4;5;6;7;8;9;0
foo;bar;baz;qux;foo;bar;baz;qux
a;b;c;d;e;f;g
```
Otherwise you have to complicate the logic a bit.

But honestly, for field parsing, you’d be so much better served to use awk, or even perl or python, or for that matter a bash loop, though that’s going to be relatively slow.
Login or Signup to reply.