Calling all Racers for the Alteryx Grand Prix! It's time to rev your engines and race to the stage at Inspire! Sign up here.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer 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

17 REPLIES 17
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!

Labels