I am having two data tables and need to compare the Table1.User with Table2.User and Table1.Date and Table1. time with Table2.Date Time column.
Could you pleas help on this.
Note: Table2 Date time column in 24hr format.
Output table should have columns from Table1+Table2 TrackingNumber.
Table 1:
You can try this:
To compare two DataTables in UiPath based on the criteria you’ve specified and produce the desired output, you can follow these steps:
Steps to Prepare the UiPath Workflow
Read Both DataTables: Load Table 1 and Table 2 from their respective sources (e.g., Excel, CSV). Use “Read Range” or “Read CSV” activities to get the data into DataTable1 and DataTable2.
Prepare Comparison: Since the Date and Time from Table 1 need to match with the Date Time from Table 2, you’ll need to combine the Date and Time from DataTable1 into a single DateTime format that matches the one in DataTable2.
Create Output DataTable: Create a new DataTable to hold the output (OutputTable). It should consist of columns from DataTable1 plus the TrackingNumber from DataTable2.
Compare Data: Loop through each row in DataTable1 and for each row, loop through DataTable2 to check if there is a match based on User and the combined DateTime.
Fill Output DataTable: If a match is found, add the row from DataTable1 along with the TrackingNumber from DataTable2 to the OutputTable.
Write the Output: Finally, write the resulting OutputTable to an Excel or CSV file using “Write Range” or “Write CSV”.
Example of the UiPath Workflow
Here’s a more detailed step-by-step implementation:
OutputTable = New DataTable()
OutputTable.Columns.Add("User")
OutputTable.Columns.Add("Date")
OutputTable.Columns.Add("Time")
OutputTable.Columns.Add("TrackingNumber")
Compare Rows
For Each row1 In DataTable1.Rows
{
user1 = row1("User").ToString
date1 = Convert.ToDateTime(row1("Date")).ToString("yyyy-MM-dd")
time1 = Convert.ToDateTime(row1("Time")).ToString("HH:mm:ss") ' Convert to 24-hr format
datetime1 = date1 & " " & time1
foundMatch = False
For Each row2 In DataTable2.Rows
{
user2 = row2("User").ToString
datetime2 = row2("Date Time").ToString ' This is already in 24hr format
If user1 = user2 And datetime1.Equals(datetime2) Then
foundMatch = True
trackingnumber = row2("TrackingNumber").ToString
OutputTable.Rows.Add(user1, row1("Date").ToString, row1("Time").ToString, trackingnumber)
End If
}
If Not foundMatch Then
OutputTable.Rows.Add(user1, row1("Date").ToString, row1("Time").ToString, "")
End If
}
Write Output DataTable to Excel
Excel Application Scope (Path to Your Output File)
{
Write Range: "Output" → Input: OutputTable
}
Detailed Breakdown
Datetime Handling: Make sure to use Convert.ToDateTime() to manipulate dates and times correctly in UiPath. You convert the time to 24-hour format using ToString("HH:mm:ss").
Condition Checks: The comparison checks both User values and the combined DateTime for a match. If not found, an empty tracking number is assigned for that user.
Output: The resulting DataTable will have all users from Table 1 appended with the corresponding TrackingNumber if found, otherwise an empty string for TrackingNumber.
’ Create a new DataTable for the output
Dim outputTable As New DataTable()
outputTable.Columns.Add(“User”)
outputTable.Columns.Add(“Date”)
outputTable.Columns.Add(“Time”)
outputTable.Columns.Add(“TrackingNumber”)
’ LINQ query to join the two DataTables based on User and DateTime
Dim query = From row1 In dtTable1.AsEnumerable()
Group Join row2 In dtTable2.AsEnumerable()
On row1.Field(Of String)(“User”) Equals row2.Field(Of String)(“User”)
Into UserGroup = Group
From userRow In UserGroup.DefaultIfEmpty()
Let dateTime1 = DateTime.Parse(row1.Field(Of String)(“Date”).ToString() & " " & row1.Field(Of String)(“Time”).ToString())
Let dateTime2 = If(userRow IsNot Nothing, DateTime.ParseExact(userRow.Field(Of String)(“Date Time”), “M/d/yyyy HH:mm”, Nothing), DateTime.MinValue)
Where dateTime1 = dateTime2
Select outputTable.Rows.Add(row1.Field(Of String)(“User”),
row1.Field(Of String)(“Date”),
row1.Field(Of String)(“Time”),
If(userRow IsNot Nothing, userRow.Field(Of String)(“TrackingNumber”), String.Empty))
’ To execute the query
For Each result In query
’ This will populate the outputTable with the necessary rows
Next
Make changes according to your requriements(variables etc)
To compare two data tables based on User, Date, and Time columns, with Table1 having separate Date and Time columns and Table2 having a combined DateTime column, here’s a step-by-step solution using UiPath:
Solution 1: Using LINQ with For Each Row
This approach leverages LINQ and date/time manipulation to get matching records.
Step-by-Step:
Read Excel Data:
Use Read Range activity to load both Excel sheets into two DataTables: dtTable1 and dtTable2.
Add New Column to Store Tracking Number:
Use the Add Data Column activity to add a new column called “TrackingNumber” in dtTable1.
For Each Row with LINQ:
Use a For Each Row activity on dtTable1.
Inside the loop, use an Assign activity with the following LINQ expression to find a matching row:
Use the Join Data Tables activity with Inner Join mode on the “User” and “DateTime” columns.
Output the Result:
The result will contain all matching rows. Use the Write Range activity to output the joined table.
Solution 3: Using DataTable.Select() Method
This is a straightforward approach using the DataTable.Select() method.
Step-by-Step:
Read Both Tables into dtTable1 and dtTable2.
Add Column in Table1:
Add a “TrackingNumber” column to dtTable1.
Loop Through Rows in Table1:
For Each row In dtTable1.Rows
Dim filter As String = $"User = '{row("User")}' AND DateTime = '{DateTime.Parse(row("Date").ToString & " " & row("Time").ToString).ToString("M/d/yyyy H:mm")}'"
Dim foundRows As DataRow() = dtTable2.Select(filter)
If foundRows.Length > 0 Then
row("TrackingNumber") = foundRows(0)("TrackingNumber").ToString()
End If
Next
