Input data with different field name

[jameke]

Hi Community,

I have multiple CSV files with different field names and a field name mapping table as shown below:

Combine_data	Data_1	Data_2	Data_3
Price	price	Market price	Market Price
etc	etc	etc	etc

I want to build a batch macro and utilize the Dynamic Rename tool to process all the files. Here's my proposed process, and I'm curious if there's a simpler way to accomplish this:

Preparation

• Formula Tool: Add field called "File_Name" to all CSV file

Data_1

price	etc	File_Name
23		Data_1
50		Data_1

• Tranpose the mapping table to below:

Combine_data	Name	Value
Price	Data_1	price
Price	Data_2	Market price
Price	Data_3	Market Price
etc	etc	etc

Batch Macro: Build Rename Table

• Join the file and mapping table by matching File_Name  with  Name
• Select the Combine_data, Name, Value and Unique

Combine_data	Name	Value
Price	Data_1	price
etc	etc	etc

• Use Formula Tool to adjust value of Name field

Combine_data	Name	Value
Price	Before_Combine	price
etc	etc	etc

• Employ the Cross Tab Tool:

Combine_data	Before_Combine
Price	price
etc	etc

• Finally, use Dynamic Rename Tool and Macro Output

This process aims to streamline the renaming of fields across multiple CSV files.

If anyone has suggestions for simplifying this workflow, I'd love to hear them!

[markcurry · Accepted solution]

Hi @jameke , the dynamic rename tool should do the trick for you alright.  I don't think you need to Transpose and Cross Tab your data.    An easy formula in the Dynamic Rename should work.

IF UpperCase([_CurrentField_]) = "MARKET PRICE" THEN
     "Price"
ELSEIF UpperCase([_CurrentField_]) + "PRICE" THEN
      "Price"
ELSE
       [_CurrentField_]
ENDIF

[jameke]

Hi markcurry,

Thank you for your assistance. Using Dynamic Rename with a formula has indeed solved my problem. I initially opted for Transpose to address the issue because I wanted a more flexible approach for certain scenarios.

For instance, if the mapping table contains a large amount of row data requiring manipulation, or if both the mapping table and data are subject to change over time.

Nonetheless, your response has been invaluable, and I'm truly thankful for your assistance!