topic RE: Delete Repetitive Values in a collection or Excel in Product Forum

Delete Repetitive Values in a collection or Excel

vinodchinthakin — Tue, 11 Jan 2022 08:55:00 GMT

Hi All,
I have a following scenario where I want to remove duplicate/repetitive values in a column.
In below example, I want to remove complete rows of text Raju which is repeated more than once in Column Name and output should look as shown below.
What logic I can apply other than conventional solution like using a loop. If the Input has 50K records or high number of records where using a loop might not be a perfect solution.
Do we have a any action for that in collection or in Excel VBO or any developed code in VB.net or C#?

Input Collection :
Name Age Location
Raju 23 Mumbai
Ravi 34 Bangalore
Raju 27 Hyderabad
Ramesh 45 Delhi

Output Collection :
Name Age Location
Ravi 34 Bangalore
Ramesh 45 Delhi

------------------------------
vinod chinthakindi
------------------------------

RE: Delete Repetitive Values in a collection or Excel

devneetmohanty07 — Tue, 11 Jan 2022 09:25:00 GMT

Hi Vinod,

If the input is an excel file you can simply use the OLEDB VBO and execute a query like 'SELECT * FROM [<Sheet_Name>$] WHERE [Name] IN (SELECT [Name] FROM [<Sheet_Name>$] GROUP BY [Name] HAVING COUNT (*) = 1)'

Just replace the <Sheet_Name> with the excel sheet consisting of your dataset.

For collection, you can try to use a LINQ query like:

var Collection_Out = Collection_In.GroupBy(item => item.Name).Where(g => g.Count() == 1).Select(g => g.Single());

Do let me know if you want to know how to create a LINQ VBO, I have posted multiple threads on this as well otherwise I can show detailed steps as well.

------------------------------
----------------------------------
Hope it helps you and if it resolves you query please mark it as the best answer so that others having the same problem can track the answer easily

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant
Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------

RE: Delete Repetitive Values in a collection or Excel

vinodchinthakin — Tue, 11 Jan 2022 10:03:00 GMT

Thanks Dev for quick response. I will try to implement your solution and will let you know once am done with it,

------------------------------
vinod chinthakindi
------------------------------

RE: Delete Repetitive Values in a collection or Excel

vinodchinthakin — Tue, 11 Jan 2022 10:26:00 GMT

Hi Dev, I have tried your code
var Collection_Out = Collection_In.GroupBy(item => item.Name).Where(g => g.Count() == 1).Select(g => g.Single());
When I tried above code in C# getting following error. Can you name the dll which is required here.
Description: Compiler error at line 1: 'DataTable' does not contain a definition for 'GroupBy' and no accessible extension method 'GroupBy' accepting a first argument of type 'DataTable' could be found (are you missing a using directive or an assembly reference?)

Can you also provide same code in VB.Net so that I can accumulate in the code stage in existing collection VBO.

Thanks

------------------------------
vinod chinthakindi
------------------------------

RE: Delete Repetitive Values in a collection or Excel

devneetmohanty07 — Tue, 11 Jan 2022 10:50:00 GMT

Hi Vinod,

You can create a new business object and add the below External References ('System.Data.DataSetExtensions.dll', 'System.Core.dll') and Namespace Imports ('System.Data.DataSetExtensions', 'System.LINQ') on the Page Description stage of your Initialize action for the LINQ queries to work properly. Also, ensure that the language is selected as 'Visual Basic':

Once you have the updated code options as shown above, create a new action named 'Get Non Repeating Records' and pass two input arguments, Input Collection (Collection) and Field Name (Text). Based on the field name that you provide the duplicate records will be fetched from the Input Collection. Also set an Output parameter as Output Collection (Collection) for this action as shown below:

Add the code stage and use the below code with the input and out arguments as show:

Output_Collection = (From row In Input_Collection _
Group row By a = row(Field_Name).Trim.ToString Into grp = Group _
Where grp.Count=1 _

Select grp.ToList).SelectMany(Function(x) x).CopyToDataTable()

