Run cmd file, wait 15 minutes and then input data

[Saarek]

I have an excel file that needs to be refreshed before the data is to be brought into the Alteryx Workflow.

I have created a macro within the excel file that refreshes the data when the file is opened, saves the changes and closes the file. A .cmd file launches the excel file that is held on the network share.

What I need Alteryx to do is run the .cmd file, wait 15 minutes and then input the data for the workflow.

I cannot find anyway to have Alteryx run this type of combination, the run command tool insists on having inputs and outputs as opposed to just running a command and the wait/parallel block do not let me set a time frame before the module runs.

I'm sure something as simple as my requirements must be possible, just can't work out how to do it with the tool set.

[andre347]

There is a 'Wait a Second' Macro created by Inviso. It's native in v11 and you can find it by using the search box on the top right. Have a look here for more information: http://inviso.dk/2016/waitasecond-alteryx-waiting-tool

[PaulN]

Hi @Saarek,

You may be interested by Alteryx Throttle tool (https://community.alteryx.com/t5/Setup-Configuration/Is-there-a-Wait-macro/m-p/246#M75) or the "Wait Macro.yxmc" mentioned in https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Wait-Pause-Between-Processing-Records/ta-p/3... and by @andre347.

Macro WaitASecond from Crew Macros (http://www.chaosreignswithin.com/) could also be an option.

Paul NOIREL

Customer Support Engineer

[MarqueeCrew · Accepted solution]

@Saarek,

I understand your ask as this.  Upon execution of a workflow, you want to run a DOS command line instruction.  Then wait 15 minutes before reading the input file.

Your challenge is to read the excel file after waiting 15 minutes.  If you place a standard INPUT tool on the canvas, you won't be able to control the read.  You will need to execute your command prompt (either as an EVENT or as a RUN COMMAND tool).  The "WAIT" macro(s) can be used to help you wait and I agree with @PaulN & @andre347 that you'll want to use them.  I would add to their suggestion the use of a DYNAMIC INPUT (https://help.alteryx.com/11.0/index.htm#DynamicInput.htm).  

Spoiler

Events

The Events tab contains options for configuring email notifications or run commands for specific events that occur when a workflow is run. This tab is not available for batch, iterative, and location optimizer macros.

Enable Events Checkbox: Select to enable events to trigger an email or run command.

Add a Run Command event

1. Click Add and click Run Command.
2. In the Edit Event window, click Run Event When and select when the event will run relative to the workflow event:
   • Before Run: Runs the command before the workflow is run.
   • After Run: Runs the command after the workflow is run.
   • After Run with Errors: Runs the command after the workflow was run, but if the workflow ran with errors.
   • After Run Without Errors: Runs the command after the workflow was run, but only if the workflow ran without error.
   • Disabled: Turns the event off.
3. For Command, click Browse to select the external command to run when the event occurs. Commands programs can be used to generate read results based on the write source. For more reasons to run an external command, see Run Command Tool.
4. In Command Arguments, type a command argument. Some commands require one or more arguments. For examples of command arguments, see Run Command Tool.
5. (Optional) For Working Directory, click Browse to select a different directory. By default, the directory is the same location where the workflow is saved.
6. In Timeout, type or click to set the time it seconds to execute the run command. The run command will stop if it is not complete in time.
7. Click OK to save the event.

As an input to the dynamic input, I would place the WAIT macro.  Before the WAIT macro I would place the PATH instructions for finding the EXCEL document (you can use a Text INPUT tool).

As for the RUN COMMAND tool, I generally create a dummy input file (or text input) with a field name of DUMMY and a row of DUMMY to provide a read into the command tool.  I don't think that you'll need this if you create an event (those instructions are in the spoiler above).

Much luck,

Mark

[Saarek]

@MarqueeCrew and everyone else, thank you very much for your help so far. I'm still new to Alteryx and would never have thought of looking at the Events in the workflow configuration.

The excel file is now launching automatically and refreshing the data as required, however I am still stuck as to the Dynamic input.

The text input has advised that it has a 1000 row limit, the wait tool is working, the Dynamic Input has foud the file but keeps advising that the Field names must not be blank. Reading through the tool info I don't yet see how I can make it just bring in all records like the regular Input Data tool?

[Saarek]

Thanks once again for your help everyone, what I have done is added the event to run before the workflow and set the command line to not close until the refresh is complete and excel has closed itself. Then the workflow runs as needed.

Would still be good to have a tool that delays an input, but this work around does the trick.

[Bhavika]

@Saarek 

Hey.. i'm in a similar situation where in i'm opening an excel, running a macro (using vbscript and it through .bat file ) before i start my workflow. But, the workflow stats before my excel is closed by the command prompt (.bat file). 

Could you please share how did you set the command line not to close until Excel is not closed. I'm real need of this and have been struggling a lot.

Thanks.