community
cancel
Showing results for 
Search instead for 
Did you mean: 

Engine Works Blog

Under the hood of Alteryx: tips, tricks and how-to's.
Bolide

How does one automate that which cannot be automated?

 

We are in an information age.  An age where someone can monitor the security of their home and unlock their care from a mobile device; start streaming a movie at home, move to an iPad, and finish on a plane; and even blend, cleanse, and predict data with little or no data science knowledge.  And yet many, if not all of us, at some point have tried to gain access to data within our own organizations only to be met with the solution of “we can email you an Excel file”.  Really?!?  I can put money into my Starbucks account via my phone, and have Starbucks scan and recognize the payment, deduct the cost from my account and give me my bonus stars for the transaction, but the only solution you have to give me data is to email it to me?  Now is a good time to tell you something important about myself – I’m lazy.  I loathe manual, tedious, repetitive tasks.  I often joke with my coworkers that I’m going to automate everything I do so I can sit and watch YouTube all day.  In fact, were I to successfully automate myself out of a job I would consider that the single greatest accomplishment of my career.  As you can probably imagine, I was not willing to manually download and process Excel files each week.  Fortunately, I come from a software development background and was able to write code to extract attachments from Outlook emails and baked that into an SSIS package that ran weekly. 

 

OutlookInputTool.pngOutlook Input toolFast forward a couple of years to Inspire 2017.  I was having breakfast before the opening keynote and I heard an Alteryx employee talking about using Alteryx to blend data from a file she receives from another department.  I had a hunch and asked her how she gets those files.  I stifled a literal “LOL” moment when she said she receives them via email.  I told her I had some code that might help her get to a fully automated solution.  Later in the conference I attended a session on using the Alteryx API and SDK.  I paid particularly close attention to the SDK portion because at the time I had no idea creating custom tools was even an option.  I know this is going to sound lame and cliché, but at that moment I was truly inspired to jump into code and build my own custom input tool to pull attachments out of Outlook emails.  I know, I know, too corny.  Moving on.  As a fantastic conference was coming to a close I just happened to spot one of the engineers, @JPKa,  who hosted the session on using the API and SDK at the closing reception.  I told him my idea and he pointed me in the right direction to get started.  Two weeks of personal time later I had  a solid version 1  of the tool and I shared it with my friends at Alteryx.

 

 

 

Working with the SDK

 

OutlookToolConfig.pngOutlook Input Tool ConfigurationEverything I needed to get started was already installed with Alteryx Designer. I used a combination of reading through and interpreting the sample and the getting started documentation. Honestly, my first impression was confusion.  The sample project involves using an XML file containing Alteryx tool XML config information as an input to set the tool XML config information.  When it came to interpreting the code to understand how it was linking the xml config information to the input data I was often confused as to whether the xml components in question were part of the Alteryx configuration or the input tool that was providing the configuration.  Simply put, it was setting configuration from configuration. If anyone from Alteryx Product Management is reading this,  a better sample might be to pull dummy customer data from a .csv file and send it to an output stream.

 

My biggest obstacle was interpreting the sample code - figuring out how to separate the xml config as input from the xml config that was reading the input.  It makes perfect sense now, but when I was trying to understand the custom tool architecture and input file interpretation simultaneously for the first time I had some trouble.

 

 

Once I had a working tool I went back and forth with the folks at Alteryx and there were some limitations there with getting them the install and sharing attachments. I discovered I could wrap the install into a yxzp file and include instructions and a help file. JP was very helpful with identifying some of the nuances of working with the SDK and offered some suggestions on improving the tool. I needed to incorporate the "UpdateOnly" parameter because the C# code was executing every time a new tool was added to the canvas. I didn't know that the Alteryx Engine had a special feature where it runs the workflow (with no actual records other than metadata) every time a new tool is added.  

 

He also suggested I add some date filtering capability to the tool, allowing people to avoid downloading their entire inbox if they select “Inbox”.  For this I added a configuration element for the user to input a query string and included a link to the documentation to build the query.

 

 

 

Using the Outlook Input Tool

 

You may be tempted to point this sucker to your Inbox right out of the gate, and I don't know what your Inbox looks like, but if it looks anything like some of our testers, I'd advise against that. Play with your drafts folder or deleted items first.

 

You may find copying the search syntax from Outlook into the Query String helpful:

 

copy querystring.pngcopy the query string from outlook

 

As for me, this is how I've been using the tool. In this example I filter out a specific Excel file and send it through a Dynamic Input tool to see the results. I'll leave it up to you to discover new and interesting ways to consume the files that you no longer have to manually fish out of your inboxes.

 

