Can Alteryx Input open Excel files

[AndrewL]

Question

Why can't Alteryx read in Excel files that are open?

Answer

The best answer to this question comes from @MikeBas posted as a response in the discussion boards. His answer in full is shown below:

The reason we can't read an Excel file when it is open is because Excel puts a write lock on the file. We also decided that reading an open file has challenges like what happens if the file is modified and saved while we are reading it? To avoid those and other edge cases, we made the decision to limit reading and writing Excel files to those that are not currently locked. You can open your file in Excel for "Read Only" and then Alteryx can read the file without any problem.

That said, if you install the Microsoft Access 2010 driver you will be able to use that driver which can read and write to open Excel files. In Alteryx it's called the Excel Legacy driver. However, be aware that if you use the Access driver you may get different results (rounding) depending on whether the file is currently open in Excel or not. The Access driver uses a 2nd library that allows reading and writing to an open file (open with a write lock). The 2nd driver does rounding slightly different from the main driver that is used if the file is not open.

If those limitations are not a problem for you, I'd suggest trying the Access driver which you can download here:

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

If you have 32bit Office installed, it's suggested that you go with the 32bit version of this driver. If 64bit Office, then go with the 64bit version.

Then in Alteryx choose the Excel Legacy format (this format will appear once the driver is installed).

NOTE: This driver was delivered with Office 2010, but is missing from later versions of Office. So if you are running a later version of Office, you'll need to install it yourself.

Hope this helps.

[BobL]

Thanks Mike. For those who decide to install the Access driver, don't assume you have 64-bit Office because you have a 64-bit OS. Many IT departments install the 32-bit Office on 64-bit OSs for better compatability with Office plug-ins.

Bob

[SeanAdams]

This is a very common question - how to deal with locked excel files - thanks @AndrewL 

[PoojaPrasadUTD]

Thank you @AndrewL for this post.

I need to use an xlsb file as a source in Alteryx and it is used at various places so I am getting 

Error: Input Data (1): Error opening the file "<filelocation on driver>\file.xlsb": The process cannot access the file because it is being used by another process. (32)

So, I am trying to install the Microsoft Access 2010 driver using the above link. But the link has AccessDatabaseEngine.exe file. Do I need to install the Engine file in order to use the Microsoft Access 2010 driver?

[lepome]

@PoojaPrasadUTD 

It is confusing that Microsoft calls the Microsoft Access Database Engine 2010 Redistributable 32-bit installation file AccessDatabaseEngine.exe, but they are one and the same.

There are sometimes issues with .xlsb files beyond just that particular driver.  Please also consider How To Input Excel Files That Have Encoding Issues for tips on how to automatically convert one Excel workbook type to another.  

Also if the root of the issue is that you are reading and writing to the same file multiple times in a single workflow, you might want to try Block Until Done tools or even better, using batch macros to control the order of execution of various parts of your workflow.