How to get the next cells of a special column with dynamic conditions and remove extra cells. 'LINQ'

Mansour_Dalir 1,371 Reputation points
2023-07-30T17:14:13.28+00:00

hi . Wishing everyone good health .

I want to reach the result by 'LINQ'. All 'Word' are variable. I used 'Word' here as an example. In the (get) section, there was no null word, no problem. (A_Get:null/A_Get:) First, bring their children close to him (A_Get And B_Get ), then remove the items From 'RemoveList' Because 'End' may be among the eliminations . Thankful.

        'Always fixed  'A_Get:','B_Get:','Limit End' and '|',
	     ' 'End' is among the deletions 
        Dim RemoveList As String() = {"RemOve1", "Remove2", "Delete", "I am Redundant","End"}
        Dim sttMain As String() = {"A|Delete|Word|A_Get:|1|2|3|B_Get:|End|Word|Word|Limit End|Other Word",
            "B|Remove2|Word|A_Get:|1|2|B_Get:|1|2|End|Word|Word|Limit End|Other Word",
            "C|I am Redundant|Word|A_Get:|2|ABC|5|B_Get:|End|Word|Word|Limit End|Other Word",
            "D|Word|RemOve1|A_Get:|B_Get:|End|I am Redundant|Word|Word|Limit End|Other Word",
            "E|Word|RemOve1|A_Get:|1|2|B_Get:|Helo!|3|End|I am Redundant|Word|Word|Limit End|Other Word",
           "F|Word|I am Redundant|A_Get:|1|B_Get:|2|OK|End|I am Redundant|Word|RemOve1|Word|Limit End|Other Word"}
        Dim Result As String() = {"A|Word|A_Get:1,2,3|B_Get:null|Word|Word",
             "B|Word|A_Get:1,2|B_Get:1,2|Word|Word",
              "C|Word|A_Get:2,ABC,5|B_Get:null|Word|Word",
              "D|Word|A_Get:null|B_Get:null|Word|Word",
              "E|Word|A_Get:1,2|B_Get:Helo!,3|Word|Word",
              "F|Word|A_Get:1|B_Get:2,OK|Word|Word"}

Then I extract the important data from this table by this code.

20230731_093550

Windows Forms
Windows Forms
A set of .NET Framework managed libraries for developing graphical user interfaces.
1,799 questions
VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,496 questions
{count} votes

Accepted answer
  1. Jiachen Li-MSFT 24,201 Reputation points Microsoft Vendor
    2023-08-02T08:55:07.9433333+00:00

    Hi @Mansour_Dalir ,

    Please check if the following code helps.

    I treated End as a separate markup element, removing it from the RemoveList.

            Dim results As List(Of String) = sttMain.Select(Function(line)
                                                               Dim elements As String() = line.Split("|"c)
                                                               Dim result As New StringBuilder()
                                                               Dim ABelement As Boolean = False
                                                               Dim keepAdding As Boolean = True
    
                                                               For Each element As String In elements
                                                                   If Not RemoveList.Contains(element) Then
                                                                       If element = "A_Get:" Then
                                                                           result.Append("|")
                                                                           result.Append(element)
                                                                           ABelement = True
                                                                       ElseIf element = "B_Get:" Then
                                                                           If result(result.Length - 1) = ":" Then
                                                                               result.Append("null")
                                                                           End If
                                                                           result.Append("|")
                                                                           result.Append(element)
                                                                           ABelement = True
                                                                       ElseIf element = "End" Then
                                                                           If result(result.Length - 1) = ":" Then
                                                                               result.Append("null")
                                                                           End If
                                                                           ABelement = False
                                                                       ElseIf element = "Limit End" Then
                                                                           keepAdding = False
                                                                           Exit For ' Ignore the rest of the elements starting from "Limit End"
                                                                       Else
                                                                           If ABelement Then
                                                                               result.Append(",")
                                                                               result.Append(element)
                                                                           Else
                                                                               If result.Length > 0 Then
                                                                                   result.Append("|")
                                                                               End If
                                                                               result.Append(element)
                                                                           End If
                                                                       End If
                                                                   End If
                                                               Next
                                                               result.Replace(":,", ":")
                                                               Return result.ToString()
                                                           End Function).ToList()
    
            For Each result In results
                Console.WriteLine(result)
            Next
    

    Best Regards.

    Jiachen Li


    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


1 additional answer

Sort by: Most helpful
  1. Azar 13,785 Reputation points
    2023-07-30T17:54:47.28+00:00

    Hi @Mansour_Dalir

    1. Define a helper function to extract the specific strings based on the dynamic conditions.
    2. Use LINQ to process each element in the sttMain array and create the resulting array.
    Imports System.Text.RegularExpressions  Module MainModule     Sub Main()         Dim RemoveList As String() = {"RemOve1", "Remove2", "Delete", "I am Redundant"}         Dim sttMain As String() = {             "A|Delete|Word|A_Get:|1|2|3|B_Get:|End|Word|Word|Limit End|Other Word",             "B|Remove2|Word|A_Get:|1|2|B_Get:|1|2|End|Word|Word|Limit End|Other Word",             "C|I am Redundant|Word|A_Get:|2|ABC|5|B_Get:|End|Word|Word|Limit End|Other Word",             "D|Word|RemOve1|A_Get:|B_Get:|End|I am Redundant|Word|Word|Limit End|Other Word",             "E|Word|RemOve1|A_Get:|1|2|B_Get:|Helo!|3|End|I am Redundant|Word|Word|Limit End|Other Word",             "F|Word|I am Redundant|A_Get:|1|B_Get:|2|OK|End|I am Redundant|Word|RemOve1|Word|Limit End|Other Word"         }          Dim Result As String() = sttMain.Select(Function(input) ProcessString(input, RemoveList)).ToArray()          For Each res In Result             Console.WriteLine(res)         Next     End Sub      Function ProcessString(input As String, removeList As String()) As String         Dim parts As String() = input.Split("|"c)         Dim identifier As String = parts(0)         Dim data As String() = parts.Skip(1).ToArray()          ' Remove unwanted elements         data = data.Where(Function(item) Not removeList.Contains(item)).ToArray()          ' Process 'A_Get' and 'B_Get' sections         Dim regex As New Regex("(A_Get:|B_Get:)(.*?)\|")         data = regex.Replace(String.Join(",", data), Function(match As Match) _             If(match.Groups(1).Value = "A_Get:", "A_Get:" & String.Join(",", match.Groups(2).Value.Split(" "c).Where(Function(s) Not removeList.Contains(s)).ToArray()), _                                               "B_Get:" & String.Join(",", match.Groups(2).Value.Split(" "c).Where(Function(s) Not removeList.Contains(s)).ToArray()))).Split(","c)          Return identifier & "|" & String.Join("|", data) & "|Word|Word"     End Function End Module 
    
    
    

    If this helps kindly accept answer thanks.