Open Excel Files That Are Already in Use

[AngelaO]

Question

How do you Input Excel files which may already be open? (i.e. viewed by a user).

Answer

The reason we can't read an Excel file when it is open is because Excel puts a write lock on the file. 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, the decision was made 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, trythe Access driver below:

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.

[Boneckrh19]

I would have really appreciated this knowledge to be listed somewhere in Alteryx either as a startup tip or as a detected-event warning because I had no idea that viewing a file would change the input to Alteryx. I just built a very long and complex workflow around the fact that the inputs would look the way they did and then suddenly it completely didn't work. This was extremely frustrating because I changed nothing in the source files or the workflow, and if it wasn't for a coworker who was testing it who just happened upon the differences when the file was open or closed I would still be completely in the dark.

It would just be nice to know from the start!

As a helpful note, it is date and currency/accounting fields that change, for anybody else trying to troubleshoot this problem.

My currency/accounting workaround is to have a regex that replaces the "$" characters (which has to be put in as \$) and the "," characters with nothing so that those fields can be converted to doubles.

(tags, alteryx dates changing, alteryx currency changing, alteryx accounting fields changing, alteryx dates different, alteryx currency different, alteryx accounting fields different)

[dws1834]

I'm trying to use a directory tool with a dynamic input tool to input multiple excel XLMS files (There are 47 files in total).  The format is similar in all the files but I've created a batch macro to read each file individually.  The model is working fine except I'm getting an error on some of the files when alteryx tries to bring them in because they are being used by another person.  I need a way to bypass this error and bring the data in even if someone else is using the file.  Is there a way to do this in alteryx?

[AnthonyMeek]

Is there a workaround to handle this error when it occurs with the legacy excel driver. Following the installation of Access driver I received the below error.

Start: Designer x64: Started running at 05/13/2019 16:37:40
Error: Input Data (1): Error opening connect string: Microsoft Access Database Engine: The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.\3051 = -67568648
End: Designer x64: Finished running in 6.7 seconds with 1 error