How to run multiple files through one workflow and result in multiple outputs

[Matt_Hancock]

Hi,

I have a workflow that creates 25 separate files, one for each distribution center.  I created an additional workflow that takes one of the files for one distribution center, applies a series of formulas to add additional data to the file, and then output the file in excel.

I need to dynamically input all 25 files, run each through the workflow, and then create a separate output for each of the 25 distribution center files.  I may have found a solution to the dynamic input, but I haven't tried it yet and I don't have a solution for the dynamic output.

Any thoughts?

Thank you for any help you can provide.

Matt

[benakesh]

Hi @Matt_Hancock ,

The formula tool can create output name  . This name can be used  output  option : 'take file/table from field'.

[PeterA1]

@Matt_Hancock 

If outputting to xlsx you can do so right in the output data tool by selecting to create new sheet, and take file/table name from a field which would be the distribution center field. 

As seen here: 

I ran the workflow and it created 10 different excel files all named based on a field I created

[CharlieS]

The key to the unique file output is the "Take File/Table Name From Field" function in the Output/Render tools. Use a Formula tool to write unique output paths and that feature will allow the same Output tool to write multiple files from the same workflow. 

This can be used to write different files, or in the case of Excel, multiple sheets in the same Excel file.

[NicholasM · Accepted solution]

Hey @Matt_Hancock ,

Here is my suggestion. 

When it comes to imputing multiple files dynamically. I would advise looking at using a wildcard symbol (*) in the input data tool. For more information on using the wildcard symbol this post is extremely helpful. 

If you are using the wildcard to input all the files at once, there is useful option in the configuration of the input tool that will create a new column containing the file name. 

@benakesh is on the right track when it comes to creating multiple output files. You can configure the output to create a new file based off the "File name" field created from the input. 

Hope that helps, let me know if you have questions!

[fharper]

You have good responses educating you on dynamic output by getting name from a field...learn the nuances before setting on the exact method...I tend to use a formula to define the full path name as that works for any file including multiple tab xlsx files.  also see in mind that if writing to xlsx there is a limit of ~1,048k rows so if you think you might exceed the row limit you need to add logic to spot the write to multiple tabs a=that are named in a way the makes sense to you or your user...output tool has a limit option but that will produce a whole new file.

On input..if the schema is the same then a wild card on an input tool works.  or you can feed a list of file names into a dynamic input.  

BUT...if the schema of the input files vary you need to make yourself a macro to read basically any input.  It is not hard but there are a few gotchas and you can make it more complex to really handle more/any input or keep it simple if you want it to work with one format and one start row...I and others have explained this in other posts so a simple search will get you good examples.

That said I add this last item...if one for produces the many outputs only to be read by the other maybe you should merge them into one flow and not write read unnecessarily and avoid the whole input issue in the second flow.

[Matt_Hancock]

Thanks for the response.  I saw a post once on how to use the wildcard with the input tool, but I cannot find that article again and I'm unsure of how to use it. 

For example, my input files are named DC 7, DC 15, DC 18 etc.  I believe there is a way to include 'DC ' and then use the * in some format to incorporate each DC number.  Can anyone provide an example of how this would be written?

Thanks,

Matt

[fharper]

It’s very simple....path/path/path/DC*

That will cause the input tool to read all files matching the pattern in the path. If you have a mix of Yxdb and csv and or other file types starting with DC in the folder u will have issues with schema as I mentioned. You can do DC*.csv to refine the pattern definition and exclude other file types that otherwise match

[Matt_Hancock]

Got it thanks!

I'll give that a try!

[Matt_Hancock]

Does the wildcard only work with .csv or should it work with .xlsx?  I can't get it to work, but my files are .xlsx.