Outputting to a formatted excel template

Hi All,

I am new to Alteryx so bare with me! I have no issues getting my data to a raw export (test dummy data attached below). The problem I am having after reading multiple threads is taking this data and outputting this data to a standard excel template by a specific field (in this case Broker #). Ideal final state would be taking the raw file breaking it into separate sheets by broker number and applying the excel template that has already been built in Excel.

Is this possible? I've used the summation tool within Alteryx so the underlying data by Broker # does not appear to be an issue just more of getting the template to apply by each sheet.

Any help is greatly appreciated!

Copy of Excel Template.xls

Test Data.xlsx

Workflow

Reporting

Output

Tips and Tricks

Accepted answers

AbhilashR

@tsch - I have created a sample solution that creates multiple copies of your original template and pastes corresponding broker data into each template based on their NEW MBI id. The output tool is writing to a named range in excel I created called 'brokerOutput'

You could build on this foundation and write another batch script to merge back these individual templates if that is what you are looking for.

Would this get your closer to what you are looking to achieve?

You could also choose to use the Render tools and recreate the template file (with corresponding data) in Alteryx, but you may not be able to achieve your existing excel formatting as is. I chose my approach to only make it easy on myself to come up with something.

Hope this helps.

fileWrite.zip

AbhilashR

@tsch - can you confirm you have a .xls file in the source folder (Inputfile location in my example)? I am able to replicate your error only when I run the workflow without any file in the source folder.

Also maybe modify the config in the run tool to something like this and try? not sure that will solve the issue though.

AbhilashR

@tsch - my output tool writes to a named range in excel, which was only two rows when I first built it. I have now modified it to include few more and it seems to be working. You can modify the range I have set within excel by going to Formulas>>Name Manager>>search for brokerOutput in my case>>Edit>>modify the range as you see fit.

I have attached the modified version for your easy reference. Take a look and let me know if this works.

Hope it makes sense and works for you.

fileWrite_v2.yxzp

All comments

AbhilashR

You could build on this foundation and write another batch script to merge back these individual templates if that is what you are looking for.

Would this get your closer to what you are looking to achieve?

Hope this helps.

fileWrite.zip

tsch

@AbhilashR This appears to be exactly what I am going after. Unfortunately, when I try to run the script I keep receiving the error message Error: Run Command (31): The external program "%temp%\script.bat" returned an error code: 1. What do I need to do on my end to avoid this? I am really new to this so appreciate your insight.

Thanks!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15865

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703