Directory data input to Dynamic input where files have different number of columns

[Bigmonki]

Hi All,

I need to collate data from Excel files extracted from multiple markets, this data then gets compared to a Baseline data set.

My workflow starts with a Directory input, then a filter to pick the files required and then a Dynamic input to collate all the data and append with a file name. The challenge, not all of the countries are on the same version of software which means there are varying numbers of columns. This leads to those files that don't match the template being excluded from the input!

What is consistent are the Key fields for the data validation. Is there a way of of finding the files then selecting just the required columns for the dynamic input?

I've given examples of the columns in the files and the workflow I am using.

Thanks in advance for any help.

[RWvanLeeuwen · Accepted solution]

Hi @Bigmonki ,

I would suggest using a batch macro that allows you to output all fields and work from there.

Best,

Roland

Infotopics - Benelux partner of the year

Empowering business through analytics. See, Understand, Act!

[Bigmonki]

Hi, please forgive me, but my understanding of macros is limited.

The first block , the control parameter, how do I tell it where the data is and that it needs to bring in multiple files?

My file names are PE.NL.expen, CL.NL.expen, and ET.NL.expen, in all cases the worksheet within the workbook is "expen".

[Bigmonki]

Hey,

I think I got it to work, just trying things out to validate the output, but it has at least given me an output now. Thank you