Alteryx: Excel Templates with Blob Tools

Have you ever needed to output your results from Alteryx into an Excel report and does that report require formatting well this blog is for you. This was a technique I picked up in my previous role and spoke about at Inspire Europe 2018 which you can watch the presentation on this link

High Level Approach

The key to this technique is to use the Blob Input and Blob Output to pick up your excel template from one location and then save it to a new location which your Alteryx workflow is also outputting the data to. The only sticking point is you need to ensure your blob tools complete before the output data tool to ensure your template is saved ready for the data being outputted by Alteryx.

Alteryx Excel Template

In the example below is an Alteryx workflow that generates a report for top 20 car makes sales for the months of June, July and August for a mock car garage. The tool container in Blue is not important and just merely represents the function an Alteryx user would perform to get the data ready to produce an excel report. The focus on this blog will be on the Green tool container which is where I move my template from one location to be used to produce a formatted excel report. 

Alteryx Workflow Excel Template

The approach to use Blob tools to move an excel template from one location to an output location is not very complex. I use a Sample Tool to limit my record to 1 as I want to only move my template once. I have also connected my Sample Tool directly after the Input Data Tool as to initialise this part of the workflow so that my template transfer across before the rest of the workflow completes.

Blob Tools Excel Template Overview

The Formula Tool creates two file-paths fields, Input which is where my excel template is located, then Output is where my template should be saved to. The Select tool in this scenario is removing unwanted fields however is actually redundant as the Blob Tools would ignore any extra fields in this situation.

Formula Tool Overview

Configuring the Blob Input is relatively simple choose a dropdown option for “Modify File Name Using Optional Input” which I have selected “Replacing Entire Path With Field” and used my field Input. This will pick up my template from the file-path location and convert it into a Blob field type.

Blob Input Overview

The Blob Output tool is a similar setup to the Blob Input Tool whereby I select “Replacing Entire Path with field” for “Modify File Name By” and I select Output for the field. This will then move my template to the output location.

Blob Output Overview

By using the Blob Tools I essentially pull in my excel template convert it to a Blob which doesn’t affect the formatting or any formulas within the excel files and then outputs to the location specified, ready for the data to be outputted by the rest of the workflow. The Output Data Tool needs to write to the same location which you are saving your template to. 

Output Excel Report

In my example I write to back sheet via the Output Data Tools. The front sheet has been formatted and contains conditional formatting and also has a graph based of the data inputted into this template. By using this technique of using the Blob tools I can create a template to match a company’s colour branding, add in their own logos, customise the report for the end user while being able to automate populating the data from Alteryx and ensure that my template is not corrupted.

Excel Formatted Report

Hope you find this blog helpful. Please leave a comment especially if I missed out a tool or reach out to me on Twitter @JosephSerpis or on the Alteryx Community and Linkedin.

4 thoughts on “Alteryx: Excel Templates with Blob Tools

    • Hi Dan private message me on the Alteryx Community your email. I am happy to share the workflow and excel template with you, my Community user name is JS420.

      Like

    • Hi John you are more than welcome to contact me via Alteryx Community to get the workflow. Currently WordPress does not allow us to save workflows.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s