Batch Files in Alteryx Part 1: Creating .bat files

[DanM]

One of the many great things about Alteryx is how it can assist the user in organizing their data not only within the tool, but outside of the tool as well. In this series we will show you how to rename, move, and delete input files that you use in your Alteryx Workflows. You will create batch files with Alteryx that you will later be able to use in a workflow that runs the file. This will give you the ability to stay organized as to what you have run through the workflow.

The first one we will go over is Renaming Spreadsheets.

Renaming Files

Creating the Batch file workflows:

Tools needed in order of use: (1)Directory Input, (2)Select, (3)Formula, (4)Summarize, (5)Output Tools.

Directory Tool (1) –

1. Browse to the file or files you are looking to change

Select Tool (2) –

1. Fields you will need are Fullpath, Directory, and File Name

Formula Tool (3) – Output Fields Created: New File Name, Command, and Batch File.

1. Create a new field, New File Name (String) – Create the new name either by adding a name with quotes around it, from a field, or in like the example below, adding the runtime date of the file to the file name.
   • Sample Expression of adding the run Date from Directory Tool as the renamed file:  

     Replace(left([FileName],findstring([FileName],'.'))+' '+tostring(DateTimeNow())+'.xlsx',':','-')

   • Sample Output - $200 2016-03-04 09-00-43.xlsx
2. Create a new field, Command (String) – This is the field you want to build your command.
   • Expression:  

     'Rename '+ '"'+[FullPath]+ '"'+ ' ' + + '"'+[New File Name]+ '"'

   • Sample Output - Rename "C:\Users\johndoe\Documents\Sample Data\Command Line Test\$200.xlsx" "$200 2016-03-03 13-23-53.xlsx"
3. Create a new field, Batch File (String) – You will take the Directory field from the Directory Input and add a name of what you want the Batch File to be.
   • Expression:  

     [Directory]+'rename_files.bat'

   • Sample Output - C:\Users\JDoe\Documents\Sample Data\Command Line Test\rename_files.bat

Summarize Tool (4)–

1. Select the Command field and the action will be to concatenate using a separator as \n.
2. Select the Batch file and the action will be First.
3. You can rename the Output fields if you choose as it will change the fields to Concat_Command and First Batch File.

Output Tool (5) –

1. Browse to save file as any name you want (you will be replacing this name). You will want to use the Save as type: (.csv) and add .bat to end of the name. We want to transform that txt file to a bat file.
2. In the Delimiters Row in the Options section add \0 as the delimiter.
3. Select Take/Table Name From Field and choose Change Entire File Path.
4. Select the First_Batch file (or name you called the field) in the Field Containing File Name or Part of File Name and unselect Keep Field in Output.

Adding Batch File to Your Workflow:

The last step is to add this batch file to the Events tab in a Workflow that you want to move the file or files you have chosen in the .bat file in the Configuration window:

1. Select Add > Run Command> Run Event When> After Run>
2. Command: Browse to the bat file and click Ok.

[SamuelKr]

I've recreated this and received the following error,

Error running Event #1: The external program

"C:\Users\my.name\Desktop\test.bat" returned an error code:1: The specified image file did not contain a resource section. (1812)

Has anyone else experienced this and found a workaround? 

[DanM]

@SamuelKr please see this post with the same error https://community.alteryx.com/t5/Advanced-Analytics/Guide-to-Creating-Your-Own-Connector-Handling-Er.... If the resolution does not help, please post back to that thread for the users to assist.

[coylet]

Hello

I received this error for the command 'Command was truncated' where it cuts off part of the destination filepath along with the destination file name.  I have tried changed the type of field and the length, but still having this issue.

The wf does work when I use a short filepath - such as my C: drive - but I need to copy the file from a network location to another network location.

Any help with this would be great!

Tammy