How to output multiple excel files based on folder while using a dynamic input tool?

[Nayan9797]

Hi everyone, I am kind of stuck on a problem where I have to output excel files based on user selection of folders.

Basically if user selects folder 1 and 2 there will be two files one for folder 1 and one for folder 2. now the selections are working fine but I am getting output in a join tool and using a dynamic input tool to render it and because of which I am getting the output in one single file while what I want it completely different.

Current Setup:

• The workflow allows users to select subdirectories dynamically.
• A Dynamic Input Tool is used to read files from the selected subdirectories.
• The files inside each subdirectory are merged together.
• An Output Data Tool saves the final output.

Issues I’m Facing:

1️⃣ All data is merging into a single output file instead of creating separate files for each subdirectory.
2️⃣ The full file path (or file name) is lost when using the Dynamic Input Tool, so I can’t track which data came from which file.
3️⃣ The output files are not being saved separately, meaning everything is combined into one instead of distinct files for each subdirectory.

What I Need:

✔ Each subdirectory should have its own separate output file.
✔ Files inside a subdirectory should be merged, but not merged across different subdirectories.
✔ The original file names (or full paths) should be retained in the output.
✔ The Output Data Tool should save separate files instead of merging everything into one.

[KGT]

To answer your question, you will need a formula tool in-between, where you can dynamically create the output path for each record, and then in the output tool, use the option at the bottom "Take Path from field" or something like that.

The other thing to note though, that List Box will not update dynamically. It will always be populated with the list from the last time the workflow was saved. That list-box will be populated before any tool on the canvas executes as it's an interface tool, and they are always initialised first. 

[Nayan9797]

I tried using formula tool to do that but the issue is I want the files inside of a certain folder to be merged in to one file 

[OTrieger]

@Nayan9797 

The way how you can solve it will be with a Batch macro, as you could merge the data, then take the 2 different paths, set them as a Control Parameter, inside the macro append the path to the data and then write the data out. The Macro will keep doing the same for each of the different paths.

[KGT]

In your Dynamic Input template, make sure that you have "Output File Name as Field" selected. You can then use that in your formula to work out the directory and construct the new filename off that.

The formula if you needed a file called "Combinedfile.yxdb" in each directory would be 

• FileGetDir([FileName]) + "\CombinedFile.yxdb"

Try that and see what happens, then adjust to what you actually need.