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.
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.
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.
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.
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.
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.