Error in Output when trying to output in 3 different sheets of same Excel file

Hi All,

I have three output streams in my workflow for three different cases. I have to output them in 3 different sheets of the same excel.

I have used three Block Until Done tools in the following way:

First time when I ran it, it gave the output as desired in three different sheets in the same excel file.

However, the next time I ran it, it gave an error. I have tried some fixes like keeping no space between sheet names. The path of the file is also correct.

The errors keep changing and I get the output file but only one or two of the sheets are there. The errors also keep changing.

Error: Output Data (95): Unable to open file for write: - Attempt to open a file that is already open: The process cannot access the file because another process has locked a portion of the file. (33)

Unable to create backup of UBR.xlsx.bak: Access is denied. (5)

Can someone please help me on how to fix these errors? Or, can recommend any other way to get the desired output?

Output

Accepted answers

pereisyd

Refer to the following discussion

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Outputting-all-3-results-of-a-join-to-separate-tabs-in-the-same/td-p/1070767

SPetrie

If the extra columns caused by using a union and a single output tool are an issue, you can try to cascade them to force an order of operations.

Im not a huge fan of this type of solution because it adds extra tools and should be unnecessary (in a perfect world, the tools actually take their turns like we would all expect).

Basically, you alter your original output and blocks, but add extra steps after the output and append something from that step to the next branch. The block will finish the output and then add the info to the other branch. The other branch cant output until that happens, so it forces the order.

Again, I am not a fan of this method, but if its stupid and it works its probably not stupid.

OutputCascade.yxmd

All comments

SPetrie

Block until done doesnt work that way. Its meant for one to many type situations. The items on the 1 output anchor would finish before the items on the 2 output would start, and so on.

Having three blocks with only one connection each is no different than going straight from the formula to the output tool.

As far as work arounds, there are a few things that can be tried depending on what we are working with. The big question on what route we take is, are all the columns the same in each of those branches? Is the only difference that the sheet names are different?

If they are all the same columns and only the sheet is different, you can use a single output tool to write to all 3. If they are different, then we have to go a different route.

pereisyd

Refer to the following discussion

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Outputting-all-3-results-of-a-join-to-separate-tabs-in-the-same/td-p/1070767

Dheeru28

Thanks @SPetrie Some of the columns would be different in the three sheets. Plus, the three streams are the 3 outputs of a Join tool.

Will be very helpful if you could recommend a way to do it.

Dheeru28

@pereisyd I tried the solution provided in the the link that you sent. It runs fine but at the end the output is not being written to the folder I specified. Do you know what can be the reason? Also, after Union, Left Join output is taking the columns present in the Right Join output. I do not want this in the final output.

123.png

Deano478

@SPetrie good shout there is only a need for 1 block until tool regardless of the join tool or not as the way the outputs are currently set up the block until dones are not really doing anything

Dheeru28

@Deano478 How would you suggest to go about it then? Can you please give an example, if possible?

Deano478

@Dheeru28 Can I ask at the moment why not just create the sheets in the output tool the overwrite them when new data comes in?

Dheeru28

@Deano478 Do you mean separate output tools for separate sheets? If yes, that is something that I can do.

But I'm looking for a way if I can get the 3 sheets in just one file as output.

SPetrie

If the extra columns caused by using a union and a single output tool are an issue, you can try to cascade them to force an order of operations.

Im not a huge fan of this type of solution because it adds extra tools and should be unnecessary (in a perfect world, the tools actually take their turns like we would all expect).

Again, I am not a fan of this method, but if its stupid and it works its probably not stupid.

OutputCascade.yxmd

pereisyd

since you're using "Change entire File Path" in the o/p, the SheetName column should have the absolute path of the XLSX|||SheetName, so I would suggest adding the absolute path to the formula tool!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703