OutlookInputToolSample.png

 

 

giphy-downsized (2).gifBut wait, there’s more!  Act now and as a bonus you get the added message stream allowing you to perform any number of analytics on your email messages.  And if that isn’t enough, join the two streams together on Message ID for maximum analytical potential!!

 

 

 

 

What's included in the package

 

You'll find an Alteryx *.yxzp file attached to this post. When you open it in Alteryx Designer, you will see installation instructions:

 

Outlook Tools Install instructions.png

 

and a help file

 

Outlook Tools Install help file.png

 

The workflow is a single RunCommand tool that will install the Outlook Input tool. You can find my source code on Git Hub.

 

I hope you find the Outlook Input tool useful. This has been a fun and exciting project and I can't wait to see what creative ways the community finds to use this tool. Use the comments below to let me know your experience working with the tool.

 

UPDATE 8/1/2017:

A new version is available on the gallery: https://gallery.alteryx.com/#!app/Outlook-Tools-Install/597b35c2f499c716ec34a782.

 

Changes:

  • Implemented paging to prevent timeouts through the Exchange Web Services when working with large inboxes and complex filters.  Note: you still may need to adjust throttling settings on your server: https://msdn.microsoft.com/en-us/library/office/jj945066(v=exchg.150).aspx.
  • Updated the installer to remove previously installed versions of the tools so it's not cluttering up your programs list.
Comments
Bolide

@Yanoflies I just got word that the deprecation deadline for the C# SDK has been extended through 2021, so you should be good for a couple of years while you transition to the new tool.  

Meteor

@rpaugh thank you so much!

The new .yxzp file on the gallery appears to be different and the setup broken but I managed to get the updated code anyway.

For some reason in the line:

 

 

return service.FindFolders(new EwsJS.FolderId(c.FolderToSearch, mailbox), folderView)

 

 

It is throwing that mailbox is not defined.

 

image.png

 

I think it is because mailbox is defined within Alteryx.Plugin.PI_PushAllRecords and you are now referring to it from outside.

 

 

var mailbox = new EwsJS.Mailbox(c.UseDifferentMailbox == "True" ? c.Mailbox : c.UserName);

 

 


This is what I did:

w/ Search Filter [i.e. if (c.SubFolderName != "" && c.SubFolderName != null)]

 

 

return service.FindFolders(new EwsJS.FolderId(c.FolderToSearch, new EwsJS.Mailbox(c.UseDifferentMailbox == "True" ? c.Mailbox : c.UserName)), new EwsJS.SearchFilter.ContainsSubstring(EwsJS.FolderSchema.DisplayName, c.SubFolderName), folderView)

 

 

w/o Search Filter

 

 

return service.FindFolders(new EwsJS.FolderId(c.FolderToSearch, new EwsJS.Mailbox(c.UseDifferentMailbox == "True" ? c.Mailbox : c.UserName)), folderView)

 

 

I can confirm that the new logic works if you address var mailbox as I am now getting results from sub-folders, seriously thank you so much for your work.

 

 

I don't have the ability to quote from a reply yet but really happy to hear the deadline is now in 2021 because like you say, I will have more time to transition.

 

 

If you have any clues as to how I could build the modified property into QueryString that is probably the last feature I am hoping for.

Bolide

@Yanoflies there shouldn't be a .yxzp file - it should be .yxi.  Did you download the whole gallery app?  If you run it and get to the final report, the download link for the yxi file is at the bottom.  I actually just ran it and verified that the installers works ok.

 

Regarding the code, the update I made required a change in the mailbox variable scope, which is why it was saying it was undefined.  If you look further up the code you'll see that it's declared at a global level and set at the local level inside the PI_PushAllRecords method.

 

Unfortunately, it looks like the QueryString issue is a limitation of Microsoft's EWS API.  There's really no way around it unless they add that feature.  That said, the SearchFilter object could probably be used in lieu of QueryString to filter on the last modified field, but that would require a significant change to the code to address all possible searching via that object.  I could certainly look into it, but it would not be a quick fix.

.

 

Meteor

@rpaugh the gallery app is fine now.

Previously the run button was greyed out and prompted that it couldn't be ran if clicked on. Now it's blue and works so it's all good!

 

I appreciate the explanation, thank you.

 

I'll study your code a bit more and explore the SearchFilter object, lots to learn.

Alteryx Partner

Rick, this is a super useful tool. Thank you for contributing it. I had it all working beautifully, but after upgrading to 2019.2 I lost the dll (showing as question mark in my workflows). I've uninstalled, and re-installed, but I can't get it back. Any ideas? 

