0

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"
  • I suggest you to replace input/output with your actual format of your real input and expected output instead of current provided wrong input/expected out + single line real sample to make your question clear – αғsнιη Feb 2 at 15:21
  • Well, if you look at the header info there is too much personal info to post real data – Sam Carleton Feb 2 at 15:23
  • yes, I said "your actual format of your real input" not real input itself – αғsнιη Feb 2 at 15:47
  • ... and what should the real output look like? Would the field headed "email" be the one to take care of, and would it contain all the possible / multiple forms shown in your sample input? – RudiC Feb 2 at 16:58
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,}$/)
  • 1
    Thank you so much, that worked perfectly! – Sam Carleton Feb 2 at 18:01
0

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
  • I TOTALLY get what you are doing here, but the problem comes in the fact I wasn't clear enough in my parenthesized comment. I am going to go back and try to improve the original question to make it more clear. – Sam Carleton Feb 2 at 13:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.