We help IT Professionals succeed at work.

Perl or Unix Shell script to replace 4th column value to the left of a specific text in a csv file

sunhux
sunhux used Ask the Experts™
on
Refer to the attached sample file;  my actual file has thousands of lines of the same
format.

I need a script urgently that would allow me to
a) search a specific (case-sensitive) text in the last column of the csv file (script is to
     allow me to input the text as the 1st parameter)
b) then for the 4th column in the csv file, replace the value with another value
    (this new value is to be taken as a parameter).

So, for example, if your script is called  replvalue.pl or replvalue.sh,
then I just need to execute it as :

# replvalue.pl "CPUTotalLoad Repaired" 3.0
so the above wud change the last line of the csv to :
2898,29/4/2010  9:12:27 AM,664.8,3.0,,Medium,UNIX-HP,Globl Ops,29/4/2010  8:17:54 PM,665.4,29/4/2010  8:17:44 PM,incident,,UKOPPMB2.AG.UK UxsA2_CPUTotalLoad Repaired Critical
egcsv.txt
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2014
Top Expert 2015
Commented:
#!/usr/bin/perl
my($s,$r)=@ARGV;
@ARGV=("egcsv.txt");
$^I=".bak";
while( <> ){
   s/^(([^,]*,){3})[^,]*(?=,.*$s[^,]*$)/$1$r/;
   print;
}
This should do the trick...
#!/usr/local/bin/perl
use strict;
use warnings;
use Tie::File;
# replace with actual file name
my $file = 'eq.csv';
# handle params
my $str = shift;
my $new_val = shift;
die "Usage: $0 <search string> <new value>\n"
    unless (defined $str and defined $new_val);
# tie and search
my @lines;
tie @lines, 'Tie::File', $file or die "could not tie $file: $!";
foreach my $line (@lines) {
    if ($line =~ m{,[^,]*$str[^,]*\z}) {
        my @vals = split /,/, $line;
        $vals[3] = $new_val;
        $line = join ',', @vals;
        # alternately you could use this instead - not sure which is better
        #$line =~ s{^([^,]*,[^,]*,[^,]*,)[^,]*}{$1$new_val};
    }
}
untie @lines;

Open in new window

Most Valuable Expert 2013
Top Expert 2013
Commented:
How about a simple awk "one-liner"?
#!/bin/ksh
awk -F, -v S="$1" -v R=$2 'BEGIN {OFS=","} {if ($NF~S) {$4=R} {print}}' filefile.tmp
echo mv file.tmp file
Replace file with the name of your inputfile.
Please note that I put echo in front of the final overwriting of the original file.
Remove it when you're satisfied with the result (the contents of file.tmp)
wmp