Control containers firing early

I'm trying to get a bunch of sheets to all output to the same excel file. In my current setup, I have a bunch of output data tools that're in charge of one sheet each and are in their own control containers. First, is there an easier way to do that? Second, occasionally an output tool will throw an error that it can't open the file because another process is using it (aka, the previous output tool). What's the solution here? Is it just that with that many output/control container combos (18 sheets in this excel workbook, 7 sheets to another one), they're bound to throw issues sometimes?

Developer Tools

Output

Accepted answers

All comments

jrlindem

What you are experiencing is not uncommon. Sometimes excel won’t fully close the file despite alteryx seeing the process as done.

does the data all come from the same dataset? If so you could write to the excel file all at once despite differing sheets by building an output filepath. But this won’t work if the schemas as different

can you attach dummy data?

mceleavey

Hi @greeleytaxdept ,

As @jrlindem pointed out, this is a problem with Excel (Excel is not a database).

The more surefire way of getting around this problem is not to use Control Containers but use a batch macro to write each output in sequence. This will ensure the file closes on completion of each batch.

ChrisTX

Try the batch macro attached at the bottom of this blog post. ("what happens if your field schema is different from one file to another?")

The Ultimate Input Data Flowchart
Community > Designer > Designer Knowledge > The Ultimate Input Data Flowchart
https://knowledge.alteryx.com/index/s/article/The-Ultimate-Input-Data-Flowchart-1583459854309

Also consider adding a Like to this Idea:

Enhance options for Excel output
https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Ideas/Enhance-options-for-Excel-output/idc-p/1243963

cjaneczko

Are your files getting saved to or backed up by OneDrive? I have this issue when saving to a OneDrive folder. If you pause syncing while running the flow, this might solve the issue. The second the file has been saved, one drive will attempt to back it up. While its backing up its considered open and in use.

I also tried to submit an idea to add a delay to containers.

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Ideas/Adjustable-Delay-for-Control-Containers/idi-p/1239177

jrlindem

Another option to consider is using the Python Tool to delay for a certain period of time. Not the best option, but still a handy trick to hold onto:

import TIME

print("Start")

time.sleep(2)

print("End")

greeleytaxdept

@jrlindemThere's some overlap between what's being output but it's all got its own schema. To your second point though, that looks exactly like what I was trying to figure out if I could do before posting.

@mceleavey @ChrisTX Never used a batch macro before but that seems like the best option probably. For the moment, my solution is just "run it until it doesn't throw any errors" which works.. well enough I guess.

@cjaneczko Oh, how I hate OneDrive sometimes. I'll need to look into it but it totally might be that.

dreldrel

Hi @greeleytaxdept, feel free to give it a try using this third party delay a second tool.

CReW_DelayASecond.yxmc

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703