Input open Excel files

Does anybody know of a way to input Excel files which may already be open? (i.e. viewed by a user).

Currently the only option I can think of is to write a powershell script which would create a copy of the files, and then point Alteryx at the copied files to read them in. Is there a simpler way?

Thanks

Preparation

Accepted answers

MikeB

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.

All comments

jdunkerley79

Only way I know is to copy it.

MikeB

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.

DataBlender

Thanks Mike, that's really good to know.

I understand and agree with the challenges you first mentioned. In this instance, I am reading a number files from network drives in a workflow which is scheduled to run in the middle of the night. I don't want this workflow to fail if one of those files happens to be open, and I'm happy for it to read the last saved version instead, so your suggestion works perfectly.

Thanks again

tom_montpool

I gave this a star for the suggestion to open the Excel file in Read Only mode.

db-DCA

Hi Mike,

It would be helpful if Alteryx gave users/designers the ability to decide for themselves whether reading an open file is desired. Your point regarding the potential for an open file to be changed while it's being read by an Alteryx workflow is valid. However, this decision should be made by the user. I'd like to see Alteryx add an option to the Input tool to allow reading from a file that is already open and locked for editing (e.g. Check the box to read data even if source file is open/locked. Warning: The data used in the workflow will not include any changes being made at the time the workflow is being run.)

For example, I have accounting clerks that update certain source files with current information throughout the day. My workflow accesses these files to process records from previous days. Ensuring everyone is out of these files prior to running workflows disrupts normal operations.

Danny

jason_scarlett

100% agree.

Let the user decide how to handle READING a locked file. This is a major pain when testing code against an excel file that I am modifying.

Alteryx should be able to OPEN the file in READ ONLY mode if the file is locked. Just like Excel does, with a warning.

KylieF

@jason_scarlett

Thank you for posting to the Community!

I'm sorry to hear your frustration with how Alteryx handles a locked file. If you'd like, you could definitely submit this idea to our Designer Idea Board. That's the best way to see an update to an existing functionality since it allows the Community to weigh in on the idea and our product team to gauge interest and look into adding it to the product.

ahmedalra

You can also right click on the input and click on cache and run workflow, then open the excel file. The next time you run the workflow, it wouldn't matter that the file is open.

Linas

We have found a workaround to this issue and packaged it into a macro: the idea is to copy the excel file into a temp folder (%Engine.TempFilePath%), then read the copied excel file. To copy the file we used File Copy-Move custom tool from here File Copying and Moving - Alteryx Community and to read it we used dynamic input. We also used block until done to make sure the file is copied before it is being read from the temp location.

jason_scarlett

That's the understatement of a "workaround". Alteryx Dev's, I await a leader within who can fix this.

schaferc

Just adding some further context from what I have heard from a Customer Support Engineer on this issue:

You are correct that this is a recent change that was made in our Designer builds:

In an effort to protect version integrity of a file, there was an intentional update (TDCB-3592 & TDCB-1646) that no longer allows for Excel workbooks to be open while also being input throughout a workflow starting in v2021.4.

Please let me know if you have questions here.

Best,

–Andrew
"

schaferc

I wanted to add a real-world example of what it looks like dealing with locked Excel files.

What's not captured in the image above is that I also had to:

- run my workflow

- have it fail so I could find out which users were still in their file and message them to get out

- confirm that an employee was out of office but kept their file open

- revise the workflow to point to a copy of that users file I had to create (which will later have to be renamed back to the original naming file)

- keep users out of their files when I ran into a separate workflow problem I had to solve

- during crunch time, I will sometimes need to rerun this workflow half a dozen times as managers make small adjustments (and thus coordinate with all staff to exit files). So the work I just described above can be multiplied 6x.

What did the process look like before this Excel lock problem? Managers would message their employees to save their file before a certain deadline before roll up reporting. That's it. No problem ever arose from this approach.

I hope this reveals the frustration of this Alteryx feature "enhancement". But otherwise Alteryx is a great tool!

Techie_FPA_Guy

Is there any update on this issue? I would hope there will eventually be a user setting to enable/disable workflows to input data from open Excel files. This really slows me down since I'm constantly having to close the files I'm using to run my workflow, only to immediately open them back up again. There is, I'm sure, a good reason for the default of this setting to be in the "don't allow import from open files" direction, but I would like to have the option to turn this off, even if I had to manually set this setting for each file as part of the input tool settings.

Linas

@Techie_FPA_Guy here is a macro that can read opened excel files.
Import Opened Excel - Alteryx Community

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280