Fuzzy Match but for Column Headers w/o replacing Spaces & Special Characters w/ underscore

Question

Hello,

I need to make column headers in my table match another tables column headers.

There are about 98 column headers that I need to match in order to join the tables correctly.

Because I will need to do this process about 30 times, I want to automate the column header name change as much as possible.

I've first tried creating a "column name source," transposing the table, fuzzy match both correct column names with the other column names, then cross tab. However, this removes special characters and spaces from the headers, replacing them with underscores.

This is not good because it prevents me from uploading the table to my database.

Before, this process included hours of manual data entry. I'm trying to blow the minds of my managers by improving their archaic practices. 😉

I've attached a couple of files:

- Attributes list includes the correct column names in the right order

- ColumnsNamesTest is a sample of the data in the template "master file"

- 3324Y_2021_National-sample is the new file that contains data I need to update the master file with

Attributes_List_v01Mar22.xlsx

ColumNamesTest.xlsx

3324Y_2021_National-sample.xls

cain_m · Answer

Thank you both for responses, but this is not exactly what I'm looking for.

I'll try to explain in a different way.

I need to insert the data from the attached file "Additional.xlsx" into the file "Into.xlsx"

The "Into" file has more columns that the "Additional" file, and I want those columns to still be included. Also, many of the column names in "Into" are different from the column names in "Additional." I need the column names from "Into" to match the column names from "Additional."

I need to do this several times so manually adding columns and changing column names with Select tool is not ideal.

Thanks

Into.xlsx

Additional.xlsx

DawnDuong · Answer

hi @cain_m

Have you tried the Dynamic Rename Setting using "Take Field Names from Right Input Rows". This way, the special characters and spaces are not replaced by underscores.

Dawn.

Qiu · Answer

@cain_m 
The naming issue of cross tab happens to me also.
Usually I use Dynamic Rename to fix it with simple Replace Function.
However it is more complicated in your case.

0305-cain_m.yxmd