loop over one or more matches in an awk column

Question

The problem is that in one column of a csv file there is one or more email addresses. The output needs there to be one line for each email address. How do I do a for loop on the email addres to repeat the line for each email address? I was assuming I would want to use a regular expression to find all the email addresses in the second column and then loop over that array, but how to get all the emails into an arry?

Here is the simple awk script:

BEGIN {
    FPAT = "([^,]*)|(\"[^\"]+\")"
    OFS=","
}

{ 
    name=substr($1,2,length($1)-2) 
    email=substr($2,2,length($2)-2) 

    print name, email
}

The input:

"agrippa","sonny.cipriano@gmail.com"
"elvirka","alex.martinho18@gmail.com"
"Inofs","sv1tm@yandex.by;Jono_Newton@hotmail.co.uk"
"bekbz","luca.rinaldi@mtf.ch,Toe1966@hotmail.co.uk"
"njkzif","aa@suisseplan.ch|brianlofts@hotmail.co.uk"
"njycz","flyfishhobby@aol.com:philip@hugosinbox.xyz"
"DanielEdict","petebll@josephay905s.changeip.com"
"JosEmbesy","jw8796384@gmail.com , natalavybopvtc@gmail.com"
"Walterdon","ramostt6547@gmail.com   ;   rueydjc583657@gmail.com"
"Kennethlob","kostazdanovolrv@gmail.com"
"Ninosh","silvertoothtiger@gmail.com"
"Patrickbam","bulldoggym2017@gmail.com"

The desired output:

agrippa,sonny.cipriano@gmail.com
elvirka,alex.martinho18@gmail.com
Inofs,sv1tm@yandex.by
Inofs,Jono_Newton@hotmail.co.uk
bekbz,luca.rinaldi@mtf.ch
bekbz,Toe1966@hotmail.co.uk
njkzif,aa@suisseplan.ch
njkzif,brianlofts@hotmail.co.uk
njycz,flyfishhobby@aol.com
njycz,philip@hugosinbox.xyz
DanielEdict,petebll@josephay905s.changeip.com
JosEmbesy,jw8796384@gmail.com
JosEmbesy,natalavybopvtc@gmail.com
Walterdon,ramostt6547@gmail.com
Walterdon,rueydjc583657@gmail.com
Kennethlob,kostazdanovolrv@gmail.com
Ninosh,silvertoothtiger@gmail.com
Patrickbam,bulldoggym2017@gmail.com

---

A little more about the REAL data, it is not just two columns. Here is the header from the real input data:

"Created","first name","last name","address1","address2","city","state","zip","country","phone Office","phone Cell","phone Home","company Name","webSite","email","NoEmail","License Type","Issued Date","License Expires"

The output is also not just two columns, email is not currently the latest in the output, but if it needs to be, it can be.

One more thing about the input data, it is a CSV file with quotes around all the data, except if there is no data, there are no quotes. The FPAT seems to handle that fine, except for the fact each column does have quotes around it, which I am using the substring to strip the quotes off.

Here is an example of the real input

"9/1/2019","Can","Back","77 High Drive","","Chicago","IL","45099","USA","555-555-8521",,,"company name","http://www.yourcomapny.co.uk/","email1@hotmail.com,email2@yahoo.com","","foobar","9/1/2019","9/1/2020"

Answer 1

With GNU awk for FPAT (and then, since we already require gawk, also using gensub() and \s shorthand for [[:space:]]):

$ cat tst.awk
BEGIN {
    FPAT = "([^,]*)|(\"[^\"]+\")"
    OFS=","
}
{
    name = gensub(/^"|"$/,"","g",$1)
    n = split(gensub(/^"|"$/,"","g",$2),emails,/\s*[;,|:]\s*/)
    for (i=1; i<=n; i++) {
        print name, emails[i]
    }
}
$
$ awk -f tst.awk file
agrippa,sonny.cipriano@gmail.com
elvirka,alex.martinho18@gmail.com
Inofs,sv1tm@yandex.by
Inofs,Jono_Newton@hotmail.co.uk
bekbz,luca.rinaldi@mtf.ch
bekbz,Toe1966@hotmail.co.uk
njkzif,aa@suisseplan.ch
njkzif,brianlofts@hotmail.co.uk
njycz,flyfishhobby@aol.com
njycz,philip@hugosinbox.xyz
DanielEdict,petebll@josephay905s.changeip.com
JosEmbesy,jw8796384@gmail.com
JosEmbesy,natalavybopvtc@gmail.com
Walterdon,ramostt6547@gmail.com
Walterdon,rueydjc583657@gmail.com
Kennethlob,kostazdanovolrv@gmail.com
Ninosh,silvertoothtiger@gmail.com
Patrickbam,bulldoggym2017@gmail.com

FWIW I usually use the *sub(/^"|"$/,"",...) approach to strip possible leading/training double quotes off CSV fields as it has the benefit over the substr() approach that it won't mangle the field in those cases where there were no double quotes.

You might want to also add some error detection in case of mangled email addresses or cases you forgot to handle (e.g. a separator other then in [;,|:]):

$ cat tst.awk
BEGIN {
    FPAT = "([^,]*)|(\"[^\"]+\")"
    OFS=","
}
{
    name = gensub(/^"|"$/,"","g",$1)
    n = split(gensub(/^"|"$/,"","g",$2),emails,/\s*[;,|:]\s*/)
    for (i=1; i<=n; i++) {
        email = emails[i]
        if ( gsub(/@/,"&",email) != 1 ) {
            printf "ERROR: too few or too many email addresses in \"%s\"\n", email | "cat>&2"
            exit 1
        }
        print name, email
    }
}

and if you REALLY wanted to validate email addresses, FWIW for the past 5 years or so with no problems I'm aware of I've been using this modified version of the regexp from http://www.regular-expressions.info/email.html (I specifically use [a-zA-Z] instead of [:alpha:] as I only want to accept letters that are considered such in my locale - you decide what makes sense for your application):

    (email ~ /^[0-9a-zA-Z._%+-]+@[0-9a-zA-Z.-]+\.[a-zA-Z]{2,}$/)

Answer 2

Not sure I understand your parenthesized comment on 15+, and 7 columns, but for the example given, try

awk -F, '


        {gsub (/[" ]/,_)                        # remove double quotes and space all over
         D1 = $1                                # save field 1 and
         sub ($1 FS, _)                         # remove it from line
         n  = split ($0, T, /[,;:\|]/)          # split the residual line into array T
         for (i=1; i<=n; i++) print D1, T[i]    # print former $1, and each T element
        }
' OFS=, file
agrippa,sonny.cipriano@gmail.com
elvirka,alex.martinho18@gmail.com
Inofs,sv1tm@yandex.by
Inofs,Jono_Newton@hotmail.co.uk
.
.
.
Patrickbam,bulldoggym2017@gmail.com