Meteoroid

Hi @rpaugh 

 

Experimenting with the JS version now, it runs considerably faster than the older one.

 

However I am experiencing a strange issue: I have both new and old setup, looking at a different mailbox to my own. Same setup.

 

The old is returning 63 records, the new is returning 64. However the new appears to be returning 10 items from my own mailbox + 54 from the selected mailbox. The old is returning 63 items from the selected mailbox.

 

Interestingly the 9 missing items seem to be undeliverables/auto responses? (Not sure if this is an intended feature). I also cannot workout why it is including 10 items from my personal mailbox.

Bolide

@BPrior hm...sounds like when I applied the mailbox fix, it didn't remove the reference to the primary mailbox's root folder.  I'll look into that.  Just curious, when you're searching another mailbox are you searching at the root level or a sub-folder level?  If you're searching a sub-folder level on the other mailbox do you still get the 10 emails from your primary inbox?

Bolide

@PhilBalderson I'm assuming when you re-installed the tool you matched the installation directory to your Alteryx directory?  If you go into the Alteryx custom tools folder do you see all the proper DLLs and configurations there?  Locate the .ini file and make sure that it's in a directory readable by Alteryx and contains the proper directory path within to the installed DLLs.

Meteoroid

@rpaugh I did some testing on a different mailbox, comparing old tool and new tool.

 

Both set-up the same searching received emails for yesterday..today in the Inbox and any subfolders.

 

Test 1: Include sub-folders ticked

Old Tool: 77 results, all from the other mailbox

New Tool: 37 items, 26 from the mailbox + 11 from my personal subfolders.

 

Test 2: Sub- folders not ticked on both tools

Old Tool: 26 results

New Tool: 26 results

 

For whatever reason ticking the sub-folder box still only picks up items in my sub-folders and I believe this is the volume difference I'm seeing when testing on various mailboxes. 

 

Bolide

@BPrior Thanks for the info.  I'll look into this as soon as I can.

Meteor

Hi @rpaugh

I am using outlook tool for extracting the excel files based on a particular subject or email. this is working fine. But as I said that this daily refreshes the movement am executing the workflow it is getting all the files which I got earlies instead of that I need only for the selected date. so that I can avoid extracting thousands of files again and again.

@gantaanvesh

One of my fellow users experienced a similar issue. Use the Formula tool with a "DateTimeToday()" value (or a variation depending on how often you run) and compare the email date. Then action on the emails with a greater value.

Hope this helps.

Thank you.

Meteor

 

@Data_is_mymiddlename 

In the configuration of outlook tool am specifying the path to download the movement i ran the workflow it will directly save in the respective folder. In this case where I can give datetimetoday() function ?

 

clipboard_image_0.png

I have to say that I love using this tool...  Right up to the point that my company went to Office 365.

 

I did change the Service URL to https://outlook.office365.com/EWS/Exchange.asmx

 

Now I get this error message.  

"OutlookInputTool (1) The request failed. The remote server returned an error: (401) Unauthorized.¶ServiceRequestException¶ at ..."

 

Has my company not allowed access for this tool?

Asteroid

@Darrie8468 go to that link and input your company credentials to start the service then attempt to use the tool.

Bolide

@gantaanvesh the DateTimeNow() function would go into a standard Filter tool.  In the Outlook configuration window you can use something like "received:yesterday" in the query section, but as other users mentioned you may end up with slightly more records than expected depending on time zones and other settings.  In any case, this should still greatly reduce the list of results.

 

@Darrie8468 it's possible your IT team locked down this access.  At least one other person reported having this issue.  However, "401" mean unauthorized so it may just mean you need to log in using whatever domain username your company set up with Office365.  For example, when my last company switched to Office365 I had to log in with "username@nala.company.com" instead of "firstname.lastname@company.com".  It's also possible that they set up different authentication altogether such as OAuth, which hasn't been incorporated yet but I'm hoping to look into.  Check with your IT team to see what restrictions they set and if there are any special login settings.  

Meteoroid

@rpaugh- this is for O365

 

We have below now <OWAUrl AuthenticationMethod="LiveIdFba, OAuth">https://outlook.office365.com/owa/</OWAUrl>

 

earlier it was <OWAUrl AuthenticationMethod="Fba">https://************************/owa/</OWAUrl>

 

Thanks

Meteor

I'm encountering more and more errors with the JS version ... I think I will move back to the C# version and live with my previous issues for the time being.

clipboard_image_0.png

