Dynamic Column Select tool based upon Expected Names

[bsharbo]

I have a procses where I need to produce multiple Excel file outputs that come from a Database Table.

The table has a category column which has multiple values such as "spoc" and "Mers".  I want to split out the files by this category value (one file for Spoc, one for Mers, etc).  I am able to do this with a batch macro.

HOWEVER: The spoc file should only contain 4 of the fields from the data-table (which has 10 fields) and the MERS file should contain 7 of the 10 fields.  This information on which file should contain which columns is found in another data-base mapping table.  It looks like follows.

Mapping Table:
Group Category   Columns to Output:
MERS     LOANID
MERS     BORROWERNAME
MERS     BORROWERAGE
MERS     BORROWERADDRESS
MERS     PAYMENTAMOUNT
MERS     PAYMENTDATE
MERS     SERVICERNAME
SPOC     LOANID
SPOC     CALLPERIOD
SPOC     AGENTNAME
SPOC     CALLCENTERNAME


Is there a way that I can take an input stream (a read from the entire table) and then select the 7 columns from MERS and the 4 columns from Spoc dynamically without having to manually manipulate the select tool for each grouping.  My actual table has 1000+ groupings and having to create a select tool for each is just not a realistic expectation.

Let me know if my question isn't clear enough! thanks!

[Ned · Accepted solution]

You could do it with a combination of the DynamicRename and the DynamicSelect tools.

Using you input data, create new names named "Keep-" + [FieldName].  Then use the DynamicRename tool with the mode "Take field names from right input rows" to rename your fields.

And then use the DynamicSelect in formula mode with a formula:

Left([Name],5)=="Keep-"

And finally use another Dynamic rename to get rid of the Keep- in formula mode with something like:

SubString([_CurrentField_], 5)

This would all be easy to button up as a macro with 2 inputs.