Compare two excel file column values and update the mismatch values in the template output excel (using linq, if possible)

[Neha_Kumari1 (Neha Kumari)]

Hello everyone, I need help with an excel problem. I have two excels, ‘Excel1’ & ‘Excel2’. I need to compare columns from the excel, taking Employee ID as the base column, columns name might vary a little, but the excel column name are to be compared based on the field name given,

the two excels are: Excel1.xlsx (8.9 KB) Excel2.xlsx (8.8 KB)
I am adding Excel1 data to queue and I am retrieving all the fields as string (NAME, ROLE, EMPLOYEE ID, SALARY,LOCATION)
now the problem statement is I need to enter the output in a template output file OutputFile.xlsx (8.6 KB), (above screenshot), if the fields value matches, it should be left empty, if not, corresponding values has to be written under ‘Excel1 value’ and ‘Excel2 value’ (NOTE: Employee ID has to be entered in the output file no matter what, for both).
Each employee will be having a separate OutputFile.xlsx.
Can comparison of the Excel2 column values with retrieved Excel1 string values, and writing the difference value in template output file be done through LINQ query by any chance, for the faster approach?

Thanks in advance!

[Mir.Jasimuddin]

Hi @Neha_Kumari1,
Yes, this can be achieved using LINQ for better performance.

1. Read both Excel1 and Excel2 into dtExcel1 and dtExcel2 using Read Range.
2. Create a dictionary to map Excel1 and Excel2 column names.
3. For each row in dtExcel1, get the matching row from dtExcel2 using LINQ:
   matchedRow = dtExcel2.AsEnumerable().FirstOrDefault(Function(r) r("Employee ID").ToString.Trim = row("EMPLOYEE ID").ToString.Trim)
4. Initialize a new DataTable for the output using Build Data Table.
5. Loop through each mapped field, compare values from both sheets.
6. If values differ, write to “Excel1 value” and “Excel2 value” columns.
7. Always write “Employee ID” for both sides, even if other fields match.
8. Write the output to a separate Excel for each employee using Write Range.

Hope this helps!

[Neha_Kumari1 (Neha Kumari)]

hi @Mir.Jasimuddin , thanks for your response. For Excel1 I’m getting the values through transaction item, and I’m storing the fields in string using SpecificContent function, so there’ll be only dtExcel2, no data table for Excel1. Can you please clarify how can I match values according to this scenario using linq. And how can I write the corresponding values to the corresponding field names, as per the given screenshot format

[Mir.Jasimuddin]

Hey @Neha_Kumari1, Thanks for the clarification! Since Excel1 values are coming from transactionItem.SpecificContent, you can skip creating a DataTable for Excel1. Here’s how you can approach it:

1. Read Excel2 into a DataTable (dtExcel2).

2. Use LINQ to find the matching row from dtExcel2 using the Employee ID from the transaction item:

   matchedRow = dtExcel2.AsEnumerable().FirstOrDefault(Function(r) r("Employee ID").ToString.Trim = transactionItem.SpecificContent("EMPLOYEE ID").ToString.Trim)
   

3. Create an output DataTable (using Build Data Table) with columns: Field Name, Excel1 Value, Excel2 Value.

4. Use a mapping (e.g., a dictionary) of field names between Excel1 and Excel2.

5. Loop through each field, compare values:

   If transactionItem.SpecificContent("FieldX").ToString.Trim <> matchedRow("FieldY").ToString.Trim Then
       ' Add a row to the output table with field name and both values
   End If
   

6. Write the output to Excel or a log file as needed.

Let me know if need anything else.

[Neha_Kumari1 (Neha Kumari)]

hey @Mir.Jasimuddin , I am a bit confused, how would we be using this second one, matchedRow, it’s datarow type, right, we won’t be able able to print the values using For each as its DataRow and not array of DataRow

it would help if you can describe step a bit, i have resumed UiPath after a while.. trying to recall all as of now
Really appreciate your inputs !

[Mir.Jasimuddin]

Sure @Neha_Kumari1, you’re right — since matchedRow is a single DataRow, you don’t use For Each on it. Instead, you directly access values like this:

matchedRow("ColumnName").ToString

So inside your loop (using field mapping), you compare like this:

val1 = transactionItem.SpecificContent("FieldName").ToString.Trim
val2 = matchedRow("MappedColumnName").ToString.Trim

Then check if they differ and add to the output table. No need to iterate over matchedRow — just treat it like a single row and access columns by name.

Let me know if you need anything else

[sudster]

Hi @Neha_Kumari1 , here’s my solution; adjust to your requirement.