Drop columns after Dynamic Rename

[StephenJPSheehan]

Hello,

I have a number of files with various headers that I am using the Dynamic Rename tool to code into standard headers based on a header dictionary we have. However, what we want to do is drop any columns that don't match when running the tool. 

To give more information, the Dynamic Rename 'Rename Mode' is 'Take Field Names from Right Input Rows'. Essentially, all of the data in the header dictionary is stored within rows of data rather than having them as field headers themselves, meaning a join doesn't work for this scenario (unless I have missed something!)

Finally, the number of columns that match and don't match will vary each time we run data through the tool, so would like something dynamic as a solution.

Appreciate any advice you may have in resolving!

Thanks

[jdminton]

I would actually do this step after the rename. You can add a Union tool after the dynamic rename to union the headers with only the common fields.

 

 

[apathetichell]

If there is a prefix you can add to either the matched or unmatched rows - you can use dynamic select after dynamic rename to drop the unmatched columns using regex_match and your dynamic select tool in formula mode.

[StephenJPSheehan]

Hi @jdminton.

Thank you for your reply. The extra text file after the dynamic rename - what is the source of this file? Struggling to see how I would use this part in the workflow. 

Appreciate your advice.

[StephenJPSheehan]

Hi @apathetichell 

With adding a prefix to the unmatched columns, what is the best way to do that? 

[apathetichell]

dynamic rename before your dynamic rename to your original data stream. use a formula:

"org_"+[_CurrentField_]

dynamic rename before changed fields:

"new_"+[_CurrentField_]

dynamic select after current dyanmic rename

regex_match([Name],"new_.*")

dynamic rename after dynamic select

replace([_CurrentField_],"new_","")

[cjaneczko]

Do you have any sample data you can include. I think you might be able to accomplish this with a Field Info Tool connected to the data files. Then Join the Field info tool to your Data Dictionary. Then cross tab the data and union it back to your dataset to only include the matches. 

[jdminton]

@StephenJPSheehan that is a text input I created to represent the fields that are acceptable. Since you didn't include any data, I had to make something up. Notice in the dynamic rename that the Field1 is being renamed to "First" and Field2 is being renamed to "Second". There is no rename for Field3. Since the union tool only brings in common fields, it ignores the Field3. The "Blank" is so that the union tool will read it in (which is why the filter removes the "Blank" after.