awk/sed question

[scooter6]

I have some text files that look like this:

"Smith","Tom","1234 Main Street"
"Jones","Robert L","9876 Elm Street"
"Williams","John Quincy","123 USA Street"

etc,etc

They are already in comma-delimmited, which is what I want.
But I need to import them into a database whose fields are slightly different.

The database has fields like this:

Last Name
First Name
Middle Initial (Initial only, not full middle name)

so, with the above examples, I need my delimmited file to look like this:

"Smith","Tom",,"1234 Main Street" (has extra ',' for blank)
"Jones","Robert","L","9876 Elm Street" (delimits mid init)
"Williams","John","Q","123 USA Street" (takes first letter of "Quincy" and delimits it as just "Q&quot

I also have Perl available, which will probably work better but I'm unsure how to accomplish this??

ideas? suggestions? help? hints? tips?

thanks in advance

Scott Ullmann
TelespectrumFX

 

[olded]

Scooter:

If I interpreted this correctly, this awk script is what you want. Gotta run. something you don't understand, let me know and I'll explain:

Regards,


Ed


#!/bin/ksh

nawk ' BEGIN { FS = "," }
{
# parse field 2 which may contain a middle name or initial
gsub("\"", "", $2) # get rid of "\""
nf=split($2, aa, " &quot # get first name into array

if(nf == 1) # no middle name
{
fn=$2 # first name
mn=""
}
else
{ # proess middle name getting first initial
fn=aa[1]
me=aa[nf]
if(length(ln) > 1)
me=substr(ln,1,1)
}

printf("%s,\"%s\",\"%s\",%s\n", $1, fn, me, $3)
} ' data.file

 

[olded]

Scooter:

Sorry about that! Disregard the first post. Had a small bug:

#!/bin/ksh

nawk ' BEGIN { FS = "," }
{
# parse field 2 which may contain a middle name or initial
gsub("\"", "", $2) # get rid of "\""
nf=split($2, aa, " &quot

if(nf == 1) # no middle name
{
fn=$2 # first name
mn=""
}
else
{ # proess middle name getting first initial
fn=aa[1]
me=aa[nf]
if(length(me) > 1)
me=substr(me,1,1)
}

printf("%s,\"%s\",\"%s\",%s\n", $1, fn, me, $3)
} ' data.file