topic Re: Awk / sed script help. in Operating System - HP-UX

Awk / sed script help.

D. Jackson_1 — Wed, 15 Jul 2009 17:20:14 GMT

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

Re: Awk / sed script help.

James R. Ferguson — Wed, 15 Jul 2009 17:49:46 GMT

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...

Re: Awk / sed script help.

OldSchool — Wed, 15 Jul 2009 18:02:18 GMT

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{
}

Re: Awk / sed script help.

D. Jackson_1 — Wed, 15 Jul 2009 18:09:10 GMT

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

Re: Awk / sed script help.

OldSchool — Wed, 15 Jul 2009 18:19:10 GMT

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

Re: Awk / sed script help.

D. Jackson_1 — Wed, 15 Jul 2009 18:39:58 GMT

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

Re: Awk / sed script help.

James R. Ferguson — Wed, 15 Jul 2009 18:49:13 GMT

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...

Re: Awk / sed script help.

OldSchool — Wed, 15 Jul 2009 19:06:51 GMT

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