Problem – I need to regularly update a file on our server that is based on a foreign server

Recently with a client, I needed a way to update an Access database daily that was stored on a different server to our own. Essentially, I wanted to automate the operation of regularly copy and pasting the most recent version of a file. To do this, I came across the technique of writing a Batch file that uses Windows ‘Run Command’ to achieve the required result.

The code you need to enter into Notepad to achieve the above is simple:

XCOPY “[Enter source file location between quote marks]” “[Enter destination FOLDER location between quote marks]” /y

Save this as a .bat file

XCOPY is the command – i.e. copy file from here to here

/y forces the overwriting of a file – i.e. if there is already a file existing with that name it will overwrite it regardless of what it finds in the destination folder

Optional – at the end of the notepad script, you can add in an “Exit 0” – which I believe will output a 0 or 1 depending on workflow success

Next problem – Alteryx’s Run Command Tool

Writing the batch file itself was really easy; however, I hit a mental brick wall when trying to command Alteryx to run the same file. This, as it turns out, was due to the ‘Run Command’ tool being very easy to misinterpret.

Here – then – is the solution that took me the best part of 3 hours and several desperate requests to colleagues within TIL to finally end up at:

• Firstly – set your workflow up like the below. So, ‘Text Input’ then ‘Run Command’ and finally a ‘Browse’

• Next – using the Syntax above – enter your .bat script into the ‘Text Input’ tool

• In the ‘Run Command’ options – set the input as a ‘Dummy’ text file (if you added in the optional content above, this is where you will see that 0 or 1 output – otherwise this will be blank)
• Also, tick both check boxes for ‘Run Silent’ and ‘Run Minimized’ – unless you want your .bat file to execute all singing all dancing

• Next – add the .bat file location – NOTE: Ensure the .bat is empty before clicking ‘run’ – our text input will add the code required to run the .bat

• Add the ‘Write Source’ as the .bat location also – paying attention to change Option 5 to ‘Never’ and setting the file type to .csv

• Final note – it’s always good practice to test your ‘Scheduled Alteryx Workflow’ once created!

Hey presto – your .bat should execute hitch-free!

In a nutshell, the ‘Run Command’ adds the contents of the ‘Text Input’ to the .bat, which it then executes.

As a further (and much easier) option to schedule an event – although, I don’t think it works (or hasn’t for me) with Alteryx Scheduler – you can access the ‘Events’ section of the ‘Canvas’ options menu. From here, you can simply add a .bat file to run before your workflow (or after) as in my case.

I hope the above was helpful – it would have been for me!

NB. Credit to Chris Love and JMac for all their assistance!