asked on

Use awk or sed to manipulate Columns in a File

I have a .csv file that I need to use awk on to massage some columns but I can only use awk in a limited fashion. The file has five columns and in column three whenever “Operator Control” is there it needs to be replaced by what is in columns four and five. It is a shift, if you will, of that column(s) entries. Please see attachment for an example. The first two lines should look like lines three and four.
Test.csv

Bill Prew

What do you mean by "I can only use awk in a limited fashion", are you just saying that you are not an expert with AWK, or are you using a certain version of AWK that has restrictions or limitations?

~bp

Bill Prew

Here is a small AWK script that should do what you asked, save as a file and run like this:

awk -f EE29012897.awk Test.csv > out.csv

BEGIN {
    FS = ","
    OFS = ","
}

{
    if ($3 == "Operator Control         ") {
        print $1,$2,$4,$5;
    } else {
        print $0;
    }
}

Open in new window

~bp

MURUGESAN N

1. If the 3rd parameter is exactly equal to "Operator Control ", you can use the command provided by Bill Prew.
else if 3rd parameter contains "Operator Control" you can use:
Replace:
if ($3 == "Operator Control ") {
With:
if ( 0 != index( $3, "Operator Control")){

2. Same thing using /bin/sed

/bin/sed "s/,Operator Control.*,/,/;"  Test.csv

Open in new window

dwortman

ASKER

Bill,

What you have shown me works fine. However, if I do this:

BEGIN {
    FS = ","
    OFS = ","
}

{
    if ($3 == "Operator Control         " || $3 == "Manual Entry              " ) {
        print $1,$2,$4,$5;
    } else {
        print $0;
    }
}

Open in new window

The Manual Entry part does not shift but the Operator Control does. The .csv output file is created by SQL*Plus and column 3 has a column size of 36 characters if this means anything.

Bill Prew

Try this, it will match those two literals followed by 0 or more spaces. Keep in mind this is case sensitive as it stands.

BEGIN {
    FS = ","
    OFS = ","
}

{
    if ($3 ~ /^Operator Control *$/ || $3 ~ /^Manual Entry *$/) {
        print $1,$2,$4,$5;
    } else {
        print $0;
    }
}

Open in new window

~bp

dwortman

ASKER

Bill,

Thanks, but the Manual Entry is still there. I verified the case of Manual Entry as well.

dwortman

ASKER

Also, the Operator Control was shifted, just not Manual Entry.

dwortman

ASKER

Bill,

As I was trying to understand your script I got lucky and figured out the problem. I removed the ^ from the script and Operator Control and Manual Entry were removed. Does this make sense to you?

Bill Prew

Can you attach and post the file you are testing with, the following works fine here...

03/30/2017 11:55:42951   ,TRAIN,Operator Control         ,Column 4A,Column 5A
03/30/2017 11:55:42946   ,TRAIN,Operator Control         ,Column 4B,Column 5B
03/30/2017 11:55:42951   ,TRAIN,Manual Entry              ,Column 4A,Column 5A
03/30/2017 11:55:42946   ,TRAIN,Manual Entry              ,Column 4B,Column 5B
03/30/2017 11:55:42951   ,TRAIN,Column 4A,Column 5A,
03/30/2017 11:55:42946   ,TRAIN,Column 4B,Column 5B,

Open in new window

~bp

dwortman

ASKER

Bill,

Sorry, I cannot post the file but I can tell you that Operator Control has a single space in front of it and the Manual Entry has a single quote in front of it:
"Manual Entry

I know it is hard to debug without the actual file...

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dwortman

ASKER

Thanks very much for the help. I will be submitting another awk question next week.