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
Comet

@rpaugh Thanks for the reply. As I said in my original question, we are using https://outlook.office365.com/EWS/Exchange.asmx, which means it is already a manual service URL, or I wouldn't have to enter the URL. And because the same workflow, which I opened from the Gallery, works perfectly on my desktop, I know there is no typo. And I copied and pasted the above URL directly from the manual service URL field.

 

I passed this on to our technology infrastructure group, who spent quite a bit of time tracking this down. Apparently the link you provided did not help because, "The commands in the article are to be run in an Exchange command shell for Exchange 2013, we are running in Office 365 with Exchange 2010 for hybrid which is being upgraded to 2016." However, in their digging, they found the cause: your tool uses TLS 1.0, which is disabled on all of our servers due to security restrictions. "TLS 1.0 is vulnerable to man-in-the-middle attacks making it non PCI compliant.  This article describes it a bit: https://www.comodo.com/e-commerce/ssl-certificates/tls-1-deprecation.php."

 

The reason it works on my desktop is that the security policy only applies to servers. And the PowerShell test on the server works because it uses TLS 1.2. Perhaps this is why Microsoft is deprecating that version of the C# API?

 

So, we are hoping that the new HTML/JS SDK version might not have this issue. Can you help us save some time by sharing whether that may be the case?

 

Bolide

@blyons I ran a packet analyzer on both versions of the tool and it looks like the C# version is using TLSv1 and the JS version is using TLSv1.2.  Are you able to install and test the new version on your server?

Meteor

@rpaugh I downloaded off of https://github.com/rpaugh/Alteryx-Outlook-Input and renamed the .zip and got an error that the config file was missing, tried going into it one folder lower and zipping/renaming it as well but same outcome on it. Will attempt to email it to myself this weekend.

Bolide

@JMoore ah I see.  Downloading from GitHub nested it in another folder with the GitHub project name.  After you download the full project zip, you'll have to open it and re-zip the sub-folder content with the yxi extension:

 

Alteryx Tool Installer.png

@rpaugh thanks for the prompt reply!


1) I tried your example and sucessfully saved a CSV attachment, but it did not work with .xlsm or .xlsx, which saved with proper name and extension, but did not have any information inside (1 KB). After this I read a little about Blob and binary files and found it is really not supposed to work, am I correct? Is there any workaround?

 

xlsm_xlsx.PNGempty blob

2) Will make sure I relay that information to my support, they say the tool is installed at server running instance, but I keep getting the following error, which I strongly believe says server could not find the tool:

 

The job read_outlook_ews did not complete successfully and resulted in the following errors:

Error: Unable to resolve plugin HTML 'Outlook Input\Outlook InputEngine.html' (Tool Id: 1)

 

 

Meteor

@rpaugh that worked perfectly! I will start testing over the next few weeks, interested to see where the blob discussion on attachments ends up and on PDF's. 

Comet

@rpaugh Thanks so much for checking the TLS version. I installed the latest JS beta version on our server and it works great!

 

Others who make the switch will probably want to note that the format of some fields changed significantly, specifically any DateTime field (received, sent, etc.) and Duration. Changes will need to be made to the parsing in the Alteryx workflow accordingly.

 

Thanks again for this tool. I really appreciate your continued support and the latest version of the tool.

Bolide

@JMoore I believe I’ve figured out how to make the attachments work. I’ll get an updated version published hopefully over the weekend.

Bolide

@JMooreand @A_Ribeiro  I think I've got the attachment issue worked out.  The content was coming in as Base64, so I switched the output from blob to V_WString in the code and leave the conversion up to you.  This allows you to output in the original format using blob convert/output:

 

Alteryx Outlook Input - Blob Output Example 2.png

 

I've updated the yxi so downloading from the gallery again will give you the latest version.

Bolide

@A_Ribeiro to your second question: did they install the tool for all users?  Also, are you able to go into the server folders and see if you have "C:\ProgramData\Alteryx\Tools\Output Input" (or similar path depending on your structure)?

@rpaugheverything works! Running on the server, filters OK, different mailbox works, downloading .xlsm to a network drive and rocking my productivity!! Thank you so much for your time and effort towards this tool! Allow me to buy you a (lot of) beers when you come to Brazil!

 

Bolide

@A_Ribeiro I'm so excited that it's working for you!!!  Just curious, were you able to use the autodiscover feature or are you using a manual service URL?

Asteroid

We use SSO to connect to Office 365 email.

https://outlook.office.com/owa/

 

I am having zero success connecting. 

 

Any insights or workarounds that I should be doing to get this to work- or am I out of luck? 

 

Regards,


Adam

Bolide

@aehrenwo you should still be able to use the tool - I was able to successfully connect when I had my Office365 dev account.  You'll just have to switch your login from your regular email address to your domain formatted email address (username@domain.com).  For example, john.doe@acme.com connecting to a North American domain might switch to something like jdoe@na.acme.com.  You may have to check with IT to get your exact domain format and settings, but it should still work.

@rpaugh Using manual service URL, but this is really minor, I don't mind a single bit... As far as I'm concerned this version is production ready! Thanks, and I'm serious about the beers! Cheers mate!

Bolide

Haha, thanks @A_Ribeiro !  And honestly, your best bet is to use a manual service URL anyway because it will be faster than autodiscover.