Error: Outlook Input (2): Error parsing json at position 529: {"Event":"Console","Message":"CEF Console: source=</ews-javascript-api/ExchangeWebService.js> message=\"Deprecation warning: value provided is not in a recognized RFC2822 or ISO format. moment construction falls back to js Date(), which is not reliable across all browsers and versions. Non RFC2822/ISO date formats are discouraged and will be removed in an upcoming major release. Please refer to <a href="http://momentjs.com/guides/#/warnings/js-date/" target="_blank">http://momentjs.com/guides/#/warnings/js-date/</a> for more info.
Arguments: 
[0] _isAMomentObject: true, _isUTC: false, _useUTC: false, _l: undefined, _i: 2019-05-21T13:27:00+10:00Z, _f: undefined, _strict: undefined, _locale: [object Object]
Error
    at Function.createFromInputFallback (/ews-javascript-api/ExchangeWebService.js:71385:94)
    at configFromString (/ews-javascript-api/ExchangeWebService.js:73243:11)
    at configFromInput (/ews-javascript-api/ExchangeWebService.js:73612:9)
    at prepareConfig (/ews-javascript-api/ExchangeWebService.js:73595:9)
    at createFromConfig (/ews-javascript-api/ExchangeWebService.js:73562:40)
    at createLocalOrUTC (/ews-javascript-api/ExchangeWebService.js:73649:12)
    at createLocal (/ews-javascript-api/ExchangeWebService.js:73653:12)
    at hooks (/ews-javascript-api/ExchangeWebService.js:71081:25)
    at Function.DateTime.Parse (/ews-javascript-api/ExchangeWebService.js:2602:21)
    at ExchangeServiceBase.ConvertUniversalDateTimeStringToLocalDateTime (/ews-javascript-api/ExchangeWebService.js:34005:37)\" line=71358"}" error: "Invalid escape character in string."

 
When I have the time I'll go back and try to figure things out but I've wasted too much time already haha.

Meteor

@rpaugh

In the query section am already giving the condition like extract From- abc@tc.in. so if I want to give Day range(received: Yesterday) where i have to give ??

 

Asteroid

@rpaugh what is the protocol used to send the authentication and receive the emails on? Which part of the code deals with this?

@rpaugh Yes.  We are using OAuth.  Please let us know when you get a chance to look at the code to allow OAuth.  In the meantime, I have been forced to link the Inbox to Microsoft Access, then create a table from that link, then have an Alteryx workflow see that table.  All that just to do what the wonderful tool already did...

Bolide

@Yanoflies would you mind sending me a screenshot of your configuration window?  I'll see if I can replicate locally.

 

@gantaanvesh you can chain query clauses together with a space between them.  So in your example you could do something like "from:abc@tc.in received:yesterday".  

 

@JMoore It's using basic authentication via TLS 1.2 for the js version I believe.  I need to update it to use OAuth, but just haven't had the time yet.

 

@Darrie8468 I haven't used Access in awhile - you can use OAuth to connect to Exchange via Access?  Or is it allowing you to use a different authentication method?

@rpaugh - Honestly, I do not know what method Access is using.  All I know is that in Access you can link to outside data sources.  One kind of connection is to the Outlook tables:  Inbox, Contacts, Calendar...  I have to create a real table via a query so Alteryx can see it.  Alteryx can't see linked tables.

Meteoroid

Hello,

 

@rpaugh Thank you for creating the tool.

 

After successfully installing the tool on my computer. The tool does not show up on my Alteryx Designer interface? I also searched for it, using the search bar but I couldn't find it. Is there any additional installation step or am I missing something? Thanks

Bolide

@abdulib did you install the C# version (with the install wizard) or the js version (using the Alteryx-embedded installer)?  This is a common issue with the C# version because you need to select the installation directory that matches the location of Alteryx - which is not always the same since sometimes installs are performed for the local user only and not everyone on the computer (i.e. using the AppData folder instead of standard Program Files).

Meteoroid

@rpaugh  I used this one: https://gallery.alteryx.com/#!app/Outlook-Tools-Install/597b35c2f499c716ec34a782

Found in the original post.

It was installed in "C:\Program Files\Alteryx\" which, I beleive, is the location of Alteryx on my computer.

 

How can I tell which version it is or where to find the correct version. 

 

 

Bolide

