Compare and merge data in 2 excel files

[araz.rustamov]

Hi, everyone!
I have 2 excel files.
The first file is a template. Let’s say key | value columns.

And the second file is dynamic and structure is similar.

I need to take values from the dynamic file and insert them into the template file for the particular key:

I’m using 2 for each excel rows activities. First one reads rows from the Secondary file and second for reads data from Template file. It assigns value from secondary file to the value in template file when keys match.
It does what I need but takes a long time when there are a lot of rows.
Is there any other way to approach the same but faster?

[pratik.maskar]

Hi @araz.rustamov ,
Thanks for reaching out to UiPath Commmunity.

First of all, use read range activity to read both the template and dynamic files into DataTables.
Then Create Dictionaries for Faster Lookup, means Iterate through the dynamic file’s DataTable and create a dictionary where the key is the ‘key’ column and the value is the ‘value’ column.
Similarly, iterate through the template file’s DataTable and create another dictionary using the ‘key’ column as the key.
Then an important step is to Iterate through the template DataTable and use the dynamic dictionary to update the ‘value’ column where the keys match.

And finally, Use Write Range activity to write the updated DataTable back to the Excel file.

Regards,
@pratik.maskar

[Shiva_Nikhil]

@araz.rustamov

'Updating the matched values in datatable 1
Dim index As Int32
For Each row In dt1.AsEnumerable
If dt2.AsEnumerable.Any(Function(a) a(0).ToString.trim.Equals(row(0).ToString.Trim)) Then
	index=dt2.AsEnumerable.ToList.FindIndex(Function(a) a(0).tostring.trim.equals(row(0).ToString.trim))
	row(1)=dt2.rows(index).item(1)
	Else
		row(1)=row(1).ToString
	End If
	Next
	

You can try this in invoke code activity

[ppr (Peter)]

In general it is a Lookup case (when 1 col is needed) / Join case (when multiple Cols are needed)

Just to mention a few options:

Joining: Left Join

• Join DataTable Activity and processing the Join Result
• LINQ

Lookup:

• Lookup DataTable activity
• LINQ
• LookUp Dictionary & LINQ / Essential Activities

[postwick (Paul)]

What you want to do is read both into separate datatables, then do a Join Data Table, then write the final datatable to the new Excel file.