Validate email address with Formula tool and output reports as Excel workbooks

I'm trying to accomplish three things. I'm not very familiar with regular expression, and I continue to run into errors. I have a data table looks something like this:

CODE	ID	First Name	Last Name	Email
E202201	20220101	Jason	Sullivan	jsullivan@gmail.com
E202201	20220102	Joseph	Raven	jrs@HOTMAIL.com
E202201	20220103	Callan	McDosky
E202201	20220104	George	Lee	z-li@school.edu

(all data presented are fictional)

1) I'm trying to validate if the email addresses are valid. I want to add a column that includes flags indicating whether the address is empty "Not Applicable", valid "Valid" or invalid ("Not Valid").

2) After that I want to output this data to an Excel workbook that should be created at output. I want the workbook's name to be "report-for-CODE.xlsx". In this example, it should be "report-for-E202201.xlsx".

3) I will connect to different data tables stored in Excel workbooks, and I want all the output files to be stored in the same directory.

How do I use a Formula tool to achieve the first two goals?

Accepted answers

atcodedog05

Hi @45179902

Here is how you can do it.

1. Validate email: You can use the formula with regex_match like below to check email. If an email address is format abc@xyz.com then its valid or else its not.

IF IsEmpty(Trim([Email]," ")) THEN "Not Applicable" 
ELSEIF REGEX_Match([Email], "[\w.]+@\u+\.\u+") THEN "Valid" 
ELSE "Not Valid" ENDIF

2. Output each code into a separate file: Create a formula like below creating the filename

"report-for-"+[CODE]+".xlsx|Sheet1"

Configure your output tool like below to output each code to a separate file.

Hope this helps : )

Code and email.yxmd

All comments

atcodedog05

Hi @45179902

Here is how you can do it.

1. Validate email: You can use the formula with regex_match like below to check email. If an email address is format abc@xyz.com then its valid or else its not.

IF IsEmpty(Trim([Email]," ")) THEN "Not Applicable" 
ELSEIF REGEX_Match([Email], "[\w.]+@\u+\.\u+") THEN "Valid" 
ELSE "Not Valid" ENDIF

2. Output each code into a separate file: Create a formula like below creating the filename

"report-for-"+[CODE]+".xlsx|Sheet1"

Configure your output tool like below to output each code to a separate file.

Hope this helps : )

Code and email.yxmd

lizhaozhi

Hi @atcodedog05

Thank you for your quick response with thumb up, solutions, and screenshot! Since I'm not familiar with regular expression, I would appreciate it very much if you can break down your regular expression in the solution to explain what each part means!

Also, I noticed that in your Alteryx Designer screenshot, "z-li@school.edu" a valid email address, is rated invalid in your solution. I believe this is because of the regular expression that you're using, but unfortunately, I lack the knowledge to adjust it.

How to edit the solution (regex) so that all valid addresses are detected correctly? For example, email addresses with subdomains (abc@careers.xyz.com) or country codes (abc@commerce.gov.cn), etc.

Regards,

George

stevemarkovick

I’ve been trying to figure out a clean way to validate emails and output dynamic Excel reports in Alteryx, and your explanations (especially about regex and handling missing CODE values) really helped clear things up.
Also, huge thanks for sharing the screenshots and sample workflows — made it way easier to follow along.
Really appreciate how you addressed edge cases too, like blank fields and proper filename generation.

christianCaldwell

there are plenty of valid email addresses that this code invalidates. Please update to allow subdomains and symbols such as + which gmail allows

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703