The run results are as follows:

Input Arguments:

Output Result:

You can publish the action and test the same from Process Studio. Let us know if this helps you out 🙂

------------------------------
----------------------------------
Hope it helps you and if it resolves you query please mark it as the best answer so that others having the same problem can track the answer easily

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant
Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------

RE: Delete Repetitive Values in a collection or Excel

vinodchinthakin — Tue, 11 Jan 2022 11:15:00 GMT

Awesome Dev. Its works well.!
I need a help where I have a code C# needs to convert to VB.Net. Can you help me out. The code helps in to "Find Row and Column Numbers by Value"

DT = new DataTable();
DT = new DataTable();
DT.Columns.Add("Column Name");
DT.Columns.Add("RowIndex");DT.Columns.Add("ColumnIndex");
int rowindex=0;
int columnindex=1;
foreach (DataColumn Column in in_DT.Columns)
{
DataRow[] rows = in_DT.Select(Column.ColumnName+" Like '%"+valuetofind+"%'");
if (rows.Length>0)
{ foreach (DataRow row in rows)
{
rowindex = in_DT.Rows.IndexOf(row)+1;
DataRow dr = DT.NewRow(); dr["Column Name"]=Column.ColumnName;
dr["RowIndex"]=rowindex; dr["ColumnIndex"]=columnindex;
DT.Rows.Add(dr);
}
} else {rowindex = 0;
} columnindex=columnindex+1;
}

Note : Also Can you suggest how to convert code from C# to VB, is there a simple way to understand the syntax or any tool which helps in conversion. If possible can you share/make a video/document on it.

------------------------------
vinod chinthakindi
------------------------------

RE: Delete Repetitive Values in a collection or Excel

devneetmohanty07 — Tue, 11 Jan 2022 16:46:00 GMT

Hey Vinod,

I went through your code and have converted the same into VB .NET. You can extend the 'Utility - Collection Manipulation' VBO by creating the action 'Find Row And Column Numbers By Value' with the following input and output parameters:

Input Parameters:

in_DT (Collection): The input collection consisting of the data where the operation needs to be performed.

in_ValueToFind (Text): The text value which needs to be looked up for in the input collection.

Output Parameters:

out_DT (Collection) : The output collection consisting of the Column Name, Row Index and Column Index where the values are found.

Workflow:

Code Stage Parameters:

Code:

Dim DT As New DataTable()
Dim rows() As DataRow

DT.Columns.Add("Column Name")
DT.Columns.Add("RowIndex")
DT.Columns.Add("ColumnIndex")

Dim rowindex As Integer = 0
Dim columnindex As Integer = 1

For Each Column As DataColumn In in_DT.Columns

rows = in_DT.Select(Column.ColumnName + " Like '%" + valuetofind + "%'")

If rows.Length > 0 Then

For Each row As DataRow In rows

rowindex = in_DT.Rows.IndexOf(row)+1
Dim dr As DataRow = DT.NewRow()
dr("Column Name")= Column.ColumnName
dr("RowIndex") = rowindex
dr("ColumnIndex") = columnindex
DT.Rows.Add(dr)

Next

Else

rowindex = 0

End If

columnindex = columnindex + 1

Next

out_DT = DT

Test Results:

Input Arguments:

Output Collection:

VB .NET and C# have few notable differences to state explicitly which I can tell you upfront:

- In VB .NET in case you need to declare variables using Dim and As notations whereas C# directly uses the typical class declaration.
- In VB .NET, you don't have any semicolons whereas in C# it is mandatory
- In VB .NET, you define scopes using For Each....Next, If....End If whereas in C# you use curly braces.
- In both languages the way the arrays are defined differ syntactically a bit

To me personally, C# seems easier to use as I am majorly from a Java based background and C# is a lot similar to Java, but you might have seen that mostly I use VB .NET for my objects, reason being is since last one year or so most of my automations have been using VBA or VB Script in some sort so I have kind of developed this habit of writing syntaxes on it more frequently.