@abdulib that's the C# version.  You'll have to verify the installation location of Alteryx as well as the tool.  If your Alteryx is located in the standard Program Files directory then you should see an "OutlookInputPlugin.ini" file located at "C:\Program Files\Alteryx\Settings\AdditionalPlugins".  The file itself should then contain the following path: "C:\Program Files\Alteryx\Outlook".  If that is not the case then you can either move all of the plugin files to the correct directory or reinstall the tool and change the installation directory (there's an option to do this during the install) to match Alteryx itself.

Meteoroid

@rpaugh 

 

see an "OutlookInputPlugin.ini" file located at "C:\Program Files\Alteryx\Settings\AdditionalPlugins" and the file itself contains the path: "C:\Program Files\Alteryx\Outlook".

 

so do I need to move the  plugin files from "C:\Program Files\Alteryx\Outlook" to  "C:\Program Files\Alteryx\Settings\AdditionalPlugins" ? which would be the correct directory?

Sorry if my questions seem a little basic. 

 

 

 

 

Bolide

@abdulib no, the .ini file and the plugin DLLs will not be in the same directory so you're good there.  What you need to do now is verify that your Alteryx installation is located at "C:\Program Files\Alteryx".

Meteor

@rpaugh 

Here's my configuration:

clipboard_image_0.png

 

Error:

Error: admissions_in: Error parsing json at position 528: {"Event":"Console","Message":"CEF Console: source=<file:///*/AppData/Roaming/Alteryx/Tools/Outlook%20Input/ews-javascript-api/ExchangeWebService.js> message=\"Deprecation warning: value provided is not in a recognized RFC2822 or ISO format. moment construction falls back to js Date(), which is not reliable across all browsers and versions. Non RFC2822/ISO date formats are discouraged and will be removed in an upcoming major release. Please refer to <a href="http://momentjs.com/guides/#/warnings/js-date/" target="_blank">http://momentjs.com/guides/#/warnings/js-date/</a> for more info.
Arguments: 
[0] _isAMomentObject: true, _isUTC: false, _useUTC: false, _l: undefined, _i: 2019-09-06T14:16:12+10:00Z, _f: undefined, _strict: undefined, _locale: [object Object]
Error
    at Function.createFromInputFallback (file:///*/AppData/Roaming/Alteryx/Tools/Outlook%20Input/ews-javascript-api/ExchangeWebService.js:71385:94)
    at configFromString (file:///*/AppData/Roaming/Alteryx/Tools/Outlook%20Input/ews-javascript-api/ExchangeWebService.js:73243:11)
    at configFromInput (file:///*/AppData/Roaming/Alteryx/Tools/Outlook%20Input/ews-javascript-api/ExchangeWebService.js:73612:9)
    at prepareConfig (file:///*/AppData/Roaming/Alteryx/Tools/Outlook%20Input/ews-javascript-api/ExchangeWebService.js:73595:9)
    at createFromConfig (file:///*/AppData/Roaming/Alteryx/Tools/Outlook%20Input/ews-javascript-api/ExchangeWebService.js:73562:40)
    at createLocalOrUTC (file:///*/AppData/Roaming/Alteryx/Tools/Outlook%20Input/ews-javascript-api/ExchangeWebService.js:73649:12)
    at createLocal (file:///*/AppData/Roaming/Alteryx/Tools/Outlook%20Input/ews-javascript-api/ExchangeWebService.js:73653:12)
    at hooks (file:///*/AppData/Roaming/Alteryx/Tools/Outlook%20Input/ews-javascript-api/ExchangeWebService.js:71081:25)
    at Function.DateTime.Parse (file:///*/AppData/Roaming/Alteryx/Tools/Outlook%20Input/ews-javascript-api/ExchangeWebService.js:2602:21)
    at ExchangeServiceBase.ConvertUniversalDateTimeStringToLocalDateTime (file:///*/AppData/Roaming/Alteryx/Tools/Outlook%20Input/ews-javascript-api/ExchangeWebService.js:34005:37)\" line=71358"}" error: "Invalid escape character in string."

 

Bolide

@Yanoflies what fields did you select for output?  I think the issue is that tool is trying to use the javascript Date type on a field with data that can't convert properly.  I ran into this issue before and thought I had accounted for it, but you must have found a date format I haven't seen yet.

Meteor

@rpaugh 

 

 

ConversationTopic
From
Sender
Id
ItemClass
Subject
Sensitivity
DateTimeReceived (Inbox)
Size
Categories
Importance
DateTimeSent (Sent Items)
DisplayCc
Displayto
ConversationId
TextBody

 

 

Bolide

@Yanoflies I was unable to reproduce the error on my end - all fields returned values correctly.  On that note, I wonder if you're getting dates on certain items in unexpected formats.  Are you or your server in different time zones or countries and/or is the date format on your computer itself set to a different format?

Labels