Need to compare the Excel1 ID with Excel2 User and Excel Excel1 Date with Excel2 Date and Excel1 Time(its in 12 hour format and need to ignore seconds) with excel2 Time(24 hour format and need to ignore seconds) and get the final macthing data table like below(all columns from excel1 and comments column from excel2)
Try this:
Dim result = From row1 In dtExcel1.AsEnumerable()
Join row2 In dtExcel2.AsEnumerable()
On row1.Field(Of String)(“ID”) Equals row2.Field(Of String)(“User”) And
row1.Field(Of DateTime)(“Date”) Equals row2.Field(Of DateTime)(“Date”) And
row1.Field(Of DateTime)(“Time”).ToString(“hh:mm”) Equals row2.Field(Of DateTime)(“Time”).ToString(“HH:mm”)
Select New With {
.ID = row1.Field(Of String)(“ID”),
.Date = row1.Field(Of DateTime)(“Date”),
.Time = row1.Field(Of DateTime)(“Time”),
.Comments = row2.Field(Of String)(“Comments”)
}
Dim matchingDataTable As DataTable = New DataTable()
matchingDataTable.Columns.Add(“ID”, GetType(String))
matchingDataTable.Columns.Add(“Date”, GetType(DateTime))
matchingDataTable.Columns.Add(“Time”, GetType(DateTime))
matchingDataTable.Columns.Add(“Comments”, GetType(String))
For Each match In result
Dim newRow As DataRow = matchingDataTable.NewRow()
newRow(“ID”) = match.ID
newRow(“Date”) = match.Date
newRow(“Time”) = match.Time
newRow(“Comments”) = match.Comments
matchingDataTable.Rows.Add(newRow)
Next
Hi @Manaswini_UI
You have extra rows and colums that are null in your excel that is the reason for this error.
You can clean the excel and then read the input the issue will be resolved.
You can achieve this comparison using UiPath with the following approach. Since you need to compare IDs, Dates, and Times (ignoring seconds and handling different time formats), we’ll use LINQ for an efficient solution.
Step-by-Step Solution:
Read Both Excel Files:
Use Read Range activity (from Excel or Workbook) to read Excel1 and Excel2 into DataTables, e.g., dtExcel1 and dtExcel2.
Data Comparison Using LINQ:
Use a Assign activity with the following LINQ query to compare the two DataTables based on your conditions:
dtResult = (From row1 In dtExcel1.AsEnumerable()
Join row2 In dtExcel2.AsEnumerable()
On row1("ID").ToString() Equals row2("User").ToString() And
row1("Date").ToString() Equals row2("Date").ToString() And
DateTime.Parse(row1("Time").ToString()).ToString("HH:mm") Equals
DateTime.ParseExact(row2("Time").ToString(), "H:mm:ss", System.Globalization.CultureInfo.InvariantCulture).ToString("HH:mm")
Select dtResult.Rows.Add(row1.ItemArray.Concat({row2("Comments")}).ToArray())
).CopyToDataTable()
Explanation:
We used a Join clause to compare:
ID from Excel1 with User from Excel2.
Date from both Excel sheets should match.
For the Time column:
Converted both formats to 24-hour using ToString("HH:mm").
The result includes all columns from Excel1 and the Comments column from Excel2.
Output to Excel:
Use a Write Range activity to write dtResult back to a new Excel file.
Full UiPath Implementation:
Read Range (for Excel1) → dtExcel1
Read Range (for Excel2) → dtExcel2
Assign Activity with the LINQ Query (as above).
Write Range to output the results.
Sample LINQ Output:
ID Date Time Comments
123 10/15/2024 7:03:34 PM Changed
125 10/17/2024 7:05:34 AM deleted
hi, Thankyou for query tried running it but no luck
could you please check below observation and provide solution
Excel1- Date column values are in Date format in Excel
Excel1-Time column values are in time format in Excel
Excel2- Date column values are in general format in Excel(we are able to get in String)
Excel2- Time column values are in general format in Excel(we are able to get in String)
Thanks for pointing out the formatting issue. Based on your observations, it looks like we need to handle different formats for the Date and Time columns in both Excel files. Let’s update the solution to address these format discrepancies.
Updated Step-by-Step Solution:
Read Both Excel Files:
Use the Read Range activity to read Excel1 and Excel2 into DataTables (let’s call them dtExcel1 and dtExcel2).
Ensure the PreserveFormat option is checked in Read Range for Excel2 to treat the Date and Time columns as strings.
Convert Formats and Compare Using LINQ:
We will handle the conversion of dates and times within the LINQ query to ensure they match correctly.
Here’s the updated LINQ query:
dtResult = (From row1 In dtExcel1.AsEnumerable()
Join row2 In dtExcel2.AsEnumerable()
On row1("ID").ToString() Equals row2("User").ToString() And
row1("Date").ToString("MM/dd/yyyy") Equals DateTime.Parse(row2("Date").ToString()).ToString("MM/dd/yyyy") And
DateTime.Parse(row1("Time").ToString()).ToString("HH:mm") Equals
DateTime.ParseExact(row2("Time").ToString(), "H:mm:ss", System.Globalization.CultureInfo.InvariantCulture).ToString("HH:mm")
Select dtResult.Rows.Add(row1.ItemArray.Concat({row2("Comments")}).ToArray())
).CopyToDataTable()
Explanation of Updates:
Date Conversion:
For Excel1, we used .ToString("MM/dd/yyyy") to match the date format.
For Excel2, we parsed the date string to match the format using DateTime.Parse().
Time Conversion:
For Excel1, we used .ToString("HH:mm") to convert to 24-hour format without seconds.
For Excel2, we used DateTime.ParseExact() to correctly interpret the 24-hour time format.
Additional Steps:
If your date format differs (e.g., dd/MM/yyyy), adjust the .ToString() methods accordingly.
Make sure Read Range uses PreserveFormat for strings to avoid data type misinterpretations.
Write the Output to Excel:
Use Write Range activity to save the dtResult DataTable to a new Excel file.
Let me know if this updated solution works for you!