In the end I would say it depends on your personal preference which language you choose and converting logic is not much difficult if you know what object both languages are using. With internet resources being so abundant in nature it has become even easier. But coming to the part if there is any converter which translates C# into VB .NET or vice versa, I am not really aware of this yet but perhaps someone can shed some light over it. I would be more than happy to give it a try as it would reduce my pain point by a bunch to be honest lol 😄

------------------------------
----------------------------------
Hope it helps you and if it resolves you query please mark it as the best answer so that others having the same problem can track the answer easily

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant
Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------

RE: Delete Repetitive Values in a collection or Excel

vinodchinthakin — Wed, 12 Jan 2022 05:52:00 GMT

Thanks Dev. Code works Perfect!
The reason why I have asked to convert to VB script as most of the Assets published by BP or DX team are developed using VB script. It will be easy for me to incorporate new actions in existing VBO using same coding language.

Even I have used some opensource web tools for conversion but seems lot of editing is required. For Example https://converter.telerik.com/
Thanks for providing valid inputs, will work around.

------------------------------
vinod chinthakindi
------------------------------

RE: Delete Repetitive Values in a collection or Excel

GabrielaEsquive — Fri, 03 Feb 2023 19:08:00 GMT

Does anyone know how can this be achieved but to review all columns to identify the duplicated values?

Thanks in advance

------------------------------
Gabriela Esquivel
------------------------------

RE: Delete Repetitive Values in a collection or Excel

devneetmohanty07 — Tue, 07 Feb 2023 08:37:00 GMT

Hi @Gabriela Esquivel,

Thanks for reaching out to community. Wanted to check what exactly you mean by review all columns to identify duplicated values? From what I perceive it is that you want non-repeating records based on all the column values just except any one specific field name. If that is your requirement you can achieve the same by adding a simple action in the 'Collection Manipulation' VBO:

Code:

Output_Collection = Input_Collection.DefaultView.ToTable(True)

------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
WonderBotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------

RE: Delete Repetitive Values in a collection or Excel

GabrielaEsquive — Tue, 07 Feb 2023 14:50:00 GMT

Hi Dev! I opened a conversation explaining better what i want to do:
https://community.blueprism.com/question/get-non-repeating-records-from-collection-review-all-columns#688cccba-ac04-40dc-bbac-0186265f7056

I replicated the solution you gave in this chat but I in the Input "Field Name" it only considers only one column to identify repeated records, i want to do the same but to consider all columns form the collection to identify repeated records , with the note that I also want to get as output only the NON repeated records, example of what I'm trying to achieve:

Input Collection :

Output Collection:

Thanks in advance!

------------------------------
Gabriela Esquivel
------------------------------

RE: Delete Repetitive Values in a collection or Excel

ShuaibSalie — Wed, 08 Feb 2023 17:13:00 GMT

Hi Devneet,

Im trying to use your solution, i get an error Public member 'Trim' on type 'Decimal' any idea how to resolve this?

------------------------------
Shuaib Salie
Medscheme
Africa/Johannesburg
------------------------------

RE: Delete Repetitive Values in a collection or Excel

devneetmohanty07 — Wed, 08 Feb 2023 19:08:00 GMT

Hi @Shuaib Salie,

If possible, can you share any screenshot of the error where I can see at which line it is showing the issue. Also, can you tell me exactly which code you are referring to since there're way too many codes on this thread now lol

------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
WonderBotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------

RE: Delete Repetitive Values in a collection or Excel

ShuaibSalie — Wed, 08 Feb 2023 19:55:00 GMT

Hi @devneetmohanty07,

Its this one:

Output_Collection = (From row In Input_Collection _
Group row By a = row(Field_Name).Trim.ToString Into grp = Group _
Where grp.Count=1 _
Select grp.ToList).SelectMany(Function(x) x).CopyToDataTable()

I figured out why its happening, the Field_Name is a number type. I tested it using a different column (text) and it worked. Can the code be modified when referencing a number type column. I got the error when executing the action.

------------------------------
Shuaib Salie
RPA Developer
Medscheme
Africa/Johannesburg
------------------------------