Combine Files from different file path and merge data in one output file.

[SejalP1]

Hi,

I am getting below file paths from directory tool. 

Want to combine the data of two files and generate single output.

Example combine data of "MI_ABC_19072019_1608.xlsx" and "MI_ABC_19062019_1430.xlsx" and generate one output.

Similarly combine files "BB_PQR_XX_19072019_1553.xlsx" and "BB_PQR_XX_19062019_1535.xlsx" into one output file. Also combine "MI_PQR_19072019_1533.xlsx" and "MI_PQR_19062019_1543.xlsx" into one output file.

Folder structure is: 2019\07\ABC,

2019\07\PQR,

2019\06\ABC,

2019\06\PQR 

But inside PQR folder having two different files.

Many Thanks. 

[danilang]

Hi @SejalP1 

if your data files are all identical formats, with the same number of columns and field types, you can configure the Input tool to read subdirectories.

This will input all the .xlsx files in the target directory and all subdirectories.  

If the files have different formats, then you'll need to use a batch macro to import them all at once.  There are lots of examples on how to do this.  Here's one 

Dan

[rafalolbert]

Hi @SejalP1,

Looks like your filename will be the best candidate to tackle this - there is a designated field in the Directory tool output with filename only.

You can use regex like this:

([^\d]+)

The final workflow looks like this - following this is suggest to get a macro that will take every record (groups of files to be combined and read them from 2 inputs and assemble the results accordingly and bring back to the main workflow).