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 DataRowDT.Columns.Add("Column Name")DT.Columns.Add("RowIndex")DT.Columns.Add("ColumnIndex")Dim rowindex As Integer = 0Dim columnindex As Integer = 1For 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 + 1Nextout_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 :D
------------------------------
----------------------------------
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----------------------------------
------------------------------
Original Message:
Sent: 01-11-2022 11:15
From: vinod chinthakindi
Subject: Delete Repetitive Values in a collection or Excel
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
Original Message:
Sent: 01-11-2022 10:50
From: Devneet Mohanty
Subject: Delete Repetitive Values in a collection or Excel
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
Original Message:
Sent: 01-11-2022 10:25
From: vinod chinthakindi
Subject: Delete Repetitive Values in a collection or Excel
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
Original Message:
Sent: 01-11-2022 09:25
From: Devneet Mohanty
Subject: Delete Repetitive Values in a collection or Excel
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());
------------------------------
----------------------------------
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
Original Message:
Sent: 01-11-2022 08:54
From: vinod chinthakindi
Subject: Delete Repetitive Values in a collection or Excel
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
------------------------------