Figuring out how to run Powershell scripts using Alteryx has been a very harrowing experience for me. I had used ‘Run command’ tasks before but I remember even then it was quite a struggle to get it up and running.

The help files from Alteryx with regards to this task is abysmal. It gives an explanation of each task within the command but doesn’t really tell you how to go about doing it step by step. The nearest help from Alteryx forum that I could get is this. Here is my attempt to make it easier for folks who find themselves in similar situation.

Task Description
I need to create a sub-folder under multiple folders. This ‘sub-folder’ name is dynamically determined during run-time depending on the input file name to the workflow.

For example, say my input file is – movies_released_20180801.csv. I have a set of folders under the parent directory – Movies as shown below –

I need to create a folder called ‘20180801’ under each of the sub-folders, if it doesn’t exist already. As each month passes, new sub-folders would need to be created.

Solution
My first step for the solution is to create a powershell script that can perform the sub-folder creation. Here is my script –

#Get Parent directory
$root = "C:\Users\kvankada\Documents\Personal\Learning\output\Movies"

#Get all directories present in the parent folder
$folders = Get-ChildItem -Path $root -Directory

#Iterate through each item within the list of parent folder
foreach($folder in $folders)
{
  #Get the subfolder full path
  $Subfolder = $folder.FullName + "\20180801"

   #Check to see if the folder exists
   if(! (Test-Path -Path $Subfolder))
   {
      #If it does not exist, create new sub-folder under each item
      New-Item -ItemType directory -Path $Subfolder
   }
}

The root folder of the above script file comes from the Alteryx workflow. The next steps now starts looking crazy but bear with me. It will all make sense. Drag in a ‘Input Task’ with following two columns –

Path – C:\Users\kvankada\Documents\Personal\Learning\output\create_subfolders.ps1
Input – C:\Users\kvankada\Documents\Personal\Learning\input\movies_released_20180801.csv

The Path should be a valid path and accessible to the user. It can be a UNC path as well. Drag in a ‘Formula’. Create two new columns Subfolder and Powershell_Script with following values as shown below –

As can be seen the new column Powershell_Script is nothing but the entirety of powershell script with ‘Subfolder’ being dynamically sent. Drag in a ‘Select’ task. De-select all the fields excepting ‘Path’ and ‘Powershell_Script’. Drag in a ‘Run Command’ task. Click on ‘Output’ under ‘Write Source [Optional]’ in the configuration window as shown below –

In the ‘Write to File or Database’ file, click on the drop-down and select ‘File’. A ‘Save a data file’ window pops up. Here chose any location and save the file as say ‘temp_powershell.csv’. I have chosen a ‘temp’ folder as my file location. In the ‘Alterx Designer’ the following changes still needs to be done against following settings –
Delimiters – \0
First Row Contains Field Names – Unchecked
Quote Output Fields – Never
Take File/Table Name From Field – Tick the checkbox. Chose the option as ‘Change Entire File Path’
Field Containing File Name or Part of File Name – Path
Keep Field in Output – Unchecked

The final dialog would look like as shown below. Changes have been highlighted. Click on ‘OK’ post completion –

The reason we need to do this is because Alteryx doesn’t provide a way to save the output to Powershell script file. We need to get the content of Powershell first saved as CSV and then the whole file being renamed via our ‘Path’ value that we have provided in the workflow.  It’s a roundabout way of doing things.

Continuing the ‘Run Command’ Task configuration and set them as shown below.
Pay particular attention to double quotes in the ‘Command Arguments [Optional]’ section. It is absolutely necessary.-

That’s it. Now you are set to run. There is no need to configure the output of ‘Run Command’ task. If you do need to use, then ‘Read Results’ path needs to be configured. I might do a continuation post later on for that. For now you can ignore the need for additional output configuration. Here is how the final workflow looks like –

I ran the workflow and here is my output –

For final workflow in the ‘Run Command’ task, you would need to set ‘Run Minimized’ and ‘Run Silent’ check boxes to have smoother run. As you are testing out, it would be important for debugging purpose to leave them unchecked. In that manner you would see if you have any errors popping out.

That’s it. That’s how it is done in Alteryx. It took me quite a lot of tinkering and experimenting to get the desired result. Hope this post has helped in solving your own problems while getting Powershell scripts run via Alteryx.