splitting fields in .csv data 1

[admoore]

I have comma delimited data with fields like this:

SERIAL#,STREET ADDRESS,CITY,ST,ZIP,RO,CUSTOMER NAME,EMAIL ADDRESS,ADVISOR,SERVICE ADVISOR,RO#,MILEAGE,C LBR SALE,C LBR G P,C PTS SALE,C PTS G P,C SUB SALE,C SUB G P,C CNT,C GOG SALE,C GOG G P,C MISC AMT,C T HRS,CPOST DT,W LBR SALE,W LBR G P,W PTS SALE,W PTS G P,W SUB SALE,W SUB G P,W CNT,W GOG SALE,W GOG G P,W MISC AMT,W T HRS,I LBR SALE,I LBR G P,I PTS SALE,I PTS G P,I SUB SALE,I SUB G P,I CNT,I GOG SALE,I GOG G P,I MISC AMT,I T HRS,TECHNICIAN,SVC OP CODES,CWI,OPERATION DESC,YEAR,MAKE NAME,CARLINE/MODEL,RES  PHONE#,BUS  PHONE#

And need to split the data in the CUSTOMER NAME field (field 7) in two...

Most of the time the data in this field looks like this:

JOHN Q. PUBLIC
MS. JANE DOE
DR. FRANK FURTER
JOHN & MARY SMITH

I need output like this:

JOHN, Q. PUBLIC
JANE, DOE
FRANK, FURTER
JOHN, & MARY SMITH

That is I need to seperate the first word from the rest of the field and eliminate any prefix, such as Mr. Mr Ms MS. Dr DR., etc...

TIA for ANY help here...

Regards,

-Allen Moore

 

[vgersh99]

Something to start with:

nawk -f customers.awk customers.txt

#--------------------- customers.awk
BEGIN {
FS=","
customerFLD="7";

prefix="Ms. Mr. Dr. DR Mr Ms"
numpref=split(prefix, prefixARR, " "
}

{
for (i=1; i <=numpref; i++) {
if ( (&quot;^&quot; $customerFLD) ~ prefixARR) {
sub(&quot;^&quot; prefixARR, &quot;&quot;, $customerFLD);
sub(&quot;^ *&quot;, &quot;&quot;, $customerFLD);
break;
}
}
sub(&quot; &quot;, &quot;,&&quot;, $customerFLD);
print $customerFLD
}


#----------------- customers.txt
1,2,3,4,5,6,Jane Fonda,8,9,10
1,2,3,4,5,6,Ms. Jane Fonda,8,9,10
1,2,3,4,5,6,Mr. Henry Ford,8,9,10
1,2,3,4,5,6,Dr. Joe Dow,8,9,10
1,2,3,4,5,6,DR Joe Shmoe,8,9,10
1,2,3,4,5,6,Mr Foo Barr,8,9,10
vlad
+----------------------------+
| #include<disclaimer.h> |
+----------------------------+

 

[admoore]

Works great; however, I need to keep all of the existing fields in the output as well... Would I simply modift the PRINT statement to accomplish this??

TIA,

-ADM

 

[vgersh99]

change
FS=&quot;,&quot;
to
FS=OFS=&quot;,&quot;

and change
print $customerFLD
to
print

#--
NOTE: be aware your FS [FieldSeparator] is &quot;,&quot; and you're also modifying the 7th field inserting the &quot;,&quot; after the &quot;first name&quot;. Just be aware that you're CREATING a new field in a record.
vlad
+----------------------------+
| #include<disclaimer.h> |
+----------------------------+