Awk / sed script help.

[D. Jackson_1]

How do all...
I am needing some assistance with a shell script.

I have a master file that is pipe "|" delimited.
My goal is to search through the file looking specifically for a few fields and if they meet a condition I have to change one of the fields and save the file.

Here is what I am looking for:

If COL 165 is = to COMPANY PAID and COL 63 does not = AIRFARE - DIRECT BILLED then within that same line replace COL 165 COMPANY PAID with JPMC CORPORATE CARD and save file.
Same conditions for every line with specifics of above.
I would rather not use perl unless it is a last resort.

Many thanks (points will be awarded)

DCJ

[James R. Ferguson]

Hi:

Posting an actual file (as an attachment) is far more helpful than a word description of columns and column contents.

If their are less than 199 fields in the file, 'awk' may suffice.

Regards!

...JRF...

[OldSchool · Accepted solution]

If you meant "fields" where you said "columns", then this should be a start. Be aware that if you have a lot of fields ( > 200?) awk may present issues.



BEGIN {
FS="|";
OFS="|";
}

{
if ($165 == "COMPANY PAID" && $63 != "AIRFARE - DIRECT BILLED")
{
$165 = "COMPANY PAID with JMPC CORPORATE CARD";
}

print $0;
}


END{
}

[D. Jackson_1]

James here is some sample output. I had to XXXXX a bunch of stuff out but the important stuff is there. That is why I didnt post it originally.

OldSchool, thanks I will give this a try.

many thanks fellas.

DCJ

[OldSchool]

hmm.. i tried mine on an AIX box and it reports that you've 256 fields...which may not work w/ HP-UX's version of awk

[D. Jackson_1]

OldSchool,
I forgot to even mention I was doing this on (cough cough) Solaris... :)

Your snippet led me down the what looks to be correct path.

Here is what my command looks like to do this:

cat TEST.txt | nawk 'BEGIN { FS="|"; OFS="|"; } { if ($165 == "COMPANY PAID" && $63 != "AIRFARE - DIRECT BILLED") { $165 = "JMPC CORPORATE CARD"; } print $0; } END{ }' > NEWTEST.txt

Just for my sanity do you all mind trying this to confirm?

Thanks abunch fellas I just needed a little guidance.

Thanks again
DCJ

[James R. Ferguson]

Hi:

Based on your data, I see more than 199 fields too. The good old 'awk' is going to choke on that, but Perl never will.

Try this:

# cat ./reformat
#!/usr/bin/perl
use strict;
my @a;
while (<>) {
@a = split /\|/;
if ( $a[164] eq 'COMPANY PAID' && $a[62] ne 'AIRFARE - DIRECT BILLED' ) {
print join '|', @a[ 0 .. 163 ],
'COMPANY PAID with JMPC CORPORATE CARD', @a[ 165 .. $#a + 1 ];
}
else {
print;
}
}
1;

...run as:

# ./reformat file

Note that Perl counts zero-relative, so I adjusted the field counts accordingly.

Regards!

...JRF...

[OldSchool]

cat TEST.txt | nawk 'BEGIN { FS="|"; OFS="|"; } { if ($165 == "COMPANY PAID" && $63 != "AIRFARE - DIRECT BILLED") { $165 = "JMPC CORPORATE CARD"; } print $0; } END{ }' > NEWTEST.txt


well, it worked on AIX, changed 2 records in the test data provided.

but....

1) skip the "cat', its not needed...

nawk 'BEGIN { FS="|"; OFS="|"; } { if ($165 == "COMPANY PAID" && $63 != "AIRFARE - DIRECT BILLED") { $165 = "JMPC CORPORATE CARD"; } print $0; } END{ }' TEST.txt > NEWTEST.txt

will let nawk read / process the file itself....and

2) not sure why you'd want one long command line... to me, something like this:

cat cardchange.awk
BEGIN {
FS="|";
OFS="|";
}

{
if ($165 == "COMPANY PAID" && $63 != "AIRFARE - DIRECT BILLED")
{
$165 = "JMPC CORPORATE CARD";
}
print $0;
}

END{
}


used with:

nawk -f cardchange.awk TEST.txt > NEWTEST.txt

is just easier to maintain