Asteroid
Thanks I will try that combination and will reach out to IT if I can't get it to work

Regards,

Adam Ehrenworth, CCEP

This email was written on my mobile device apologies for any typos.

Get Outlook for Android
Asteroid

I tried a few different combinations and one, in particular, showed promise it ran for a full 3-4 minutes and then finally produced a similar error.

 

Is there a particular manual service URL I should try if we can connect to our mail server.

https://outlook.office.com/owa/

???

 

I may be complicating things by trying to use an alternative email box instead. that is the one I need the data on. 

 

Thanks- trying to solve without IT first as those tickets tend to go to a black hole. 

 

Adam

Bolide

@aehrenwo try https://outlook.office365.com/ews/exchange.asmx with your domain credentials.

I am having an issue with this tool.  I have it set up on a workflow and have it attached to a Mailbox and it returns information from that mailbox.  However, I do not get the whole message body in any of the fields that have body type information in them (Body, Preview, TextBody, Normalized body, etc) they all end with ...

 

Any suggestions?

 

Dennis

Bolide

@DENNISRTURNER you're probably reaching the cell limit in the Alteryx results window.  Try putting a browse tool at the end and viewing the cell contents from there - or output to a file to see if it gets all of the data.  Let me know if you still can't see all the data.

@rpaugh, You are correct.  Guess you can tell I am new.

Bolide

@DENNISRTURNER haha, we were all new at some point Smiley Happy.

Hi, 

 

I installed the tool, but i am getting "FindItems" error everytime i tried running the workflow. Can someone please help me in resolving this.

FYI, i was using the C# version of this tool with similar settings and it used to work perfectly. Any suggestions please ??

Outlook Input_JS error msg.JPG

Hi,

 

I think i got the new tool (JS version) working using the manual URL service now (didnt work with autodetect i guess).

But i am still facing some issues like - 

1. Alteryx designer crashes (hangs) when i try to run the tool with 'Attachments' as one of the selected elements. (even when querying a very small data)

2. Not able to get the data from Sub-folder of shared mail box.

3. Feels like it is bit unstable compared to the earlier C# version, as sometimes the workflow runs smoothly but other times it hangs or show no results.

 

Not sure if i am using some wrong configuration settings or any issue with installation.

Any suggestions would be greatly appreciated !! 

 

Thanks,

Rohan.

 

Bolide

@rohan_tibarewala yes, that error was an issue with autodetect.  

  1. Are you pulling big attachments, or many attachments from a small set of emails?  Also, may not be the issue but just curious, are you selecting the "Has Attachments" field in conjunction with the "Attachments" field, or just the "Attachments" field?
  2. What does your configuration look like for the shared mailbox?  If the sub-folder is under the Inbox on a shared mailbox then you would check the field for "Use Different Mailbox", enter the name of the other mailbox, select "Inbox" as the folder to search, and check the box for including sub-folders.  You may have to play around with the configuration hierarchy a bit (e.g. selecting something like MsgFolderRoot or PublicFoldersRoot for the shared mailbox and search for the sub-folder under there).  If it's a public shared folder (not necessarily associated with a mailbox) then that may not work yet - I still need to update the tool to pull in public shares.
  3. What criteria changes between a smooth run and a hanging run?  Are you able to pinpoint a particular field or folder that triggers the crash?
Atom

Hi @rpaugh,

 

Firstly, thank you for all the great work here. I do appreciate it. 

 

Could you please let us know what you inputted in the configuration panel for "blob convert" and "Blob Output" tool?

 

Capture.PNG

Below is the output i get from your Outlook Input tool. I can't seem to figure out how to open this file.

 

Capture2.PNG

 

Thank you in advance,

 

Peter

Bolide

@pgeorges yes, of course.  

 

Blob Convert:

Alteryx Outlook Input - Blob Convert.png

 

Blob Output:

Alteryx Outlook Input - Blob Output.png

 

Result:

Alteryx Outlook Input - Blob Output Result.png

Atom

@rpaugh Thanks, it worked!

 

Is there an alternative solution where instead of using the "Blob Output", I can use a different tool to read .xlsx file and continue my workflow?

 

Bolide

@pgeorges if you have something that meets one of the default encoding offered by the blob convert tool (e.g. csv file) then you can convert or use a base64 decoder from there and stream directly to the rest of your workflow.  However, anything else looks like it will have to be written to a file that Alteryx can expect.  This really isn't any different from the previous version of the tool with the exception that you now have full control over naming, location, and other manipulations of the file attachment that you didn't have before.  The good news is that you can still do this using a combination of blob output and dynamic input:

 

Alteryx Outlook Input - Blob Output Stream.png

Atom

@rpaugh genius. Thanks for all your help!

Hi, 

 

Does this tool work under version 2018.4? or even the newest version of alteryx?

I am having a hard time installing the tool. Although installation is successful I cannot see the tool under Connectors.

 

Any help is highly appreciated!

Bolide

@daguilar076 What version of the tool are you installing, the yxi installer or the other one?

Atom

Hi @rpaugh,

 

Is there anyway to install without running the setup msi?  Long story, but we do not have access to install executables on our pc's.  We currently have v11.5.1 installed.

 

Thanks,

 

Mark

 

Bolide

@memo70 the only way to install without the msi is to use the newer tool built on the html/js SDK.  It's currently technically in beta but so far seems to be running well.  

Labels