community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

Input open Excel files

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

Only way I know is to copy it. :(

Alteryx
Alteryx

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.

Bolide

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

Alteryx Partner

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

Atom

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

Hey Mike, 

 

I downloaded the driver and tried to "delete data & append" to an open Excel Legacy file and got the following error: "You have found a bug. Replicate, then let us know. We shall fix it soon." Is there a way around this? I'm dealing with the same issue of needing to write to open excel files. 

 

Thanks for the help!

Labels