Add a column from one data table to another data table by comparing two columns

Learn
, ,
1

Hi,

I have two excel files Input_Sheet.xlsx and Report_Sheet.xlsx(Attached).
I want to add the “Valid To” column from Report_Sheet.xlsx to Input_Sheet.xlsx where the “OA Number and OA Line” of Input_Sheet.xlsx is equal to “Purchasing Document and Item” of Report_Sheet.xlsx

Report_Sheet.XLSX (11.8 KB) Input_Sheet.xlsx (18.7 KB)

1 Like
2

@supermanPunch you had helped me last time with Linq for a similar problem. Any help will be appreciated.
@ClaytonM, @Palaniyappan - Any quick solution will be helpful.

1 Like
3

Hi
—read the datatable from these excel and store as dt1 and dt2
—this line query help you resolve this
dt = (From x In dt1 Join y In dt2 On x(“OA Number and OA Line”).tostring Equals y(“Purchasing Document and Item”).tostring
Select DT1.clone.LoadDataRow(New Object(){x(“columnname”).tostring,x(“columnname”).tostring,y(“columnname”).tostring},False)).CopyToDataTable

Where dt is a variable of type Datatable with default value as New System.Data.Datatable

Cheers @rahul.lakshmanan

4

Here i would have to pass all column values in the loaddatarow right?

5

Yah the columns that you need
@rahul.lakshmanan

6

I would like to have all columns of dt1 and just one column from dt2. But as dt1 can have many columns this code will be lengthy… Any other way?

7

Fine
In that case
—once after getting the datatable dt1 and dt2 use a add Datacolumn activity and mention the new column name you want to add so that it will get added at the end of the datatable dt1

—now use a FOR EACH ROW activity where pass the dt1 as input and inside the loop use snother for each row loop and pass dt2 as input where change the variable name from row to row2

—inside the inner for each row loop use a if condition like this
row(“OA Number”).ToString.Equals(row2(“Purchasing Document”) AND row(“OA Line”).ToString.Equals(row2(“Item”).ToString)

If true it will go to then part where use a assign activity like this
row(“Valid to”) = row2(“Valid to”).ToString

Next to this assign activity inside the if condition use a BREAK activity to come out of loop once after the match if found

Cheers @rahul.lakshmanan

8

My mistake, i should have made it clear that i don’t want to use for loops as it will impact performance. Any way of doing this by linq without adding all the column names? Sorry for the inconvenience again

9

Use join data table. Once you join the tables, based on the condition, you can remove unwanted columns.

10

Join data table does not work in this case. You can try the same with the excel files that i have provided

11

@Palaniyappan any other workaround you can think of? Also when I try with the linq query you provided it is giving error stating Purchasing Document and Item are not part of table

12

This depends on how you are processing the data, because if you are performing steps on each row (where the row is the transaction item), then it would make more sense to keep it simple: just use a filter to get the value from the “Valid To” and add it to the row.

However, if your process is just to combine the two tables, then I’m pretty sure there is a Linq solution somewhere on the forums similar to @Palaniyappan’s first post. If I come across it I will post it, but you could do some searching for it :smiley:

Also, I’m fairly sure the Join Data Table activity let’s you do this, although, it does have limitations and I haven’t used this a whole lot. - You probably need to rename the columns for it to work.

13

@Clayton and @Palaniyappan i was able to make Join data table to work after changing the excel columns Purchasing Document and Line of Report_Sheet.xlsx to Number. I also searched a lot in the forum before posting the question and the excel files.
Thank you guys for helping me in this solution

1 Like
14

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.