Mapping Different Header Name in Excel

Help Studio
, ,
1

I have this sample Source File

image

Then I have this static Template File

image

Then I created a Config File for header matching

image

The rows are column names

What I want to do is read the Source File, since I already matched the Headers on my Config File I will just copy the entire column for example Customer ID then paste that to the Account Number in my Template File and so on.

Final Output look like this

image

2

Hi @Shoji

Instead of directly copying the columns better to iterate the each row and paste the data based on the column names.

Here you no need to use the Config file for Header Matching. We achieve this with LINQ Expressions.

Check the below process steps,
→ Use Read Range workbook activity to read the sample Source file and store in a datatable called dt_Source.
→ Use another Read range workbook activity to read the Template file and store in another datatable called dt_Template.
→ Then use the assign activity to write the LINQ Expression,

- Assign -> dt_Template = (From row In dt_Source
                           Let CustomerID = row("Customer ID").toString
                           Let CustomerName = row("Customer Name").toString
                           Let Payment = row("Payment").toString
                           Let Percent = row("Percent").toString
                          Select dt_Output.Rows.Add({CustomerName,CustomerID,Payment,Percent})
                                    ).Copytodatatable()

→ Then you can use the Write Range workbook activity to write the dt_Template datatable to the Output file.

Check the below workflow for your better understanding,
Sequence9.xaml (8.0 KB)

image
image852×619 34.7 KB

Check the below output file,
Static.xlsx (8.1 KB)

Hope it helps!!

3

Hi @mkankatala thanks for the reply,

What I’m doing right now is something like this

  1. Read Range for the Source File and store to dtSource, Read Range for the Config File and store to dtHeader
  2. Use For each row in dtHeader to assign values:
    sourceHeader = CurrentRow(0).ToString
    templateHeader = CurrentRow(1).ToString
  3. Now I will loop to dtSource to get the Column names.
  4. After that I will get the data in dtSource then compare it to my templateHeader if it’s match I will paste the data to the Template File.

I’m really confuse right now, I can’t think of any way to do this.

Main.xaml (18.0 KB)

4

To make it less confusing, you can rename the columns in dtSource to match dtTemplate:

image
image585×577 15.3 KB

Then you can loop through dtSource and add each row to dtTemplate, without worrying about different column name.

image
image522×645 17.8 KB

After that you can use Write Range to save dtTemplate to a new file.