Output a file and re-input it within the same workflow

[Ozzie]

Sometimes clients have asked how they can re-input the same excel file that they have just outputted into the same workflow. Normally, their gut instinct would be to open up a new workflow and start fresh with the updated file. However, this can be a bit cumbersome especially if they want to do this multiple times or for those wanting to do some sort of logging process in an app. Luckily, there is a quick and easy trick for this.

Attached to this article is a workflow that I go through below...

Now the initial step to this might be different depending whether or not you already have a file that you want to bring in or not. If you are usingjust a regular Input tool, make sure you obtain the file path by selecting “Full Path” in the “Output File Name as Field” drop down. If you are writing in a text input make sure you have a field specifying the full path. This will be the same file path where we will output the data and bring it back in. The filepaths must be consistent throughout the workflow.

After your data blending and data preparation is complete and you are ready to output the file, place a “Block Until Done” tool at the end of your stream. Make sure your first output is connected to an “Output” tool with your specified settings with the proper file path. Next, place a “Dynamic Input” tool and make sure you are reading the field with the file path and change the action dropdown to “Change Entire File Path”. For the “Input Data Source Template “ box, I just reference the same file that I am going to write to.

And that is how yououtput a file then re-input it within the same workflow.

[bmarten]

Hi,

We are having trouble recreating this solution for multiple write commands. We are attempting to run write to two SQL tables, and delay reading data until both tables have been be written to. We are using a solution similar to that above, however it appears that the input is beginning to import the data before both write commands are completed.

Any insight as to why this may be happening and any work around solutions greatly appreciated

Thanks

[MJ]

Good stuff! Kept me from having to run another workflow to just append (union) my most recent logs with the history file. Thanks for sharing

[JW96]

@Ozzie 

I think this is exactly what I need, but I'm having some trouble getting it to fit together. In my workflow, it starts by pulling data from SAP (Denodo) & is filtered, then outputs into Excel. Then I want to take that excel output & turn it into an input. Then it can add formulas/joins then output into the same file. Your example is based on the original input being Excel & dropping the file path into a field. Is there a way I can still make mine work in one workflow?

Denodo -> Filters -> Excel1

Excel1 -> Formulas/Joins -> Excel1

[troy_mech]

I wanted to join two tables from different file called File1 and File2. After comparing two tables and joined the output need to be append in the File2. I need to use File2 with updated data as input file for another calculation. Is this possible if so could you please show me the workflow. I have tried from my end but received warning message in the Dynamic input tool as it says "Invalid Path: "0" in MakeCleanPath(). Also suggest me the valid option to select on Dynamic input tool

[lepome]

Be aware that when writing to and reading from Excel files, the file might not have updated all the cells that have formulas in them.  Please see this link for more information.  If this is a problem, you might consider doing all of the manipulations in the same environment.  (I'm biased, but I recommend Designer.)  The other consideration is that you might really need a database. (Again, I'm biased because I recognize that Excel is not a database.)

Further, the Dynamic Input tool is not as versatile as a batch macro, as described in this article.  It might be worthwhile to create such a batch macro and then reuse it, tailored as necessary.  One more option would be to use a Run Command tool, ironically, without bothering to specify a command to run.