Need to replace a specific column value of a file based on values in other columns of the file

Question

Below is my content of file and below is my requirement Based value in column no 1 and column no 5, I would want to replace value in column 7 with "1".

For example:

If column 1="change" and column 5="defer", then replace value in column 7 with "1".
If column 1="change" and column 5="defererence" then replace value in column 7 with "1".
Otherwise do not do anything with row, keep it as it is.

Input file:

Change|sinmg|1234|ewfew|def|fdfd|JAMES|rewr|ROBERT|3|fe
Change|sinmg|2345|ewfew|defer|VENKTRAAAMMAMAMAMMAMA|3|rewr|BEAEY|3|
noChange|sinmg|2323|ewfew|def|VENKTRAAAMMAMAMAMMAMA|3|rewr|BEAEY|3|fe
Change|sinmg|3456|ewfew|defer|VENKTRAAAMMAMAMAMMAMA|3|rewr|BEAEY|3|
Change|sinmg|2345|ewfew|defererence|VENKTRAAAMMAMAMAMMAMA|3|rewr|BEAEY|3|

Above is just a sample to make it easier to explain.However I want to pass values for column 1 and column 5 from a file to match against value in file. If it matches, then only replace column 7 with value "1" otherwise dont do anything with row, keep it as it is.

I tried couple of options and not able to achieve required results.

perl -F'\|' -i -lape  'if ($F[0] eq "change" && $F[4] eq "defer") { s/$F[6]/1/g;}' file_name

Above command is replacing all values of 3 in file irrespective of fields. But i want to only replace 6th column value based on 1st column and 4th column by passing different values to 1st and 4th column in a loop.

Adding more information:

As mentioned by me above, above example is just simplest form of my problem to make everybody understand the requirement. I have a file with name "listfile" which has got list of values for column no 1 and column no 5 for matching. If values in column no 1 and column no 5 from my sourcefile matches with the values passed from file "listfile", then solution should replace value in column no 7 with "1". Otherwise do not do anything with row from source file, keep it as it is.

I tried to do below, but unable to achieve required.

    #!/usr/bin/ksh
    for line in $(cat dir/listfile)
    do
    VAR1=$(echo $line | nawk -F"|" '{print $1}')
    VAR2=$(echo $line | nawk -F"|" '{print $2}')
    nawk -F"|" 'BEGIN {OFS="|"} {if($1!="'"$VAR1"'" && $5!="'"$VAR2"'") {$8="1"; print $0;}      else {print $0;}}' dir/sourcefile >> dir/sourcefile_REVISED
    done

No of records between original source file and revised source file after replacing Column no 7 should be same. Only thing is for all values of Column no 1 and 5 from file listfile, i need column no 7 value to be replaced by "1".

Thanks,

@iamauser the 1 is quoted to mean the literal value 1, the seventh column contains numeric data and the attempted script `s/$F[6]/1/g` use a literal 1 so it's pretty clear. — Chris Seymour, Sep 24 '13 at 20:05
@sudo_O OK, thanks. My initial answer was with first column, but changed after looking at the question more carefully :-) — iamauser, Sep 24 '13 at 20:12

iamauser · Answer 1 · 2013-09-24T20:00:03.360

1

You can use awk to do this.

awk -F'|' 'BEGIN{OFS="|"}{if($1=="Change"&&$5=="defer"){$7=1}{print}}' file

I realize that you also need 5th column to match with "differerence"... Following should work:

awk -F'|' 'BEGIN{OFS="|"}{if($1=="Change"&&$5=="defer"||$5=="defererence"){$7=1}{print}}' file

edited Sep 24 '13 at 20:00

answered Sep 24 '13 at 19:34

iamauser

11,119
5
34
52

mpapec · Answer 2 · 2013-09-24T20:58:29.303

1

