As per output file I want three values: AA, XX_YY and Test AA which are column1, USERID and NAME. The logic will be same for all the records. Please refer output file for format details. Let me know for any details.
@ppr@Yoichi Can you also have a look on this thread.
Try this below process:
=> Use Read Text File to read the text file and store the output in a variable say InputText.
= Use Invoke code activity and paste the below code:
' Output DataTable
outputDataTable = New DataTable
outputDataTable.Columns.Add("Group", GetType(String))
outputDataTable.Columns.Add("UserID", GetType(String))
outputDataTable.Columns.Add("Name", GetType(String))
' Process the input text
Dim lines() As String = inputText.Split(Environment.NewLine.ToCharArray, StringSplitOptions.RemoveEmptyEntries)
Dim currentGroup As String = ""
For Each line As String In lines
If line.Contains("USERID:") Then
Dim parts() As String = line.Split({"USERID:"}, StringSplitOptions.RemoveEmptyEntries)
If parts.Length > 1 Then
Dim userIDParts() As String = parts(1).Trim().Split({"NAME:"}, StringSplitOptions.RemoveEmptyEntries)
If userIDParts.Length > 1 Then
outputDataTable.Rows.Add(currentGroup.Trim(), userIDParts(0).Trim(), userIDParts(1).Trim())
End If
End If
ElseIf line.Contains("No users connect to group") Then
currentGroup = line.Trim()
outputDataTable.Rows.Add(currentGroup, "", "")
ElseIf line.Contains("$DSNGRP") Then
currentGroup = line.Substring(0, Math.Min(8, line.Length)).Trim()
End If
Next
But I see some discrepancies. Can you please do a quick wrap over them.
No users connect to group should be column 3 that is Name
In column Group, for every group we create we have different values like Test, TestPRODUP, TestUTIL and etc. but here in screenshot I only see Test in column A.
Please let me know for any clarifications!.
Thanks!!
' Output DataTable
outputDataTable = New DataTable
outputDataTable.Columns.Add("Group", GetType(String))
outputDataTable.Columns.Add("UserID", GetType(String))
outputDataTable.Columns.Add("Name", GetType(String))
' Process the input text
Dim lines() As String = inputText.Split(Environment.NewLine.ToCharArray, StringSplitOptions.RemoveEmptyEntries)
Dim currentGroup As String = ""
Dim currentName As String = ""
For Each line As String In lines
If line.Contains("USERID:") Then
Dim parts() As String = line.Split({"USERID:"}, StringSplitOptions.RemoveEmptyEntries)
If parts.Length > 1 Then
Dim userIDParts() As String = parts(1).Trim().Split({"NAME:"}, StringSplitOptions.RemoveEmptyEntries)
If userIDParts.Length > 1 Then
outputDataTable.Rows.Add(currentGroup.Trim(), userIDParts(0).Trim(), userIDParts(1).Trim())
End If
End If
ElseIf line.Contains("No users connect to group") Then
currentName = "No users connect to group"
outputDataTable.Rows.Add(currentGroup, "", currentName)
ElseIf line.Contains("$DSNGRP") Or line.Contains("TestPRODUP") Or line.Contains("TestUTIL") Then
currentGroup = line.Substring(0, Math.Min(8, line.Length)).Trim()
currentName = ""
End If
Next
Thanks a lot again for providing the updated solution, but I want to highlight that
in your code in below line you have used a contains on these words, in real scenario I wil get a lot more that is like TestPRODRA, TestUATNAObj and others so I want a dynamic logic for column A βGroupβ. the values in column can be dynamic.
' Output DataTable
outputDataTable = New DataTable
outputDataTable.Columns.Add("Group", GetType(String))
outputDataTable.Columns.Add("UserID", GetType(String))
outputDataTable.Columns.Add("Name", GetType(String))
' Process the input text
Dim lines() As String = inputText.Split(Environment.NewLine.ToCharArray, StringSplitOptions.RemoveEmptyEntries)
Dim currentGroup As String = ""
Dim currentName As String = ""
For Each line As String In lines
If line.Contains("USERID:") Then
Dim parts() As String = line.Split({"USERID:"}, StringSplitOptions.RemoveEmptyEntries)
If parts.Length > 1 Then
Dim userIDParts() As String = parts(1).Trim().Split({"NAME:"}, StringSplitOptions.RemoveEmptyEntries)
If userIDParts.Length > 1 Then
outputDataTable.Rows.Add(currentGroup.Trim(), userIDParts(0).Trim(), userIDParts(1).Trim())
End If
End If
ElseIf line.Contains("No users connect to group") Then
currentName = "No users connect to group"
outputDataTable.Rows.Add(currentGroup, "", currentName)
Else
' Extract valid groups dynamically using Split
Dim words() As String = line.Split(New String() {" "}, StringSplitOptions.RemoveEmptyEntries)
For Each word As String In words
' Check if the word is not a common keyword or contains special characters
If Not word.Contains("$") AndAlso Not word.Contains("@") AndAlso Not word.Contains("USERID:") Then
' Use the first encountered word as the dynamic group value
currentGroup = word
currentName = ""
Exit For
End If
Next
' Check if line contains USERID and NAME, then add row
If line.Contains("USERID:") Then
Dim parts() As String = line.Split({"USERID:"}, StringSplitOptions.RemoveEmptyEntries)
If parts.Length > 1 Then
Dim userIDParts() As String = parts(1).Trim().Split({"NAME:"}, StringSplitOptions.RemoveEmptyEntries)
If userIDParts.Length > 1 Then
outputDataTable.Rows.Add(currentGroup.Trim(), userIDParts(0).Trim(), userIDParts(1).Trim())
End If
End If
End If
End If
Next
I checked the output properly and it seems to be correct !! Thankyou.
Can you please also add a try catch functionality to this. In cases where an exception comes while performing split operation/other operations the exception should be added to the specific list/array created on which I can later take it out and print/use it accordingly.
' Output DataTable
outputDataTable = New DataTable
outputDataTable.Columns.Add("Group", GetType(String))
outputDataTable.Columns.Add("UserID", GetType(String))
outputDataTable.Columns.Add("Name", GetType(String))
' List to store exceptions
exceptionsList = New List(Of String)
' Process the input text
Try
Dim lines() As String = inputText.Split(Environment.NewLine.ToCharArray, StringSplitOptions.RemoveEmptyEntries)
Dim currentGroup As String = ""
Dim currentName As String = ""
For Each line As String In lines
If line.Contains("USERID:") Then
Try
Dim parts() As String = line.Split({"USERID:"}, StringSplitOptions.RemoveEmptyEntries)
If parts.Length > 1 Then
Dim userIDParts() As String = parts(1).Trim().Split({"NAME:"}, StringSplitOptions.RemoveEmptyEntries)
If userIDParts.Length > 1 Then
outputDataTable.Rows.Add(currentGroup.Trim(), userIDParts(0).Trim(), userIDParts(1).Trim())
End If
End If
Catch ex As Exception
exceptionsList.Add("Error processing line: " & line & ". Exception: " & ex.Message)
End Try
ElseIf line.Contains("No users connect to group") Then
currentName = "No users connect to group"
outputDataTable.Rows.Add(currentGroup, "", currentName)
Else
Try
' Extract valid groups dynamically using Split
Dim words() As String = line.Split(New String() {" "}, StringSplitOptions.RemoveEmptyEntries)
For Each word As String In words
' Check if the word is not a common keyword or contains special characters
If Not word.Contains("$") AndAlso Not word.Contains("@") AndAlso Not word.Contains("USERID:") Then
' Use the first encountered word as the dynamic group value
currentGroup = word
currentName = ""
Exit For
End If
Next
' Check if line contains USERID and NAME, then add row
If line.Contains("USERID:") Then
Dim parts() As String = line.Split({"USERID:"}, StringSplitOptions.RemoveEmptyEntries)
If parts.Length > 1 Then
Dim userIDParts() As String = parts(1).Trim().Split({"NAME:"}, StringSplitOptions.RemoveEmptyEntries)
If userIDParts.Length > 1 Then
outputDataTable.Rows.Add(currentGroup.Trim(), userIDParts(0).Trim(), userIDParts(1).Trim())
End If
End If
End If
Catch ex As Exception
exceptionsList.Add("Error processing line: " & line & ". Exception: " & ex.Message)
End Try
End If
Next
Catch ex As Exception
' Handle any other exception that might occur during the process
exceptionsList.Add("General Exception: " & ex.Message)
End Try
Thanks a lot for all your help, marking this as solution.
If it does not bother you can you share this code in UiPath activities format i.e., using UiPath for each, assign and other activities as I already have some logic which is running for other things, using invoke code will disturb the flow and exceptional handling functionality.
It would be very helpful and kind to you if you can share it also in UiPath activity format.