need help for column rename and column resort

HI,

we have multiple scenarios where we have to compare either 2 files or 2 tables, we have to compare column to column and add match/mismatch to it.
The Idea is to join these 2 files/tables and map column headers and resort them
and then compare them

The challenge we are facing is its getting very tedious as the number columns is as large as 200 to 400.

The column names are not the same and also not in the same order.
we are facing issue when renaming column to match both files/tables and resorting them to be in same order.

Example –
File A : First Name
File B : First_NM

File A : SSN_N
File B : ssn_n

we see cases where names are same , lower case vs upper cases, full name vs short form names,…

I am looking for something like a reference column re sorter or reference column mapper.

Please help

You could try my Header Re-Map component. It creates a log file for backup / recovery / setting sharing between components. It is basically a single selection drop-down column name assigner, but it has some advanced setting options to do things like filter the drop-down options by category, insert or delete unassigned column names, sort column names to match order of mapping table or column name list, etc.

Here is an example workflow showing how it works. You will have to save it locally first so that it can locate the workflow for writing / reading log files.

3 Likes

Maybe extracting the col headers do a similarity search, renaming and then try to compare
br

I use a similar approach to what @Daniel_Weikert proposed before dialing it in manually in the component, which can be utilized as a “pre-map” in the second input of the component. This allows you to load predicted column name matchups before making the final manual adjustments in the component.

I used to use a similarity and rule based match logic, but now I pass both column name lists to ChatGPT and have it do the initial column name alignment table attempt for my starting point.