Arranging columns to standardized fields

[rohashah]

I've scoured the internet to create this enhancement and to no avail...

I have source data that is always in unique column headers. Currently, I use an arrange tool to sort source data columns to standardized field names. This is a very meticulous process and looking to improve it.

Essentially, I would like to have to 2 vertical columns (one the source data and the other is the standardized field names) and be able to dynamically assign field names and spit out my source data with new column headers that I've assigned to it.

If anyone has any guidance on this, that would be very helpful - thanks!

[NicoleJohnson]

Can you provide some examples (dummy data if that's easier) of what you're starting with and what you'd like it to look like as an end result?

Also, when you say "dynamically assign names", are you wanting to map the source data fields to the standardized list with every run (which sounds more like a macro situation) or are you able to create a set "key" that lists all possible unique column header names and the standardized names they should be switched to? If the second case is the scenario, then you should be able to use a Transpose tool + Find & Replace + Cross-Tab to get to that result (can provide example if needed/if this is what you're looking to do)...

Let us know! Thanks!

NJ

[MarqueeCrew]

I suggest posting some example before and after data and you should get a prompt solution or many.

Cheers,
Mark

[gnans19]

 "Dynamic Rename"?

or

Transpose ,Join and Cross tab

[rohashah]

Hello all, thanks for the thoughts!

To clarify, I attached a workflow as an example.

Nicole - Unfortunately a find and replace would not work b/c the source field names will almost always be different for each project and require some manual effort to see if it's a good fit for a standardized column. I'm thinking a macro is the only option - the mapping would only need be completed once per project.

Thanks in advance for the help. 

[NicoleJohnson · Accepted solution]

Okay, try this!

Attached a macro that seems deceptively over-simple, but I think it will do exactly what you're looking for if I correctly understand your objectives... basically the macro consists of an input and an output only, and the input is configured to have the Text Input showing the standard field names you want to use (so you would want to configure this in the macro with your desired field names, or else switch it to point to a file that contains the field names listed in a single row). Then the input is configured to check the box that says "Show Field Map", which will require you to map/review the columns coming into the macro to match them to the columns set up in the macro.

So by saving this macro with your standard field names, you can include it as a tool in your workflow, and you'll only need to map the fields one time based on the fields in your individual projects. Note that it might try to guess the mapping based on letters/words that it recognizes as matches or similar items, but you still have the opportunity to review them. Once it runs through the macro, the results will be using the standard field names, which you can then feed into your arrange tool. Check out the example workflow I attached with a bunch of random names that I mapped to the ABCD standard field names.

I hope this works for you!! Let me know if it needs tweaking or didn't quite hit the mark. :)

Cheers!

NJ

[rohashah]

Nicole - This is AMAZING! I didn't even think this was possible and I can't believe the macro is so simple. 

Quick question: Is it possible to select 'none' in the front end macro if I don't have data for a standardized column? because I noticed that all fields/questions are required to be filled out. Let me know if this doesn't make sense. 

Again, thanks so much for getting me here. 

[NicoleJohnson]

Glad it worked!! I was totally surprised it turned out that simple too haha.

Not 100% sure about the missing fields question (and there's probably some tricky way around this), but I believe the macro as it's currently set up will require something to be mapped to all fields. However, if you know you're short a few columns, you could always add the "missing" columns to the end of your data with a formula tool before you get to the macro step. i.e. add "BlankField1", "BlankField2", etc. columns and set them to Null() values at the end of your data, then map those dummy fields to the missing fields in the macro field map.

Not an ideal solution, but it might do the trick if you're only having to do it occasionally for those projects that are missing fields? :)

NJ

[NicoleJohnson · Accepted solution]

Here, this might work, actually... another macro that will prompt you for the number of required fields (which you can set to 11 or 20 or however many fields will be needed for the mapping)... then just add this tool before the tool that shows the field mapping, and it will create the blank fields for you. :)

NJ

[rohashah]

No worries, I'll put some more thought into it! 

The missing fields macro works but I had to drop in a unique b/c after transposing, the field names were getting duplicated so it became a ridiculous count. 

I'll definitely use this macro though and then the simple mapping macro you built. My next step will be to figure out how to get the macro to retain the fields w/o resetting itself when I click out of it. 

Thanks so much, Nicole! Appreciate all the help.