perl -F'\|' -i -lape '
  BEGIN{ $" = "|" }
  $F[6]=1, $_="@F" if $F[0] eq "Change" && $F[4] =~ /^defer(erence)?$/;
' listfile

edited Sep 24 '13 at 20:58

answered Sep 24 '13 at 20:47

mpapec

50,217
8
67
127

@mpapec Can you please describe a bit how this works? I could only understand `if $F[0] eq "Change" && $F[4] =~ /^defer(erence)?$/` portion. I would like to learn such concise style of scripting. – jkshah Sep 24 '13 at 22:09
@jkshah `$F[6]=1` replaces value in seventh cokumn, and `$_="@F"` makes new line of values separeted by `|` – mpapec Sep 24 '13 at 22:17

score 0 · Answer 3 · answered Sep 24 '13 at 19:40

The substitute operator s/// doesn't understand the concept of columns of data, it just operates on the given string. In this case, you are replacing whatever is in column 7 everywhere it occurs in the input line because of the g modifier.

The awk solution in the answer by @imauser is a good solution.

Chris Seymour · Answer 4 · 2013-09-24T19:47:31.073

With awk you would do:

$ awk '$1=="Change"&&$5~/^defer(erence)?$/{$7=1}1' FS='|' OFS='|' file
Change|sinmg|1234|ewfew|def|fdfd|JAMES|rewr|ROBERT|3|fe
Change|sinmg|2345|ewfew|defer|VENKTRAAAMMAMAMAMMAMA|1|rewr|BEAEY|3|
noChange|sinmg|2323|ewfew|def|VENKTRAAAMMAMAMAMMAMA|3|rewr|BEAEY|3|fe
Change|sinmg|3456|ewfew|defer|VENKTRAAAMMAMAMAMMAMA|1|rewr|BEAEY|3|
Change|sinmg|2345|ewfew|defererence|VENKTRAAAMMAMAMAMMAMA|1|rewr|BEAEY|

score 0 · Answer 5 · answered Sep 24 '13 at 20:20

You can still use a perl+regexp solution by "skipping" the first six fields before doing the replacement:

perl -F'\|' -i -lapE  'if ($F[0] eq "change" && $F[4] =~ m{^defer(erence)?$}) { s{^(?:[^|]*\|){6}\K([^|]*)}{1} }' file_name

An advantage over the awk solutions: you can still use the -i switch here.

jkshah · Answer 6 · 2013-09-24T21:47:38.340

You can achieve this using split and join in perl script as follows:

#!/usr/bin/perl
use warnings;
use strict;

my $infile = "dir/sourcefile";
my $listfile = "dir/listfile";
my $outfile = "dir/sourcefile_REVISED";

my @list;
open LFOPEN, $listfile or die $!;
while (<LFOPEN>) {
    chomp;
    my @col = split /\|/, $_;
    push @list, \@col;
}
close LFOPEN;

open IFOPEN, $infile or die $!;
open OFOPEN, '>', $outfile or die $!;
while (<IFOPEN>) {
    chomp;
    my @col = split /\|/, $_;
    foreach my $lref (@list) {
        $col[6] = '1' if ($col[0] eq $lref->[0] and $col[4] eq $lref->[1]);
    }
    print OFOPEN join ('|', @col) . "\n";
}
close IFOPEN;
close OFOPEN;

Input (dir/sourcefile):

Change|sinmg|1234|ewfew|def|fdfd|JAMES|rewr|ROBERT|3|fe
Change|sinmg|2345|ewfew|defer|VENKTRAAAMMAMAMAMMAMA|3|rewr|BEAEY|3|
noChange|sinmg|2323|ewfew|def|VENKTRAAAMMAMAMAMMAMA|3|rewr|BEAEY|3|fe
Change|sinmg|3456|ewfew|defer|VENKTRAAAMMAMAMAMMAMA|3|rewr|BEAEY|3|
Change|sinmg|2345|ewfew|defererence|VENKTRAAAMMAMAMAMMAMA|3|rewr|BEAEY|3|

List (dir/listfile):

Change|defer
Change|defererence

Output (dir/sourcefile_REVISED):

Change|sinmg|1234|ewfew|def|fdfd|JAMES|rewr|ROBERT|3|fe
Change|sinmg|2345|ewfew|defer|VENKTRAAAMMAMAMAMMAMA|1|rewr|BEAEY|3
noChange|sinmg|2323|ewfew|def|VENKTRAAAMMAMAMAMMAMA|3|rewr|BEAEY|3|fe
Change|sinmg|3456|ewfew|defer|VENKTRAAAMMAMAMAMMAMA|1|rewr|BEAEY|3
Change|sinmg|2345|ewfew|defererence|VENKTRAAAMMAMAMAMMAMA|1|rewr|BEAEY|3

Don't use global filehandles, use 3 form one instead like : `open my $fh, ">", $file or die $!;` — Gilles Quénot, Sep 24 '13 at 22:00
@sputnick Thanks for your feedback but didn't get you. `use 3 form one ??` - can you please elaborate a little or provide reference link? — jkshah, Sep 24 '13 at 22:05
Your `open` don't pass `perlcritic -5 file`, this is Perl Best-Practices : `file.pl: Bareword file handle opened at line 3, column 1. See pages 202,204 of PBP. (Severity: 5, Policy: InputOutput::ProhibitBarewordFileHandles) ` — Gilles Quénot, Sep 24 '13 at 22:23
@sputnick Now I get it, will take care next time. I am curious even if we properly open and close bareword filehandles, is there a risk? — jkshah, Sep 25 '13 at 04:13
Run `perlcritic -4 script.pl` yourself, it will tell you. I think 4 is a good level, less is too annyoning/discutable. — Gilles Quénot, Sep 25 '13 at 09:14

Need to replace a specific column value of a file based on values in other columns of the file

6 Answers6