Using Alteryx to Get Around Files Locked for Editing in Windows

Ever been in a position where you have a scheduled Alteryx flow which points at a shared Excel file and someone went on holiday with it left open?

To access the contents of the locked file, we can use the following heuristic method

1. Copy the files somewhere
2. Access those copied files

To copy the files, we will use the Run Command tool, since the blob input tool runs into the same "locked for editing" issue.

I will demo copying Excel files from two locations below.

There are two more xlsx files in the subfolder "Other file area that is in use".

Setting up the file locations and names

• I use the Directory tool to get all the files in the directories (pictured above) which I want to get my xlsx files from.
• I use a filter tool to keep only the non-temporary .xlsx files

• I union the list of xlsx files I want to copy together

• I create two fields, one for the file name and one for the file extension ("dummy file.xlsx" becomes "dummy file" and ".xlsx")

Separately, I get the information for my output area, which in my case is a Temp folder

• I use a Text Input tool to get my Temp folder location
• I use a Date Time Now tool to get the current datetime for timestamp purposes on the filenames
• I format the timestamp for a filename suffix

• I append all the fields to the input filepaths we constructed

A brief interlude into cmd copy command

We can open the Command Prompt in Windows by typing cmd into the start menu and opening up the Command Prompt. From here we can run commands which achieve things in Windows which are normally completed via clicking and dragging.

To copy a file from one area to another area, you can write (and then click enter)

copy "path/to/file/filename.fileextension" "path/to/output/location/newfilename.fileextension"

Note the quotes are required to allow you to input spaces in the file path.

To run multiple commands in a row, you can type each command consecutively with single or double ampersands (&)

command1 && command2 && ...

E.g.

copy "input/path/1/..." "output/path/1/..." && copy "input/path/2/..." "output/path/2/..."

Copies the two input files to the two output paths.

Run Command tool in Alteryx

Using this knowledge of the copy command, we can run it on all our Excel files using the Run Command tool.

To use it we need to create a single row and column which contains the full command as a string inside the cell. We will then save that command as a .bat file and run it in Alteryx.

• I created the output full paths to be in the Temp subfolder I created, with the names of the original files concatenated with the current datetime

• I created individual copy commands for each of the rows (one per file) by concatenating "copy" with the input and output full paths, wrapped in quotes

• I used a Summarise Tool to concatenate all of the individual copy commands with " && " (essential the spaces are included), forming my single line of multiple copy commands

• I used the Run Command tool to save and use the command created. Note that the "Output..." settings are essential to be saved as just the cell contents with no additional information - I chose to use a csv filetype with lots of things unticked, but save the file as a .bat

• Done! Just run the flow and all the files will be copied, regardless of if they are locked for editing! You should verify that the generated .bat file (can open with notepad) has the expected command as discussed in the previous section.

Now you are free to access the file contents for any purpose - note, however, that editing these files will not change the original files as we made copies!