Format unstructured text in structured and create a datatable with three columns

[Dhruvi_Arumugam]

Hi Everyone,

Can please someone help me in achieving the formatted text required . I am facing issues.

Below is the file of the input text:
Input.TXT (3.6 KB)

Below is the expected output I want
Output.TXT (852 Bytes)

Here in the Input:

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.

Thanks!!

[vrdabberu (Varunraj)]

Hi @Dhruvi_Arumugam

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

Below are the Invoked arguments:

=> Use Write Range Workbook to write the data table to excel.

Output:

Hope it helps!!
Regards

[Dhruvi_Arumugam]

Hi @vrdabberu

Thanks for your quick answer.

But I see some discrepancies. Can you please do a quick wrap over them.

1. No users connect to group should be column 3 that is Name
2. 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!!

[vrdabberu (Varunraj)]

Hi @Dhruvi_Arumugam

Use this code in Invoke Code acitivity:

' 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

the invoked arguments are the same.

Output:

Regards

[Dhruvi_Arumugam]

Hi @vrdabberu

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.

Please help me on that as well

@vrdabberu

ElseIf line.Contains("$DSNGRP") Or line.Contains("TestPRODUP") Or line.Contains("TestUTIL") Then

Let me know for any clarifications.

Thanks again for your efforts!!

[vrdabberu (Varunraj)]

Hi @Dhruvi_Arumugam

Use this 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 = ""
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

Invoked arguments are the same.

Output:

Hope it helps!!
Regards

[Dhruvi_Arumugam]

Hi @vrdabberu

Thanks I will Test and let you know the output.

Thanks a lot again !!

[Dhruvi_Arumugam]

Hi @vrdabberu

Getting below errors

Please suggest.

Thanks

[vrdabberu (Varunraj)]

Hi @Dhruvi_Arumugam

Go to Imports and import System.Data and Microsoft.Office.Interop.Excel. Could you share the screenshot of Invoked arguments too

Regards

[Dhruvi_Arumugam]

I already have

@vrdabberu

System.Data and Microsoft.Office.Interop.Excel.

in my imports panel, also I was missing creating out argument for outputdatatable variable, that I did.Now I am left with below error

Thanks

[Dhruvi_Arumugam]

Hi @vrdabberu

I fixed this using System.data.datatable using directly in invoke code

[vrdabberu (Varunraj)]

@Dhruvi_Arumugam

Try deleting that Import namespaces and check once. Share the screenshot of Invoked arguments

Regards

[Dhruvi_Arumugam]

I ran the code, it is only giving me 1021 rows as output in excel whereas I have around 13K rows in string.

Thanks

[vrdabberu (Varunraj)]

Hi @Dhruvi_Arumugam

Check out this zip file. Change your Input Text file and you can run your workflow.

BlankProcess6.zip (152.6 KB)

Regards,

[Dhruvi_Arumugam]

@Dhruvi_Arumugam

I ran the code, it is only giving me 1021 rows as output in excel whereas I have around 13K rows in string.

Thanks

Like I said I got to run it but.

[vrdabberu (Varunraj)]

@Dhruvi_Arumugam

Is it possible to share the file.

Regards

[Dhruvi_Arumugam]

Hi @vrdabberu

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.

Thanks!!

[vrdabberu (Varunraj)]

Hi @Dhruvi_Arumugam

Use the below Invoke Code

' 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
 

Below are the Invoked arguments

Regards

[Dhruvi_Arumugam]

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.

Thanks!!