Update excel datatable

Help
1

Hi, how can I compare two excels and read data from one and update it into another ? Write range, write cell doesn’t work in my approach as I do not have an index value or particular cell. I just want to compare and update Column in second excel with the value from First excel.
Any inputs shall be much appreciated . Thanks

2

Hi
we can use LOOKUP DATA TABLE

Cheers @Shailesh123

3

@Palaniyappan - Lookup datatable will help me in comparison . I am stuck with updating the excel after comparison as Write range does not work in my case. In my case I want to update the row in 2nd excel only when the comparison between 2 excels is a success .

4

Using the row index given by LookupDatatable you can use write cell activity to update in your excel

5

we can use the output of the lookup datatable as to writecell activity
or fine hope these steps would help you resolve this
–use excel application scope and pass the file path of first file and inside the scope use a READ RANGE activity and get the output with a variable of type datatable named dt1
–again use the method to get the datatable from second excel file named dt2
–then use a for each row loop and pass the variable dt1 as input and change the variable name from row to row1
and inside this loop use another for each row loop and pass the input as dt2 and change the variable name from row to row2
–now inside this inner for each row loop use a IF condition like this
row1(“yourcolumnname”).ToString.Equals(row2(“yourcolumnname”).ToString)
if the condition is true it will go to THEN Part where we can use a simple assign activty to assign the value to dt2 rows (as you want)
like this
row2(“yourcolumnname”) = “your value to be updated”
or if you want to update the vaalue from first datatable then like thiis
row2(“yourcolumnname”) = row1(“yourcolumnname”).ToString.

–Then we can use WRITE RANGE activity and mention this dt2 as input and mention the sheetname in second excel file we want

hope this would help you
Cheers @Shailesh123

6

@Palaniyappan- My Excel has 10,000 in dt1 and 7000 in dt2 . So when I use two for each’s its taking 11 minutes to write range ! This data will keep increasing and Write range is taking a lot of time to write the data .

7

My Excel has 10,000 in dt1 and 7000 in dt2 . So when I use two for each’s its taking 11 minutes to write cell! This data will keep increasing and Write cell is taking a lot of time to write the data .

1 Like
8

then instead of using second for each row loop we can use LOOKUP datatable activity and mention the output of that activity as input to the assign activity

that would not degrade the performance
i have used it
Cheers @Shailesh123

9

1.In that case you can read two excels
2.By loop one datatable you can use select method to compare values ,
3.if matched update the value to datatable itself,
4.at last u can write the updated datatable to excel.

Actually select methods is the best approach to filter or compare or get values from datatable and also the fastest

10

here you go with an example

Cheers @Shailesh123

11

@Palaniyappan- this approach wont work in my case as I have to loop through the datatable. I’ll have to filter and read values - from that filtered data table , then move to next filter ,read values and from that filtered data table and update and so on.