Mapping two+ Excel with different Column Names

Hi,

I got the following issue.

I have a Orderlist from CustomerA (named CustomerA.xlsx) where I have to extract the data from and load it into the “Master” Excel Orderlist.xlsx. The “Master” Excel called Orderlist.xlsx has different Column Names and is always without any data in the beginning - just the column names. I’m struggling finding a solution so that the data from CustomerA.xlsx according to its column names are loaded into the Orderlist.xlsx.

There are different Customers with different Excel Column Names (see CustomerB.xlsx). I’m not getting into a solution to work with dynamic column names.

First, I tried to two different DataTables:

  1. dt_master which has the column names of Orderlist.xlsx
  2. dt_customer1 which was read into DataTables with Excel Application Scope

Then I wanted to join the DataTables but totally forgot the logical function of Joining. So I searched the forum for some similar issues but none of these helped me out since their basic problem were different.

Hope you understand my problem, will try to explain it better. Tried to find a solution the whole night with all the datatable activities as well as excel activities.

CustomerA.xlsx (8.4 KB) CustomerB.xlsx (9.5 KB) Orderlist.xlsx (8.2 KB)

Thanks for any help!

In stead of using column name, why you don’t try to use column index?
I saw yours 3 files and if the columns is not change in the future, you can use column index to get data and write to the Orderlist.xlsx

Hi,

Hope the following sample helps you.

Sample20201225-3.zip (28.4 KB)

Regards,

The Columns are always the same from every customer. Customer A has always the columns ID, D_City, Product, CustomerName, D_Amount.
CustomerB has always DB_ID, DB_City, DB_Product, DB_Name, DB_Amount, DB_ShipmentDate as columns. So I could definetely use index instead of name.

Additionally to my description from yesterday, that’s how I want it:

If CustomerA orders sth. and sends me his orderlist named CustomerA.xlsx, I want to read and load its input into the Master Excel Orderlist.xlsx where:

CustomerA.xlsx Columns = Orderlist.xlsx Columns:
ID = CustomerID
CustomerName = Customer
D_City = City
D_Amount = Amount

The rows of column “DB_Product” are not interesting for the procedure, So we don’t want to read and load it into Orderlist.xlsx.

And save the file as Orderlist_CustomerA.xlsx into a specific filepath.

Same procedure for CustomerB where:

CustomerB.xlsx Columns = Orderlist.xlsx Columns:
DB_ID = CustomerID
DB_Name = Customer
DB_City = City
DB_Amount = Amount

The rows of column “DB_Product” and “DB_ShipmentDate” in CustomerB.xlsx are not interesting, so we leave them.

After read & load the data from CustomerB.xlxs correctly, we save the output into “Orderlist_CustomerB.xlsx” into a specific path.

I want to create a workflow where the user can pre-define the customers column names to be assigned to the columns of the Master Exce Orderlist.xlsx. There are more Customers who have similar date in their excel but the column names are different. Think about a CustomerC, having column names such as DB_Ident which has to be assigned to “CustomerID” from Orderlist.xlsx.

Hope you understand what I mean :smiley:

I made a better explanation under my second post / reply to @OanhDTK.
Hope you now better understand what I’m looking for.
I tried your sample, goes into the right way but saves e.g. all Customer*.xlsx into the same Orderlist. I need it seperatly and need to define in upfront which columns of Customer*.xlsx have to be assigned/mapped to Orderlist.xlsx. I will try to understand your sample better. Thank you so much!!

Still need help with dynamic Excel Mapping. I was able to do it with “Filter Wizard” but I couldn’t find a way to do it with variables so that I’m able to do it with different column names.

@ppr saw some postings from you which where similar to my problem, so hoping you’ll can help me too. I really appreciate it! Thanks!