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"
"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 '20 at 16:58