This activity helps to remove the “duplicate row” from the input “datatable.”
Technical reference
|
|
INPUT |
Table: Enter the "Input datatable" variable where the input data is stored. This parameter helps you to remove the “Duplicate rows” from the “datatable”. This field only accepts the "datatable" data type. |
|
MISC |
DisplayName: Displays the name of the activity. The activity name can also be customized to help in troubleshooting. |
|
|
SkipOnError: It specifies whether to continue executing the workflow even if it throws an error. This supports only Boolean value “True or False”. By default, it is set to “False.” True: Continues the workflow to the next step None: If the option is specified as blank, by default the activity will perform as “False” action. |
||
|
Version: It specifies the version of the Datatable automation feature in use |
* Represents mandatory fields to execute the workflow
Here's an example of how the RemoveDuplicateRow activity works –
In the following example, I am using the “Excel sheet” which is used to automate in
the Use case. To get a better understanding, click here.
1. Drag and drop the " RemoveDuplicateRow " activity onto the designer pane.
a. This activity can only be utilized after the other “Datatable activities.”
b. It helps to remove the “duplicate rows” from existing datatable with a new column.
c. Here I am extracting the output as “Datatable” from the “Excel sheet” using the
“ReadRanges” activity from the “Excel Automation.” (Refer the Use case to
get detailed information).
i. “JoinedTable” is the output for the “datatable” which is declared here.
d. Navigating to the “Datatable” in the properties of “RemoveDuplicateRows” to
provide the input datatable to remove the duplicate rows.
i. Here I am using the datatable “JoinedTable” extracted as output from the
“Excel sheet”.
2. You can add “TableViewer” activity to view the output of the table as duplicates
removed from the table.
3. Now, I have executed the workflow to remove the duplicate rows from the datatable.
Use Case
The following use cases demonstrate how to utilize the "Datatable" automation within
the workflow.
In this use case, we are presented with two distinct Excel files as inputs. For one group
of Excel files, the content comprises a list of employee details for individuals working in
different branches on the same project. The second set of input files contains identical
employee details, but these employees are relocating to different locations.
Based on the provided input files, our task involves cross verifying the Excel files and
updating two columns: "Relocating To" and "Effective From" for each employee.
Subsequently, this information will be transformed into an HTML file and distributed to
the employees.
Refer the attached sample excel files below.
Steps to execute the bot
Follow the below steps to automate the above use case.
Extraction of Data from first excel:
1. Launch the designer and create a workflow named “DataTable”.
2. Drag and drop the “Get Excel Object” activity from the Excel Automation feature.
a. It serves as a scope to automate all other activities within this. It will load the “Excel”
file that has been provided as input.
b. Enter the “filepath” of the excel file or select the “Three dots” and choose the “Excel
File” from the system. (Attached the sample excel file).
c. Drag and drop the “ReadRanges” activity from the Excel Automation feature.
i. We are using this activity to read the ranges from the provided “Excel” file and extract
the data to “Datatable” type as output.
ii. Enter the “Range” as “A1” as we are reading the ranges from “A1” to the end of the
data.
iii. Provide the “SheetName” as “Sheet1” as it contains the data that needs to be read.
iv. Navigate to the properties and create a variable in the “DataTable” field as
“Employee_record”. It will convert and extract the read data to store in the variable
“Employee_record”,
v. There are two ways to create a variable-
vi. Method 1 – Click on the "Datatable" property within the "ReadRanges" activity and
enter the variable name. In this case, we are using " Employee_record." Then, press
"Ctrl+Q," which is a shortcut key to create a variable.
vii. Method 2 - Click on the Variables pane and enter the name
" Employee_record." Then, in the "Variable Types" column, select "Browse for
Types" from the dropdown menu.
viii. The .Net window for data types will appear on the screen, enter the type
name as “System.Data.Datatable” and then click on “OK” button.
Extraction of Data from second excel:
3. Now, let’s drag and drop another “GetExcelObject” and place it next to
the first one.
a. As we have two sets of excel file, we are using another “GetExcelObject”
activity to extract the data from another excel.
b. Enter the “filepath” of the excel file or select the “Three dots” and choose the
“Excel File” from the system. (Attached the sample excel file).
c. Drag and drop the “ReadRanges” activity from the Excel Automation feature.
i. We are using this activity to read the ranges from the provided “Excel” file
and extract the data to “Datatable” type as output.
ii. Enter the “Range” as “A1” as we are reading the ranges from “A1” to the
end of the data.
iii. Provide the “SheetName” as “Sheet1” as it contains the data that needs to
be read.
iv. Navigate to the properties and create a variable in the “DataTable” field as
“Employee_record2”. It will convert and extract the read data to store in the
variable “Employee_record2”.
v. There are two ways to create a variable-
vi. Method 1 – Click on the "Datatable" property within the "ReadRanges"
activity and enter the variable name. In this case, we are using
" Employee_record2." Then, press "Ctrl+Q," which is a shortcut key to
create a variable.
vii. Method 2 - Click on the Variables pane and enter the name
" Employee_record2." Then, in the "Variable Types" column, select
"Browse for Types" from the dropdown menu.
viii. The .Net window for data types will appear on the screen, enter the
type name as “System.Data.Datatable” and then click on “OK” button.
Removing Duplicates:
4. Drag and drop the “RemoveDuplicateRow” activity from the “Datatable”
automation.
a) It helps to remove the “Duplicate rows” from the datatable variable.
b) In this case, we are using to check and remove the duplicate rows from
the datatable variable “Employee_record” i.e., from the first table.
c) Navigate to the properties and enter the input table as “Employee_record”
in the “Table” property.
5. Place “Table viewer” activity next to the “RemoveDuplicateRow” activity.
a) It helps to view the datatable in a table format.
b) Navigate to the properties and enter the input table as “Employee_record”
in the “InputTable” property.
c) Provide the “MessageTitle” as “DuplicatesRemoved” in the property.
d) The “ExpirySecs” will be set as “5” by default. If you want to increase or
decrease the seconds, enter the number. Here we have provided as “10”.
Filtration of Columns:
6. Drag and drop the “FilterTable” activity and place it next to the
“TableViewer” activity.
a. It helps to filter the values from the datatable by either “Keeping” it
or “Removing” the columns or rows from the table.
b. In this case, we are using this activity to remove the “Position, Start
date and Salary” from the first datatable “Employee_record”.
c. Double click on the activity and click on the “Filter Wizard” option.
d. A window will appear, select the “Limit Columns” tab.
e. Enter the column name as “Position” and select the “+” button.
f. Enter the column name as “Start date” and select the “+” button.
g. Enter the column name as “Salary” and choose the “remove” radio
button.
h. Click on “OK” button.
i. Navigate to the properties and provide the “InputDatatable” as
“Employee_record”.
j. Declare a variable in the “OutDatatable” as “FilteredTable”.
k. There are two ways to create a variable-
i. Method 1 – Click on the "OutDatatable" property within the
"FilterTable" activity and enter the variable name. In this case, we are
using " FilteredTable." Then, press "Ctrl+Q," which is a shortcut key
to create a variable.
ii. Method 2 - Click on the Variables pane and enter the name
" FilteredTable." Then, in the "Variable Types" column, select "Browse
for Types" from the dropdown menu.
iii. The .Net window for data types will appear on the screen, enter the
type name as “System.Data.Datatable” and then click on “OK” button.
Joining Tables:
7. Drag and drop the “JoinDataTable” activity and place it next to the
“FilterTable” activity.
a. It helps to join two tables with similar data within it.
b. In this case, we are going to join the two tables using the “Full” as
“JoinType”.
c. Double click on the activity and provide the “Datatable1” as
“FilteredTable.”
d. Specify the “Datatable2” as “Employee_record2” as we are using these
tables to join the values.
e. Click on the three lines adjacent to the “DataTable1” and choose the
“Edit wizard” option.
f. It helps to provide the condition to join the tables. By default, the first condition
needs to be started with “=” expression. Here we are using the “Name” column
which equal in the both tables.
g. Enter the “Datatable1 columns” as “Name” and choose the “Operation” as “=”.
h. Enter the “Datatable2 columns” as “Name” as both tables contains the same
column name with similar values in it. Click on “Save” button.
i. Navigate to the properties and declare a variable in the “DataTable” from
the output properties as “JoinedTable”.
j. There are two ways to create a variable-
i. Method 1 – Click on the "DataTable" property within the "JoinDataTable"
activity and enter the variable name.
In this case, we are using " JoinedTable." Then, press "Ctrl+Q," which is a
shortcut key to create a variable.
ii. Method 2 - Click on the Variables pane and enter the name "JoinedTable"
Then, in the "Variable Types" column, select "Browse for Types" from the
dropdown menu.
iii. The .Net window for data types will appear on the screen, enter the type
name as “System.Data.Datatable” and then click on “OK” button.
k. You can add the “Tableviewer” activity to view the output of the “JoinedTable”
datatable. It will join both tables with all the columns and the column “Name”
from the second table will be added as “Name1”.
Remove Columns:
8. Drag and drop the “RemoveColumn” activity and place it next to the
“JoinDatatable” activity.
a. It helps to remove the unnecessary columns from the existing datatable.
b. In this case, we are using this activity to remove the “Name1” column which
has been added to the “JoinedTable” datatable. As this contains similar values
from the column “Name”.
c. Double click on the activity and provide the “Column” name as “Name1” as it
need to be removed from the table.
d. Navigate to the properties and enter the “DataColumnIndex” as “3”.
i. The index of the column starts from 0. Here the column “Name1” is places
as fourth column in the table.
ii. Thus the index position will be “3”.
e. Specify the “datatable” as “JoinedTable”.
f. You can add the “Tableviewer” activity to view the output of the “JoinedTable”
datatable. The column “Name1” will be removed from the table.
Adding a new column:
9. Drag and drop the “Add a new column” activity and place it next to the
“RemoveColumn” activity.
a. It helps to add or update with a new column in the existing datatable. The
values for the column can be provided only as default value.
b. In this case, we are going to add a new column as “Effective from” to the
existing datatable “JoinedTable” with default values as “01/09/2023”.
c. Double click on the activity and enter the “column” name as “Effective From”
that is needed to be added to the table.
d. Navigate to the properties and provide the “Datatable” as “JoinedTable” to
add the column in this table.
e. Choose the “datatype” as “DateTime” from the drop-down as we are going to
add date as the default value. This datatype format indicates the datatype of
column.
f. Specify the “DefaultValue” as “01/09/2023”. It will be added as default values
to all the rows.
g. Enter the “Position” to mention where the column has to be added. Here we
are going to add as the last column.
i. Since we're unsure about the column index position in the "JoinedTable," utilize
"JoinedTable.Columns.Count" to determine the count of columns and then add the
new column adjacent to it. For instance, if the count is "3," the new column will be
appended as the "4th" column.
ii. Enter the “Position” as “JoinedTable.Columns.Count”.
Conversion to HTML FILE:
10. Drag and drop the “DatatableToHtmlFile” activity and place it next to the
“AddNewColumn” activity.
a. It helps to convert the datatable to the html file format.
b. In this case, we are going to convert the datatable “JoinedTable” to
Html file.
c. Navigate to the properties and enter the “AuthorName” as “FinalData”
to create the name for the html file.
d. Enter the “InputDatatable” as “JoinedTable” in the field.
e. Provide the file path to store the “htmlfile” in the local system and add
“\FinalData” along with the filepath.
11. Now, execute the workflow.
12. Once the flow has been executed, double click on the “htmlfile” stored
in provided filepath to view on the browser.
