Database Output on a formatted Excel Sheet to write New Daily Files

Question

Hello,

I have seen several posts around formatting sheets to an output which is then creating a new workbook within that format for daily reporting purposes. I am unsure whether these methods are the most effective approach.

Currently within my workflow there are two containers:

- 1st Container: Input from database to a template file overwriting a specific range to keep the formatting.

- 2nd Container: Using Blob Input as the template file, a formula tool to timestamp the file and a blob output tool to change the file path.

This process I believe works but is there a simpler way of doing this within one workflow to where a new file is being written preserving the formatting so that the historical data is gathered to the final output location. I would like all daily files to be stored in the final output location.

Example of what I would want in the shared drive location:

Report 05-02-2023

Report 05-03-2023

Report 05-04- 2023

Example WF.PNG

Raj · Answer

There are a few ways to streamline this process within a single workflow:

* Use the Directory Tool to dynamically generate the file name with the current date.

* Use the Input Data Tool to read the template file into your workflow.

* Use the Dynamic Replace Tool to replace the relevant data within the template file.

* Use the Output Data Tool to write the modified file to the output location with the dynamically generated file name.

Yoshiro_Fujimori · Answer

Hi @jmathew94 ,

As an Alteryx user, I want to keep a distance from Excel things as long as possible.

But if I were forced to provide output data in Excel format, I would do it separately from data transformation process.

So the post below may not be what you expect, but here is an alteryative approach.

In your case, I would create two separate workflows.

1st WF is to input from database, add a filename for each row, and output to a .yxdb file.

2nd WF is to format it into a table (from scrach on Alteryx using Table tool) and output to .xlsx file with Render tool.

In most of the cases, I would be able to "mimic" the required Excel template with Table tool.

If not, I would negotiate with the user to use my format (which should be easier to maintain, and probably more concise😉).