Using Blob input and blob output tool and populate data into excel file

Hello Alteryx Community,

I am trying to use a excel template file with summation formulas in it and populate the Alteryx results with this template. So I could avoid errors when the data is altered.

For example, I have three data rows and if I wanted to make changes and add a fourth row into the output, so then excel would help me add up the correct numbers. I know that blob input and blob output could help me. However, I keep having errors in my final output. Even though the template was read in, but when open the final excel output it says that data needs to be recover. After recovering the summation, formula is taken out.

Book1.xlsx is the final result I would like to have.

Any idea why am I having this error?

Thank you!

Workflow

Developer

Developer Tools

Output

Help

Dynamic Processing

Accepted answers

SPetrie

Using the blob for the template file is a good idea, but I believe the issue you are having is with the excel file itself.

Overriding a sheet and keeping the formatting requires you to give it a range to work with when saving and I believe not giving it the range is what is causing the issue.

You also cant override existing formulas when updating a range, so thats something else we need to account for.

Its a bit annoying, but the way I get around it is to split my flow and have an output for each range I want data to go to.

I edited your flow to use this concept.

Since you mentioned wanting to use the excel formulas, I removed the total formulas from the workflow. I also extended the formulas down in the template excel file.

I use a formula tool to assign the file output name and the range I want the selected columns to go to

Setup the two (or however many you eventually need) output tools and you should be ready

The output data goes to the ranges we specified and since the total columns were not in the range, the formulas are still intact and calculate the totals for us.

Blob Tools.yxzp

All comments

SPetrie

Using the blob for the template file is a good idea, but I believe the issue you are having is with the excel file itself.

Overriding a sheet and keeping the formatting requires you to give it a range to work with when saving and I believe not giving it the range is what is causing the issue.

You also cant override existing formulas when updating a range, so thats something else we need to account for.

Its a bit annoying, but the way I get around it is to split my flow and have an output for each range I want data to go to.

I edited your flow to use this concept.

Since you mentioned wanting to use the excel formulas, I removed the total formulas from the workflow. I also extended the formulas down in the template excel file.

I use a formula tool to assign the file output name and the range I want the selected columns to go to

Setup the two (or however many you eventually need) output tools and you should be ready

The output data goes to the ranges we specified and since the total columns were not in the range, the formulas are still intact and calculate the totals for us.

Blob Tools.yxzp

ashamluo

Thank you for the explanation, now I know what went wrong!

Is there a way to have the range in a dynamic way? The rows of data I am having changes when I used the workflow, is there a way to make it more dynamic?

SPetrie

You are welcome.

As far as dynamically updating the range, the answer is yes. I used a static range, but you can always use formulas or other info to generate the range you are putting into the output file name.

Neelima1

Hi SPetrie

Thank you for the solution, its working fine in generating single output. Can we generate multiple outputs with same template, since we need to generate output based one column contains multiple job codes. Please advice

SPetrie

@Neelima1 Yes, multiple outputs can be generated. How you go about it depends on what exactly you are looking to do. Multiple template file outputs would be handled differently than outputting to different sheets within a single template file for example.

It may be best for you to create your own post in the community with an example input and desired output and I or someone else can better assist.

Neelima1

submitted the new post...

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235