We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.

Alteryx Designer Desktop Discussions

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

Input open Excel files

DataBlender
11 - Bolide

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

DB

19 REPLIES 19
32bit
8 - Asteroid

I agree with opening read only as an option. It's unfortunate that this and other suggestions get delegated to a "voting" section that most people don't have the time to look at. That's why there are currently 1,600 ideas that are considered new but actually years old. 

 

You can work around this file locking issue by copying the file before opening it. Unfortunately you will have to rely on an external run command to accomplish this. The blob tool also will not open a locked file. xcopy will. You will have to issue a similar command to delete the copied file. This sounds like a good case for a macro, but I think you'll run into a challenge deleting the copy in the same workflow you're reading it in.

jason_scarlett
10 - Fireball

Agreed. I would think that this issue drives users within Alteryx nuts too ... and that should get the dev team fixing it.

ahmedalra
7 - Meteor

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.

schaferc
7 - Meteor

Hi Mike,

 

This was not an issue for me to for the past 9 months (I do have the MS Access Driver installed on my machine). However, after updating to 2022.1, I can no longer Input tool files (Excel) that are open. This is a serious issue with the generation of our ongoing workflows. Any suggestions?

 

schaferc_2-1660690015826.png

 

 

Thanks.

 

 

Linas
8 - Asteroid

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
10 - Fireball

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

schaferc
7 - Meteor

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
7 - Meteor

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

 

schaferc_1-1661451195578.png

 

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
5 - Atom

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
8 - Asteroid

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

Labels