I am unable to make any edits to one of my existing workflows after updating to 10.1. I no longer have the file format option of excel legacy so i can not set output option to to delete data and append, any existing tool that was set up with this format errors out when I try to run and if i change it to the overwrite option it errors as well. is there a previous version i can revert to that will bring back the legacy option in my output tools?
The Legacy driver we use for Excel is supplied by Microsoft. It was automatically installed with Office 2010, but they stopped including it, so you have to install it manually if your machine has a newer version of Office. Updating Alteryx alone should not have removed this driver.
The error I was getting with the new xlsx driver was that the file is currently in use. The workflow is updating 3 sheets in the same excel file and the first tool will update the corresponding sheet correctly but when it gets to the next two output tools they error and say the file is in use. the file is an .xlsm macro enabled file if that matters, (which is why we had to use legacy delete and append when the workflow was created)
You can get around that error by using a Block Until Done tool in front of your 3 outputs that write to the same file. You can put it anywhere upstream that makes sense, but for efficiency should go as close to the output tools as possible.
The Block Until Done tool has 3 outputs, so make sure you connect the 3 streams to the different outputs of the Tool.
Why do you need to do this with the new driver? Because the Legacy driver will happily write to the "open" Excel file while we will not.
Microsoft is using an additional library that allows Excel files to be written to if open by a Microsoft product (for example, Excel itself or the Access driver).
So, if the file is currently open, the Access Driver will use the additional runtime library to do the write into the open Excel file.
The thing to understand is that some functionality differs between the two (Access Driver vs Runtime Library), so you can actually get different data out depending on whether the file was open at the time or not.
The differences that we've noted as well as reported by users effects datatypes that can be rounded. The Access Driver has different rules than the runtime library, so the resulting data can potentially be different.
If an Excel file is open in Excel, or by the Access Driver, then Alteryx will not be able to write to it. At the file system level, Excel and the Access Driver both open with a write lock.
Similarly, when we open a file for write, we also open it with a write lock which will not allow another process, such as another stream in Alteryx, to write to the same file.
Hence the Block Until Done Tool - which serializes downstream processing. Each sheet will write in turn and the next won't start until the previous stream has finished and closed the file.
Is there a driver out there that will work with Office365 32 bit? That's my current version of Office I'm on and would love to have the "Delete Data & Append" feature available on my .xlsx outputs. I just upgraded to Alteryx v2019.3.