Read multiple files with mismatched columns number , names

[nitin_ks]

I have 20.  Excel files containing mismatched number of columns and names .

I need a specific set of columns ,some files might have different names to those columns 

I need to process each file , get specific columns and generate output file for each input with datetime added to the output filename

i tried setting up the following

dir —-> macro [ macroinput(fullpath) —-> dynamicInput—-> formula —- select —> macro output]   —-> outputtool

but dynamic tool still complains that second file has mismatched columns , i thought putting in macro would do it

please help

[OTrieger]

@nitin_ks 

You need to use a batch macro to open the different files as they have different schema.

[apathetichell]

1) develop a master schema file -> this includes the column names you want, and potential matches for these column names. 

2) Directory tool -> batch macro. You cannot use dynamic input. Don't look at dynamic input. Pretend that tool does not exist. It will not help you here and will only create misery.

3) Use a dynamic rename to rename your columns based upon your master table above in your batch macro.

4) use a dynamic select in your batch macro to only pull the columns you want.

5) configure your interface designer of your batch macro to auto-union by name.

6) output the results from your batch macro.

[nitin_ks]

I am new to this , 

 What to configure in macro input tool

So flow would be 

dir —->batchmacro[[ macroinput—-dynamicrename __dynamicselect____macro output ]]]

[binuacs]

@nitin_ks how many sheets are present in each of the Excel input files? 

[nitin_ks]

Only 1, but could have different name

[binuacs]

@nitin_ks try the below batch macro, the first macro reads the input file and the sheet name, inside the second macro you need to make your changes