Workflow throwing errors when run through gallery but works fine when run locally

[AkisM]

The workflow is relatively complex and it writes various info to various sheets of the same excel file, at the same time.

I used all relevant "Block Until Done" tools to ensure that the info is written one by one, only after the previous write finishes, to ensure that I won't be getting any "cannot open file because it is being used" errors.

The workflow does in fact run just fine locally with no errors whatsoever. However when I run it through the gallery, I receive various lines of the above mentioned error.

• Unable to open file for write: C:\ProgramData\Alteryx\Service\Staging\2140_737bc27fd10d428486833f6c625ce376\Output.xlsx - Attempt to open a file that is already open: C:\ProgramData\Alteryx\Service\Staging\2140_737bc27fd10d428486833f6c625ce376\Output.xlsx: The process cannot access the file because another process has locked a portion of the file. (33) (Tool Id: 158)
• Unable to open file for write: C:\ProgramData\Alteryx\Service\Staging\2140_737bc27fd10d428486833f6c625ce376\Output.xlsx - Attempt to open a file that is already open: C:\ProgramData\Alteryx\Service\Staging\2140_737bc27fd10d428486833f6c625ce376\Output.xlsx: The process cannot access the file because another process has locked a portion of the file. (33) (Tool Id: 159)
• Unable to open file for write: C:\ProgramData\Alteryx\Service\Staging\2140_737bc27fd10d428486833f6c625ce376\Output.xlsx - Attempt to open a file that is already open: C:\ProgramData\Alteryx\Service\Staging\2140_737bc27fd10d428486833f6c625ce376\Output.xlsx: The process cannot access the file because another process has locked a portion of the file. (33) (Tool Id: 160)
• Unable to open file for write: C:\ProgramData\Alteryx\Service\Staging\2140_737bc27fd10d428486833f6c625ce376\Output.xlsx - Attempt to open a file that is already open: C:\ProgramData\Alteryx\Service\Staging\2140_737bc27fd10d428486833f6c625ce376\Output.xlsx: The process cannot access the file because another process has locked a portion of the file. (33) (Tool Id: 157)

The strangest part is that this workflow (unchanged) used to work just fine on the gallery as well. Not sure what changed. Any ideas on how to resolve this?

[Pingu]

I think you can also get this error if the Gallery does not have access to your local files. So you would need to store them to a network location and make sure the gallery has the necessary access to that drive.

[atcodedog05]

Hi @AkisM 

You can try deleting the output file before generating output and check.

I would also suggest you to try Crew Macro's Wait a Seconds tool to sequence outputs.

Hope this helps : )

[danilang · Accepted solution]

Hi @AkisM 

What you're seeing on the server may be caused by the OS.  When you tell the OS to close a file, the OS returns OK and then asynchronously performs a final buffer flush.  In general, this speeds up systems by de-coupling the foreground programs from background OS operations.   If you try to reopen the file before this buffer flush is complete, you'll get a file in use error. 

For most programs this isn't a problem because they calculate all the data, open the file, write all the data creating new tabs as required(if using excel) and then close the file.  Alteryx is different, since each output tool acts as it's own little program.  The file is opened, data is written to the tab in question and the file is closed.  The next output tool has to repeat the process, starting with opening the file again.  If the second output runs too quickly after the first, the OS hasn't finished flushing its buffers yet and you get the error. 

What you need is a way to ensure that the data is written and the file is closed before the second output tool starts.

The combination of the Summary and Union tool serve to add enough of a delay into the process that the OS can flush it's buffers before the second output tools tries to open the file.  The summary tools adds a single record with a single column to the second data stream.  The Select and Filter tools remove this column and record before passing the data on to the rest of the process. 

Dan

[sparksun]

I create a workflow and publish it to my Gallery,but everthing goes well.

You may create a new one to see what will happen.

[EdP]

Additional suggestions can be found here:  https://knowledge.alteryx.com/index/s/article/Error-writing-Excel-file-to-Shared-Drive