Data Mapping from one file to another excel file with different column names

[peachnam (Pichana Namsoom)]

Hi there,

Please help me with the following scenario, as I am unable to find proper solution to it.

Scenario:
I have below type of files to map:
a) CSV File : It has lots of columns and rows
b) Excel File : This is a standard file template with columns (columns name are different from column names in CSV file)

Now for a specific condition - if column (Case_ID_ Count) in CSV file has data as 1, i need to map data from specific columns in CSV file to specific columns in Excel File as below:

What I have done uptil now:
Step 1: Used Read CSV activity to read data from csv file and converted it into datatable - lets say DT1
Step 2: Used Read Range to read Excel file into datable - lets say DT2
Step 3: Used “For Each” rows in DT1 and inside For each loop used “If Activity” with condition as - row(“Case_ID_ Count”).ToString.Contains(“1”)

Now I am stuck as i am not sure how to map the data…Please help as I am pretty new in Uipath Or Development

[PeterK (Peter)]

“Left Join” will be your tool.

Join Data Tables

good luck!

[PeterK (Peter)]

to be more specific:
Split your data table in two tables. one with the flag Case_ID_ Count == 1 and the remaining entries.
Join or replace your values in the first table.
At the end merge both tables back together.

[peachnam (Pichana Namsoom)]

Thanks Buddy, will try and get back soon

[amaresan (Amaran)]

@peachnam

(from x In dtPeriod.AsEnumerable() where (From a In dtPeriod.AsEnumerable() Join b In dt_calandar_datatable1.AsEnumerable() On a(“column3”).ToString Equals b(“Date”).ToString select a).Contains(x) select x).CopyToDataTable()

[peachnam (Pichana Namsoom)]

@PeterK @amaresan

Hi, I followed below steps using Join Table & Filter Table activities:

1. Filtered Data for if Case_ID_ Count column in CSV file contains 1
   

   

2. Used Join Table Activity for Excel Template (DT1) and Filtered data as in step 1 as DT2
   

   

3. Now for matching city column in excel template with another excel’s Location - Column, and if City = Location, get data from RI No column in the other excel to LOcation_RI column in Excel Template,
   

   
   
   
   

4. Finally, used Write Range to copy data from final datatable to the Excel Template workbook
   

   

However i am getting following error in Bot Output panel

[PeterK (Peter)]

Just guessing: the DataTable for Write Range is empty.

I think the join command is wrong. You have to set in the join only the matching columns (e.g. a personal number which exist in both tables and combines both tables). Like NSLookup in Excel. And then you can remove the old columns and rename the freshly joined columns.

[peachnam (Pichana Namsoom)]

That’s right. Having a doubt if Join DataTable activity is right for this scenario.

As in the scenario - need to fetch data from the CSV file which has it’s unique columns and put data in Excel Template with it’s own unique columns

Its basically, there’s no common columns between the two files, we need to get data from specific columns in CSV file and put them in predefined columns in the excel template

[peachnam (Pichana Namsoom)]

is it the function wherein in column in DT1 = Column in DT2, then copy data value from DT2 to DT1?

My Example:

[amaresan (Amaran)]

@peachnam

Could you elaborate your requirement with example?

I did’t Get what is you requirement

[peachnam (Pichana Namsoom)]

Example:
In DT 2 (CSV File) there’s column name = Service_Order_ID
and in DT1 (Excel Template with just columns & blank data) there’s column name = CSOItemKey

Now, how to fetch data from DT2 column " Service_Order_ID" to DT1 Column “CSOItemKey”?

[amaresan (Amaran)]

@peachnam

Hi,

Correct me if I’m wrong.

You want get row from DT2 when DT2(Service_Order_ID) equal to DT1(Service_Order_ID)

For that Use above query , which is shared by me.

[peachnam (Pichana Namsoom)]

Hi,
Below is my files snapshot:
DT1

DT2

Now I want Value in Service_Order_ID column in DT2 within CSOItemKey Column cell in DT1

[peachnam (Pichana Namsoom)]

Yay! Issue Fixed.

Step 1 - Filtered Data in source file as per condition using Filter Data Table activity
Step 2 - Renamed Column name of filtered DT as per Excel Template Column Name using Assign activity (Datatable.Columns(“Old Column Name”).ColumnName = “New Column Name”
Step 3 - Write the data table with renamed column name into excel file 3
Step 4 - Used Merged Data Table Activity to Merge Excel File 3 and Original Excel Template
Step 6 - Used Join activity to get Location code based on City column in Template