How to Delete Files with Alteryx – Cleaning Up my Computer Part 1

***WARNING THIS POST IS GEARED TOWARDS DELETING FILES WITH COMMAND PROMPT, PLAY WITH CARE***

My downloads folder is a mess. Its full of random files. Data files, workbooks from Tableau Public, images, gifs, pdfs, application installers, you name it and its there. And nearly all of it is single use files I have no need for anymore. My documents folder is a mess too. My big projects are well grouped together but smaller flights of fancy are a disorganised jumble of data, workflows, workbooks and screenshots that I have no need for any more. But now I’ve got an Alteryx that helps me keep folders in check and purge old unwanted files.

The base of the workflow is on the Alteryx Gallery here but will need some re-configuring to set up for your own use:
https://gallery.alteryx.com/#!app/Delete-Old-Files/5989dc85f499c716ec3725a7

The gist of the workflow is to utilise Alteryx’s Directory tool to get the last used date of every file in a folder and to then call the Windows Command Prompt to delete old files.

Step 1: Directory Input

This gives us a list of all files in the folder and the full filepath for each. If we want to look for all files types set the file spec to * and if we want to search within folders we need to tick “Include SubDirectories”.

From this, these are the important fields that help us determine old files:

Step 2: Filter to Old Files

Now we need to use a filter tool to keep only files used, or written since a given date. In my example below with, it keeps files used or written after the 1st of January this year and I take through all of the old files from the False path of the filter. You can configure this formula however you see fit.

DateTimeFormat(MAX([LastWriteTime],[LastAccessTime]),"%Y-%m-%d") >= "2017-01-01"

Step 3: Wrap Command Prompt Code Around the Files to Delete

This code below in a Formula tool will allow command prompt to delete a file in a given file path.

'del /f ' +'"' + [FullPath] +'"'

Step 4: Concatenate Every Row of Your Current List into a Text Blob

For this stage we want to use a Summarize tool and concatenate the field we created with the string formula using \n as our concatenation separator.

This concatenates every database row into a text blob with the \n creating a new line in the blob for each row.

Step 5: Write and Run this Code as a Batch File

To write batch files with Alteryx, we need to trick it into thinking it is writing a csv file but replace the extension with .bat

Below is how we want to configure the Run Command tool. Writing a .bat file to Output and then running it immediately. Whatever file path we write our bat file to will have to be the same filepath that we enter in the Command field.

Within the Output configuration, we need to set it up like this:

File format needs to be *.csv but in the write location we need to replace “.csv” with “.bat”. Both file formats are just text files which are interpreted differently by a computer so we can write as one and change the file format and Windows will interpret it differently.

To stop the csv chucking in any accidental commas,  we need  to change the delimiter to \0.

And finally we need to untick “First Row Contains Field Names” so that the first row in the batch file isn’t “Field_1” or whatever and we only get the body of the text.

and there we go, running this workflow in my downloads purged any file I’ve not used this year.

Thanks for reading 🙂

Ian Baldwin

London, UK