Clearing prior month data from output template

Question

I have a workflow that writes one Excel file with three worksheets. The output file is a template because each sheet has specific formatting. It works great EXCEPT, one worksheet has a variable number of records each month. There were 40 records last month; this month I ran it and there were only 16 records. The output only overwrites the first 16 records and leaves the previous months data in rows 17-40. I have my output range set as $A1:O5000 so clearly the workflow doesn't write blank rows to clear out the prior data. How can I do this within the workflow so I don't have to remember to open the template, clear it out, and then run the workflow?  I tried using Table and Render tools, but those truncated data in weird ways no matter how wide I made my columns (?!) and I got irritated enough that I'm here. Any suggestions? Thanks!

TheMattLeonard · Answer

I too have had the misfortune of using the Table and Render Tools. If you want to have specific formatting but also overwrite the whole sheet every time, you should look into using the Blob Input and Blob Output Tools. Basically you'll create a template in excel with all of your formatting but no data, and then point the Blob Input Tool towards it. Then you'll point the Blob Output tool to your Output File (this will overwrite the file every time).

Unfortunately, you also need to make sure you build some Block Until Done tools into your workflow so that the Blob output happens BEFORE your actual data output. This can be a little annoying but unlike the table and render tools there usually IS a way to get what you want if you set it up right.

The way the Blob setup works is it takes a copy of the template each time you run the workflow and bases the output off of that, rather than the previous run of the output. You'll still need your specific ranges and to check the box that says "preserve formatting" but it should accomplish what you want in that it doesn't leave prior results in the file.

https://help.alteryx.com/2018.3/BlobInput.htm

https://help.alteryx.com/20231/designer/blob-output-tool