Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.

E-mail as a data source?

Highlighted
12 - Quasar
12 - Quasar

Is it possible to use e-mails as a data source?

 

I'd like to see if it's possible to use Alteryx to do something like this:

-Access a shared Exchange mailbox

-Read in all the email messages (HTML content)

-Parse that content into a data table

 

The download tool seems like it could handle the reading HTML part, but how would you get Alteryx connected to the Exchange mailbox?  I'm open to using Sharepoint as some intermediary if that helps.

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

I have done something similar in the past but as emails came in I parsed the subject, if a user emailed with a certain subject line I'd then run an Alteryx module.

 

That wasn't accessing Exchange though, does exchange have an API you could use? Alternatively could you use Access to link as a proxy, then link to Access....http://www.outlookexchange.com/articles/ricardosilva/out2acc.asp 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi,

There is an API, called EWS Managed API (https://msdn.microsoft.com/en-us/library/dd633710(EXCHG.80).aspx ), that allows you to:

- Use the Autodiscover service to return the EWS endpoint for an email account, and get user and domain settings for the account.

- Manage the folders that contain your email messages, appointments, and tasks.

- Search for folders, email messages, appointments, and tasks.

- Create, send, forward, and reply to email messages.

- Manage calendar appointments and meetings.

I believe it'll provide all resources you may need to accomplish your goal.

Best,

_AG_

Highlighted
8 - Asteroid

I was able to figure out how to extract emails into an Access database thanks to the clues from this thread. But, I don't really know how to get started to parse out the email content now that I have it in Access. In the case I have, the emails should be in a fairly standard format - these are notifications about the data entered in a web form - so I think RegEx might work. But, I don't know enough about RegEx to figure it out. If anyone has any suggestions or would be willing to assist, I can provide a cleansed example of one of the emails. 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Good to know Michael, that you have them in Access by now.

I can recommend you to have this URL at hand for Regex reference:

http://userguide.icu-project.org/strings/regexp#TOC-Regular-Expression-Metacharacters

 

And this one to testing your expressions:

http://www.regexr.com/

 

I don't know how your email text is, but if you post an example, we can figure it out.

Best,

_AG_

Highlighted
8 - Asteroid

Hi AG. Sorry for the delay...was gone for Thanksgiving Holiday and just catching up. I was not able to attach a cleansed sample email (Outlook item) file. The email is a notification from another regional office with contact/lead information for someone in our region. I have copied the sample body content below in this thread (although the system removed some "invalid" HTML). The email contains the following information (not always there) that I would want to extract from the content:

- name for form

- URL of related landing page

- Request (type)

- Product of Interest

- Comments

- name (first and last seem to be together)

- company/entity name

- Address line 1

- Address line 2 (maybe?)

- City

- Zip/postal code

- Country name

- Email address

 

I'd like to extract these elements into a table format.  I don't know if RegEx or some other tool is the right one to use for this. I do have the body content from these emails now in a single column/field in MS Access that I can use as an input to an Alteryx process. Any help would be GREATLY appreciated.

 

==================== EXAMPLE EMail Copy Below =============================

 

 

 

 

 

 

 

 

 

 

 

Lead Notification from EMEA Marcoms


Hello,

We have received a lead for your region, please see details below.  This is all of the information collected regarding this lead.

Filled Out Form: Poster Web Request ES on OTDR-Poster-es
http://pages.anritsu-emearesponse.com/OTDR-Poster-es.html

If a quote or demo request this information will populate
Request =
Product of interest =
Comments (If any) = 

john smith from XYZCompany RD
Ave. John F. Kennedy #54 Ensache Serrales
Santo Domingo

10119
Dominican Republic

ljohn.smith@claro.com.do

This data will now be removed from our CRM.

Best regards and good luck.

EMEA Marcoms

 

 

 

 

Anritsu EMEA Ltd. 200 Capability Green, Luton, Bedfordshire, LU1 3LU, U.K.
Registered No. 5744468 England.

This email was sent to leonardo.cabrera@claro.com.do. If you no longer wish to receive these emails
you may unsubscribe at any time.

View our Privacy Statement - Copyright Anritsu 2015.

 

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi Michael,

As always, there are many ways to accomplish things in Alteryx, so here is a humble approach to what you asked. 

Below you'll find a sample workflow that parses:

Filled Out Form, 

Determines that the URL you need (there are many in every email text) is the one that follows the "Filled Out Form line" and parses it, 

Request content,

Product of Interest content and 

Comments (multiline)

 

I tried to use several methods, just as a showcase of what you can do. That's why I used REGEX for some text, formulas for others and took the Commnets field (which I asumed it's going to be multiline, without any identifier for the second line) to have all possible use cases in the same workflow.  

 

Hope this helps. Don´t hesitate to contact me if you need something.

Best,

_AG_

 

PS: Thanks @csteele for the alternative approach to what I started doing.

Highlighted
8 - Asteroid

Thanks AG. I'll look at it today and let you know if I have any questions. Much appreciated!

Highlighted
8 - Asteroid

AG - this was great. I think I see how it works now. You took the email content and turned it into multiple "rows" based on carriage returns. Then you used some multi-row formulas and some other parsing steps to strip out the different elements that I was looking for. This is really great, and I think it will get me over the hump on solving this. In fact, another project I was working on late last week may apply to this as well where I did something similar with splitting a comma-separated list of values into multiple fields with a max length each. I used some of these same tools...not the regex, but it looks as if I might be able to figure out how to use that now that the lines/rows from the email are split up. Thanks so much.

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Great! I'm very happy to be of help. 90% of this should go to @csteele though.

There are some "challenges" you have to watch in the data, for example those multiline texts, that have only one header.

 

Best,

_AG_

Labels