Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Output legacy Excel files

BFritter
6 - Meteoroid

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?

6 REPLIES 6
MikeB
Alteryx
Alteryx

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.

 

You can easily re-install it from here:

https://www.microsoft.com/en-us/download/details.aspx?id=13255

 

NOTE: you should match the driver with your version of Office (32bit driver for 32 bit Office - or 64bit driver if you have 64bit Office)

 

Once you install the driver you will get the Legacy option back.

 

I'm curious to know what error you are getting with the new XLSX driver using Overwrite Sheet because that should work for you as well and may be preferable.

 

 

BFritter
6 - Meteoroid

Thank you for the quick reponse, we just updated our microsoft office so this was definitely the issue and this was an easy fix. I will pass this on to the rest of my team.

 

Thank you,

Brian

BFritter
6 - Meteoroid

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)



MikeB
Alteryx
Alteryx

Ah, ok.

 

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.

 

Hopefully this information is useful.

 

 

JPulley
5 - Atom

Hi MikeB,

 

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.

 

Thanks for any help!

Jengel629
5 - Atom

I am also having this problem and would love to have the "Delete Data & Append" option.

Labels