Solved: Re: Extract text from Notepad

Th11 · ‎03-26-2024

Hi,

I am new to PAD. using PAD i have to extract certain data's from the notepad. I wonder is it possible with PAD?

Eg:

From this notepad i need to extract firstname, DOB and Message Text Data's. Single file can contain 50 different client details.Please help. Thanks in advance!

kinuasa · ‎03-27-2024

I will provide a similar answer as others, but I believe using the "Parse text" action and regular expressions would be effective.

SET Datatable TO { ^['Firstname', 'DOB', 'Lastname', 'Fileno', 'Message Text'] }
File.ReadTextFromFile.ReadText File: $'''C:\\Test\\Text\\SampleText.txt''' Encoding: File.TextFileEncoding.UTF8 Content=> FileContents
Text.ParseText.RegexParse Text: FileContents TextToFind: $'''(?<=Firstname:).*(?=DOB)''' StartingPosition: 0 IgnoreCase: False Matches=> FirstnameMatches
Text.ParseText.RegexParse Text: FileContents TextToFind: $'''(?<=DOB:).*''' StartingPosition: 0 IgnoreCase: False Matches=> DOBMatches
Text.ParseText.RegexParse Text: FileContents TextToFind: $'''(?<=Lastname:).*(?=Fileno)''' StartingPosition: 0 IgnoreCase: False Matches=> LastnameMatches
Text.ParseText.RegexParse Text: FileContents TextToFind: $'''(?<=Fileno:).*''' StartingPosition: 0 IgnoreCase: False Matches=> FilenoMatches
Text.ParseText.RegexParse Text: FileContents TextToFind: $'''(?<=Message Text:).*''' StartingPosition: 0 IgnoreCase: False Matches=> MessageTextMatches
LOOP LoopIndex FROM 0 TO FirstnameMatches.Count - 1 STEP 1
    Variables.AddRowToDataTable.AppendRowToDataTable DataTable: Datatable RowToAdd: [FirstnameMatches[LoopIndex].Trimmed, DOBMatches[LoopIndex].Trimmed, LastnameMatches[LoopIndex].Trimmed, FilenoMatches[LoopIndex].Trimmed, MessageTextMatches[LoopIndex].Trimmed]
END

View solution in original post

Deenuji · ‎03-27-2024

@Th11

Since you are using @kinuasa code. Just add one if condition inside the loop like below:

Code:

SET Datatable TO { ^['Firstname', 'DOB', 'Lastname', 'Fileno', 'Message Text'] }
File.ReadTextFromFile.ReadText File: $'''C:\\Users\\deenu\\OneDrive\\Desktop\\data.txt''' Encoding: File.TextFileEncoding.UTF8 Content=> FileContents
Text.ParseText.RegexParse Text: FileContents TextToFind: $'''(?<=Firstname:).*(?=DOB)''' StartingPosition: 0 IgnoreCase: False Matches=> FirstnameMatches
Text.ParseText.RegexParse Text: FileContents TextToFind: $'''(?<=DOB:).*''' StartingPosition: 0 IgnoreCase: False Matches=> DOBMatches
Text.ParseText.RegexParse Text: FileContents TextToFind: $'''(?<=Lastname:).*(?=Fileno)''' StartingPosition: 0 IgnoreCase: False Matches=> LastnameMatches
Text.ParseText.RegexParse Text: FileContents TextToFind: $'''(?<=Fileno:).*''' StartingPosition: 0 IgnoreCase: False Matches=> FilenoMatches
Text.ParseText.RegexParse Text: FileContents TextToFind: $'''(?<=Message Text:).*''' StartingPosition: 0 IgnoreCase: False Matches=> MessageTextMatches
Text.ParseText.Parse Text: MessageTextMatches TextToFind: $'''Rejected''' StartingPosition: 0 IgnoreCase: False OccurrencePositions=> RejectedPositions
LOOP LoopIndex FROM 0 TO FirstnameMatches.Count - 1 STEP 1
    IF MessageTextMatches[LoopIndex].Trimmed = $'''Rejected''' THEN
        Variables.AddRowToDataTable.AppendRowToDataTable DataTable: Datatable RowToAdd: [FirstnameMatches[LoopIndex].Trimmed, DOBMatches[LoopIndex].Trimmed, LastnameMatches[LoopIndex].Trimmed, FilenoMatches[LoopIndex].Trimmed, MessageTextMatches[LoopIndex].Trimmed]
    END
END

Thanks,
Deenuji Loganathan 👩‍💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀. If you'd like to appreciate me, please write a LinkedIn recommendation 🙏

View solution in original post

kinuasa · ‎04-12-2024

@Th11

The following is a sample that stores a list for each person’s data and stores it in a data table using regular expressions.

SampleText.txt

Firstname:sita	DOB: 3/5/24
Lastname:Rama	Fileno:12345
Message Text:Accepted
------------------------------
Firstname:John	DOB: 3/3/24
Lastname:catch	Fileno:88888
Message Text:Rejected
------------------------------
Firstname:Smith	DOB: 3/3/33
Lastname:Test	Fileno:88000
Message Text:Rejected
------------------------------
Message Text:Rejected for this client because he did notpay the bill
------------------------------
Firstname:Janav	DOB: 8/9/35
Lastname:Micheal	Fileno:33333
Message Text:Accepted
------------------------------

Desktop flow

**REGION SetList
Text.Replace Text: $'''NEWLINE''' TextToFind: $'''NEWLINE''' IsRegEx: False IgnoreCase: False ReplaceWith: $'''\\n''' ActivateEscapeSequences: True Result=> NL
Variables.CreateNewList List=> List
DISABLE Variables.AddItemToList Item: $'''%''%''' List: List
File.ReadTextFromFile.ReadText File: $'''C:\\Test\\Text\\SampleText.txt''' Encoding: File.TextFileEncoding.UTF8 Content=> FileContents
Text.SplitText.Split Text: FileContents StandardDelimiter: Text.StandardDelimiter.NewLine DelimiterTimes: 1 Result=> TextList
LOOP LoopIndex FROM 0 TO TextList.Count - 1 STEP 1
    Text.Replace Text: TextList[LoopIndex] TextToFind: $'''-{1,}''' IsRegEx: True IgnoreCase: False ReplaceWith: $'''%''%''' ActivateEscapeSequences: False Result=> Replaced
    IF Replaced.IsEmpty = $'''False''' THEN
        Text.ParseText.ParseForFirstOccurrence Text: TextList[LoopIndex] TextToFind: $'''Firstname:''' StartingPosition: 0 IgnoreCase: True OccurrencePosition=> Position
        IF Position >= 0 THEN
            Variables.AddItemToList Item: TextList[LoopIndex] List: List
        ELSE
            SET List[List.Count - 1] TO $'''%List[List.Count - 1]%%NL%%TextList[LoopIndex]%'''
        END
    END
END
**ENDREGION
**REGION SetDatatable
SET Datatable TO { ^['Firstname', 'DOB', 'Lastname', 'Fileno', 'Message Text1', 'Message Text2'] }
LOOP LoopIndex FROM 0 TO List.Count - 1 STEP 1
    Text.ParseText.RegexParse Text: List[LoopIndex] TextToFind: $'''(?<=Firstname:).*(?=DOB)''' StartingPosition: 0 IgnoreCase: False Matches=> FirstnameMatches
    Text.ParseText.RegexParse Text: List[LoopIndex] TextToFind: $'''(?<=DOB:).*''' StartingPosition: 0 IgnoreCase: False Matches=> DOBMatches
    Text.ParseText.RegexParse Text: List[LoopIndex] TextToFind: $'''(?<=Lastname:).*(?=Fileno)''' StartingPosition: 0 IgnoreCase: False Matches=> LastnameMatches
    Text.ParseText.RegexParse Text: List[LoopIndex] TextToFind: $'''(?<=Fileno:).*''' StartingPosition: 0 IgnoreCase: False Matches=> FilenoMatches
    Text.ParseText.RegexParse Text: List[LoopIndex] TextToFind: $'''(?<=Message Text:).*''' StartingPosition: 0 IgnoreCase: False Matches=> MessageTextMatches
    IF MessageTextMatches.Count = 2 THEN
        Variables.AddRowToDataTable.AppendRowToDataTable DataTable: Datatable RowToAdd: [FirstnameMatches[0].Trimmed, DOBMatches[0].Trimmed, LastnameMatches[0].Trimmed, FilenoMatches[0].Trimmed, MessageTextMatches[0].Trimmed, MessageTextMatches[1].Trimmed]
    ELSE
        Variables.AddRowToDataTable.AppendRowToDataTable DataTable: Datatable RowToAdd: [FirstnameMatches[0].Trimmed, DOBMatches[0].Trimmed, LastnameMatches[0].Trimmed, FilenoMatches[0].Trimmed, MessageTextMatches[0].Trimmed, '']
    END
END
**ENDREGION

View solution in original post

kinuasa · ‎04-24-2024

@Th11 wrote:

If I copy and paste it into a notepad and deleted the data before firstname then it is working perfectly.

I think it would be better to use the “Parse text” action to get only “Firstname” and after.

Text.Replace Text: $'''NEWLINE''' TextToFind: $'''NEWLINE''' IsRegEx: False IgnoreCase: False ReplaceWith: $'''\\n''' ActivateEscapeSequences: True Result=> NL
**REGION SetList
Variables.CreateNewList List=> List
File.ReadTextFromFile.ReadText File: $'''C:\\Test\\Text\\SampleText2.txt''' Encoding: File.TextFileEncoding.UTF8 Content=> FileContents
Text.ParseText.RegexParse Text: FileContents TextToFind: $'''Firstname[\\s\\S]*''' StartingPosition: 0 IgnoreCase: False Matches=> Matches
SET FileContents TO Matches[0]
Text.SplitText.Split Text: FileContents StandardDelimiter: Text.StandardDelimiter.NewLine DelimiterTimes: 1 Result=> TextList
LOOP LoopIndex FROM 0 TO TextList.Count - 1 STEP 1
    Text.Replace Text: TextList[LoopIndex] TextToFind: $'''-{1,}''' IsRegEx: True IgnoreCase: False ReplaceWith: $'''%''%''' ActivateEscapeSequences: False Result=> Replaced
    IF Replaced.IsEmpty = $'''False''' THEN
        Text.ParseText.ParseForFirstOccurrence Text: TextList[LoopIndex] TextToFind: $'''Firstname:''' StartingPosition: 0 IgnoreCase: True OccurrencePosition=> Position
        IF Position >= 0 THEN
            Variables.AddItemToList Item: TextList[LoopIndex] List: List
        ELSE
            SET List[List.Count - 1] TO $'''%List[List.Count - 1]%%NL%%TextList[LoopIndex]%'''
        END
    END
END
**ENDREGION
**REGION SetDatatable
SET Datatable TO { ^['Firstname', 'DOB', 'Lastname', 'Fileno', 'Message Text'] }
LOOP LoopIndex FROM 0 TO List.Count - 1 STEP 1
    SET MessageText TO $'''%''%'''
    Text.ParseText.RegexParse Text: List[LoopIndex] TextToFind: $'''(?<=Firstname:).*(?=DOB)''' StartingPosition: 0 IgnoreCase: False Matches=> FirstnameMatches
    Text.ParseText.RegexParse Text: List[LoopIndex] TextToFind: $'''(?<=DOB:).*''' StartingPosition: 0 IgnoreCase: False Matches=> DOBMatches
    Text.ParseText.RegexParse Text: List[LoopIndex] TextToFind: $'''(?<=Lastname:).*(?=Fileno)''' StartingPosition: 0 IgnoreCase: False Matches=> LastnameMatches
    Text.ParseText.RegexParse Text: List[LoopIndex] TextToFind: $'''(?<=Fileno:).*''' StartingPosition: 0 IgnoreCase: False Matches=> FilenoMatches
    Text.ParseText.RegexParse Text: List[LoopIndex] TextToFind: $'''(?<=Message Text:).*''' StartingPosition: 0 IgnoreCase: False Matches=> MessageTextMatches
    IF MessageTextMatches.Count > 0 THEN
        LOOP LoopIndex2 FROM 0 TO MessageTextMatches.Count - 1 STEP 1
            IF LoopIndex2 = 0 THEN
                SET MessageText TO MessageTextMatches[LoopIndex2].Trimmed
            ELSE
                SET MessageText TO $'''%MessageText%%NL%%MessageTextMatches[LoopIndex2].Trimmed%'''
            END
        END
    END
    Variables.AddRowToDataTable.AppendRowToDataTable DataTable: Datatable RowToAdd: [FirstnameMatches[0].Trimmed, DOBMatches[0].Trimmed, LastnameMatches[0].Trimmed, FilenoMatches[0].Trimmed, MessageText]
END
**ENDREGION

View solution in original post

Deenuji · ‎03-26-2024

@Th11

I am suggesting .net script based solution for your use case.

My flow screenshot:

PAD action Explanation:

Variables.CreateNewDatatable InputTable:

Create a new DataTable named DataTable with column names Firstname, DOB, and Message Text. Initialize it with an empty row.

Scripting.RunDotNetScript:

mention the System.Text.RegularExpressions namespace.
Define a C# script that performs the actions required action: reading the text file, defining the regular expression pattern, matching the pattern against the text, removing the first row from the DataTable, and adding extracted data to the DataTable.
The script receives filename and dataTable as inputs and updates the dataTable with extracted data.
The input and output parameters are specified in the configuration of the action.

Code[Change the input file parameter alone]:

Variables.CreateNewDatatable InputTable: { ^['Firstname', 'DOB', 'Message Text'], [$'''''', $'''''', $''''''] } DataTable=> DataTable
Scripting.RunDotNetScript Imports: $'''System.Text.RegularExpressions''' Language: System.DotNetActionLanguageType.CSharp Script: $'''// Read the text file
string text = File.ReadAllText(filename);

// Define the regular expression pattern
string pattern = @\"Firstname: (?<firstname>\\w+)\\s+DOB: (?<dob>\\d{2}/\\d{2}/\\d{2})\\r?\\nLastname: \\w+\\s+Fileno: \\d+\\r?\\nMessage Text: (?<message>.+?)\\s+\";

// Match the pattern against the text
MatchCollection matches = Regex.Matches(text, pattern);

 // Remove the first row from the DataTable
        if (dataTable.Rows.Count > 0)
        {
            dataTable.Rows.RemoveAt(0);
        }
        
// Extract the required information and add to DataTable
foreach (Match match in matches)
{
    string firstname = match.Groups[\"firstname\"].Value;
    string dobString = match.Groups[\"dob\"].Value;
    string message = match.Groups[\"message\"].Value;

    // Add the extracted data to the DataTable
    dataTable.Rows.Add(firstname, dobString, message);
}''' @'name:dataTable': DataTable @'type:dataTable': $'''Datatable''' @'direction:dataTable': $'''InOut''' @'name:filename': $'''C:\\\\Users\\\\deenu\\\\OneDrive\\\\Desktop\\\\data.txt''' @'type:filename': $'''String''' @'direction:filename': $'''In''' @dataTable=> DataTable

New to PAD, Not sure how to copy/paste above code?

Please let me know in case if you have any queries.

Thanks,
Deenuji Loganathan 👩‍💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀. If you'd like to appreciate me, please write a LinkedIn recommendation 🙏

VishnuReddy1997 · ‎03-26-2024

Hi @Th11 ,

I have created a flow with 2 different approaches.

Approach 1:

Code:

File.ReadTextFromFile.ReadText File: $'''C:\\Users\\Downloads\\input 1.txt''' Encoding: File.TextFileEncoding.UTF8 Content=> FileContents
Text.ParseText.RegexParse Text: FileContents TextToFind: $'''(?<=Firstname:\\s)\\w+''' StartingPosition: 0 IgnoreCase: False Matches=> FirstName
Text.ParseText.RegexParse Text: FileContents TextToFind: $'''(?<=DOB:)\\d+\\/\\d+\\/\\d+''' StartingPosition: 0 IgnoreCase: False Matches=> Dob
Text.ParseText.RegexParse Text: FileContents TextToFind: $'''(?<=Lastname\\s:\\s)\\w+''' StartingPosition: 0 IgnoreCase: False Matches=> lastname
Text.ParseText.RegexParse Text: FileContents TextToFind: $'''(?<=Fileno:\\s)\\d+''' StartingPosition: 0 IgnoreCase: False Matches=> FileNo
Text.ParseText.RegexParse Text: FileContents TextToFind: $'''(?<=Message Text:\\s)(\\w*\\s\\w+|\\w+)''' StartingPosition: 0 IgnoreCase: False Matches=> MessageText

Approach 2:

Code:

File.ReadTextFromFile.ReadTextAsList File: $'''C:\\Users\\OneDrive\\Desktop\\Power Automate Desktop\\Practice\\Text File\\input.txt''' Encoding: File.TextFileEncoding.UTF8 Contents=> FileContents
LOOP FOREACH CurrentItem IN FileContents
IF Contains(CurrentItem, $'''Firstname''', False) THEN
Text.SplitText.Split Text: CurrentItem StandardDelimiter: Text.StandardDelimiter.Space DelimiterTimes: 1 Result=> TextList
Display.ShowMessageDialog.ShowMessage Title: $'''FirstName''' Message: TextList[1] Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False
Text.SplitText.SplitWithDelimiter Text: TextList[5] CustomDelimiter: $''':''' IsRegEx: False Result=> DOB
Display.ShowMessageDialog.ShowMessage Title: $'''Date Of Birth''' Message: DOB[1] Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False
END
IF Contains(CurrentItem, $'''Message''', False) THEN
Text.SplitText.SplitWithDelimiter Text: CurrentItem CustomDelimiter: $''':''' IsRegEx: False Result=> Message_Text
Display.ShowMessageDialog.ShowMessage Title: $'''Message Text''' Message: Message_Text[1] Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False
END
END

(Note:- if you got your solution you can mark as solution and gives kudos)

Thanks & Regards

Vishnu Reddy

kinuasa · ‎03-27-2024

I will provide a similar answer as others, but I believe using the "Parse text" action and regular expressions would be effective.

SET Datatable TO { ^['Firstname', 'DOB', 'Lastname', 'Fileno', 'Message Text'] }
File.ReadTextFromFile.ReadText File: $'''C:\\Test\\Text\\SampleText.txt''' Encoding: File.TextFileEncoding.UTF8 Content=> FileContents
Text.ParseText.RegexParse Text: FileContents TextToFind: $'''(?<=Firstname:).*(?=DOB)''' StartingPosition: 0 IgnoreCase: False Matches=> FirstnameMatches
Text.ParseText.RegexParse Text: FileContents TextToFind: $'''(?<=DOB:).*''' StartingPosition: 0 IgnoreCase: False Matches=> DOBMatches
Text.ParseText.RegexParse Text: FileContents TextToFind: $'''(?<=Lastname:).*(?=Fileno)''' StartingPosition: 0 IgnoreCase: False Matches=> LastnameMatches
Text.ParseText.RegexParse Text: FileContents TextToFind: $'''(?<=Fileno:).*''' StartingPosition: 0 IgnoreCase: False Matches=> FilenoMatches
Text.ParseText.RegexParse Text: FileContents TextToFind: $'''(?<=Message Text:).*''' StartingPosition: 0 IgnoreCase: False Matches=> MessageTextMatches
LOOP LoopIndex FROM 0 TO FirstnameMatches.Count - 1 STEP 1
    Variables.AddRowToDataTable.AppendRowToDataTable DataTable: Datatable RowToAdd: [FirstnameMatches[LoopIndex].Trimmed, DOBMatches[LoopIndex].Trimmed, LastnameMatches[LoopIndex].Trimmed, FilenoMatches[LoopIndex].Trimmed, MessageTextMatches[LoopIndex].Trimmed]
END

Th11 · ‎03-27-2024

Hi @kinuasa ,

Thank you so much for your response. Its really helpful. From the above notepad example, I need to extract only the Message text value is "Rejected" and the corresponding First name, Last name ,DOB , file no. can you please help me how to achieve it. Thanks!

Deenuji · ‎03-27-2024

@Th11

Since you are using @kinuasa code. Just add one if condition inside the loop like below:

Code:

SET Datatable TO { ^['Firstname', 'DOB', 'Lastname', 'Fileno', 'Message Text'] }
File.ReadTextFromFile.ReadText File: $'''C:\\Users\\deenu\\OneDrive\\Desktop\\data.txt''' Encoding: File.TextFileEncoding.UTF8 Content=> FileContents
Text.ParseText.RegexParse Text: FileContents TextToFind: $'''(?<=Firstname:).*(?=DOB)''' StartingPosition: 0 IgnoreCase: False Matches=> FirstnameMatches
Text.ParseText.RegexParse Text: FileContents TextToFind: $'''(?<=DOB:).*''' StartingPosition: 0 IgnoreCase: False Matches=> DOBMatches
Text.ParseText.RegexParse Text: FileContents TextToFind: $'''(?<=Lastname:).*(?=Fileno)''' StartingPosition: 0 IgnoreCase: False Matches=> LastnameMatches
Text.ParseText.RegexParse Text: FileContents TextToFind: $'''(?<=Fileno:).*''' StartingPosition: 0 IgnoreCase: False Matches=> FilenoMatches
Text.ParseText.RegexParse Text: FileContents TextToFind: $'''(?<=Message Text:).*''' StartingPosition: 0 IgnoreCase: False Matches=> MessageTextMatches
Text.ParseText.Parse Text: MessageTextMatches TextToFind: $'''Rejected''' StartingPosition: 0 IgnoreCase: False OccurrencePositions=> RejectedPositions
LOOP LoopIndex FROM 0 TO FirstnameMatches.Count - 1 STEP 1
    IF MessageTextMatches[LoopIndex].Trimmed = $'''Rejected''' THEN
        Variables.AddRowToDataTable.AppendRowToDataTable DataTable: Datatable RowToAdd: [FirstnameMatches[LoopIndex].Trimmed, DOBMatches[LoopIndex].Trimmed, LastnameMatches[LoopIndex].Trimmed, FilenoMatches[LoopIndex].Trimmed, MessageTextMatches[LoopIndex].Trimmed]
    END
END

Thanks,
Deenuji Loganathan 👩‍💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀. If you'd like to appreciate me, please write a LinkedIn recommendation 🙏

Th11 · ‎04-01-2024

Hi @Deenuji ,

Thanks for your response. From the below example i need to extract the Data's who is having the "Message Text" value. I tried. it throws me error says that Index "LoopIndex" is out of range. could you please guide me. Thanks!

I need to extract only the data(Firstname, Lastname, DOB, Fileno, Message Text) who is having the Message Text as a keyword.

Deenuji · ‎04-01-2024

@Th11

Please try the below approach[Of course we need to think of optimize the code in case if its take more time for you]:

Code:

SET DataTable TO { ^['Firstname', 'DOB', 'Lastname', 'Fileno', 'Message Text'] }
File.ReadTextFromFile.ReadTextAsList File: $'''C:\\Users\\deenu\\OneDrive\\Desktop\\data.txt''' Encoding: File.TextFileEncoding.UTF8 Contents=> FileContents
LOOP FOREACH CurrentItem IN FileContents
    IF (IsNotEmpty(CurrentItem.Trimmed) AND NotStartsWith(CurrentItem.Trimmed, $'''--''', False)) = True THEN
        Text.ParseText.RegexParse Text: CurrentItem TextToFind: $'''(?<=Firstname:)(.*?)(?=DOB)''' StartingPosition: 0 IgnoreCase: False Matches=> FirstnameMatches
        IF FirstnameMatches.Count > 0 THEN
            SET Strfirstname TO FirstnameMatches[0]
        END
        Text.ParseText.RegexParse Text: CurrentItem TextToFind: $'''(?<=DOB:)(.*?)\\s''' StartingPosition: 0 IgnoreCase: False Matches=> DOBMatches
        IF DOBMatches.Count > 0 THEN
            SET StrDOB TO DOBMatches[0]
        END
        Text.ParseText.RegexParse Text: CurrentItem TextToFind: $'''(?<=Lastname:)(.*?)(?=Fileno)''' StartingPosition: 0 IgnoreCase: True Matches=> LastnameMatches
        IF LastnameMatches.Count > 0 THEN
            SET Strlastname TO LastnameMatches[0]
        END
        Text.ParseText.RegexParse Text: CurrentItem TextToFind: $'''(?<=Fileno:)(.*?)\\s''' StartingPosition: 0 IgnoreCase: False Matches=> FilenoMatches
        IF FilenoMatches.Count > 0 THEN
            SET Strfileno TO FilenoMatches[0]
        END
        IF StartsWith(CurrentItem, $'''Message Text''', False) THEN
            Text.ParseText.RegexParse Text: CurrentItem TextToFind: $'''(?<=Message Text:)(.*?)\\s''' StartingPosition: 0 IgnoreCase: False Matches=> MessageTextMatches
            Variables.AddRowToDataTable.AppendRowToDataTable DataTable: DataTable RowToAdd: [Strfirstname.Trimmed, StrDOB.Trimmed, Strlastname.Trimmed, Strfileno.Trimmed, MessageTextMatches[0].Trimmed]
            SET Strfirstname TO $'''%''%'''
            SET StrDOB TO $'''%''%'''
            SET Strlastname TO $'''%''%'''
            SET Strfileno TO $'''%''%'''
            SET Strmessage TO $'''%''%'''
        END
    END
END

Thanks,
Deenuji Loganathan 👩‍💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀. If you'd like to appreciate me, please write a LinkedIn recommendation 🙏

Th11 · ‎04-11-2024

Hi @Deenuji , The above solution is working perfectly. But i am facing one problem , for the same client there is 2 Message text is there. when the bot is running it is throwing the error. In the output it should include the message text value into the datatable along with the previous client details. Please guide me.

Eg:

For ex, For "Smith" There is 2 Message Text Value. In the datatable under Smith it has to be like

Firstname Lastname DOB Fileno MessageText

Smith Test 33333 88000 Rejected ,

Rejected for this client because he did not pay the bill

Deenuji · ‎04-11-2024

@Th11

I would suggest to go with .net script for the same:

Flow Screenshot:

Flow Source code:

SET dataTable TO { ^['Firstname', 'DOB', 'Lastname', 'Fileno', 'Message Text'] }
SET FilePath TO $'''C:\\Users\\deenu\\OneDrive\\Desktop\\data.txt'''
Scripting.RunDotNetScript Imports: $'''System.Data
System.Text.RegularExpressions''' Language: System.DotNetActionLanguageType.CSharp Script: $'''List<string> fileContents = new List<string>(File.ReadAllLines(@\"%FilePath%\"));
        int counter = 0;
        string strFirstname = \"\";
        string strDOB = \"\";
        string strLastname = \"\";
        string strFileno = \"\";

        foreach (string currentItem in fileContents)
        {
            if (!string.IsNullOrWhiteSpace(currentItem) && !currentItem.TrimStart().StartsWith(\"--\"))
            {
                if (string.IsNullOrEmpty(strFirstname))
                {
                    strFirstname = Regex.Match(currentItem, @\"(?<=Firstname:)(.*?)(?=DOB)\").Value.Trim();
                }
                if (string.IsNullOrEmpty(strDOB))
                {
                    strDOB = Regex.Match(currentItem, @\"(?<=DOB:\\s*)\\d{2}/\\d{2}/\\d{2}\").Value.Trim();
                }
                if (string.IsNullOrEmpty(strLastname))
                {
                    strLastname = Regex.Match(currentItem, @\"(?<=Lastname:)(.*?)(?=Fileno)\").Value.Trim();
                }
                if (string.IsNullOrEmpty(strFileno))
                {
                    strFileno = Regex.Match(currentItem, @\"(?<=Fileno:\\s*)\\d+\").Value.Trim();
                }

                if (currentItem.StartsWith(\"Message Text\"))
                {
                    string messageText = Regex.Match(currentItem, @\"(?<=Message Text:\\s*).*$\").Value.Trim();
                    string nextElement = counter + 1 < fileContents.Count ? fileContents[counter + 1] : \"\";
                    string doubleNextElement = counter + 2 < fileContents.Count ? fileContents[counter + 2] : \"\";

                    if (nextElement.StartsWith(\"Message Text\") || doubleNextElement.StartsWith(\"Message Text\"))
                    {
                        if (nextElement.StartsWith(\"Message Text\"))
                        {
                            messageText += Regex.Match(nextElement, @\"(?<=Message Text:\\s*).*$\").Value.Trim();
                        }
                        else if (doubleNextElement.StartsWith(\"Message Text\"))
                        {
                            messageText += \",\" + Regex.Match(doubleNextElement, @\"(?<=Message Text:\\s*).*$\").Value.Trim();
                        }
                    }

                    if (!string.IsNullOrWhiteSpace(strFirstname))
                    {
                        DataRow row = dataTable.NewRow();
                        row[\"Firstname\"] = strFirstname;
                        row[\"DOB\"] = strDOB;
                        row[\"Lastname\"] = strLastname;
                        row[\"Fileno\"] = strFileno;
                        row[\"Message Text\"] = messageText;
                        dataTable.Rows.Add(row);
                    }

                    strFirstname = strDOB = strLastname = strFileno = messageText = \"\";
                }
            }

            counter++;
        }''' @'name:dataTable': dataTable @'type:dataTable': $'''Datatable''' @'direction:dataTable': $'''InOut''' @dataTable=> dataTable

How to copy/paste the above code into your PAD?

Thanks,
Deenuji Loganathan 👩‍💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀

kinuasa · ‎04-12-2024

@Th11

The following is a sample that stores a list for each person’s data and stores it in a data table using regular expressions.

SampleText.txt

Firstname:sita	DOB: 3/5/24
Lastname:Rama	Fileno:12345
Message Text:Accepted
------------------------------
Firstname:John	DOB: 3/3/24
Lastname:catch	Fileno:88888
Message Text:Rejected
------------------------------
Firstname:Smith	DOB: 3/3/33
Lastname:Test	Fileno:88000
Message Text:Rejected
------------------------------
Message Text:Rejected for this client because he did notpay the bill
------------------------------
Firstname:Janav	DOB: 8/9/35
Lastname:Micheal	Fileno:33333
Message Text:Accepted
------------------------------

Desktop flow

**REGION SetList
Text.Replace Text: $'''NEWLINE''' TextToFind: $'''NEWLINE''' IsRegEx: False IgnoreCase: False ReplaceWith: $'''\\n''' ActivateEscapeSequences: True Result=> NL
Variables.CreateNewList List=> List
DISABLE Variables.AddItemToList Item: $'''%''%''' List: List
File.ReadTextFromFile.ReadText File: $'''C:\\Test\\Text\\SampleText.txt''' Encoding: File.TextFileEncoding.UTF8 Content=> FileContents
Text.SplitText.Split Text: FileContents StandardDelimiter: Text.StandardDelimiter.NewLine DelimiterTimes: 1 Result=> TextList
LOOP LoopIndex FROM 0 TO TextList.Count - 1 STEP 1
    Text.Replace Text: TextList[LoopIndex] TextToFind: $'''-{1,}''' IsRegEx: True IgnoreCase: False ReplaceWith: $'''%''%''' ActivateEscapeSequences: False Result=> Replaced
    IF Replaced.IsEmpty = $'''False''' THEN
        Text.ParseText.ParseForFirstOccurrence Text: TextList[LoopIndex] TextToFind: $'''Firstname:''' StartingPosition: 0 IgnoreCase: True OccurrencePosition=> Position
        IF Position >= 0 THEN
            Variables.AddItemToList Item: TextList[LoopIndex] List: List
        ELSE
            SET List[List.Count - 1] TO $'''%List[List.Count - 1]%%NL%%TextList[LoopIndex]%'''
        END
    END
END
**ENDREGION
**REGION SetDatatable
SET Datatable TO { ^['Firstname', 'DOB', 'Lastname', 'Fileno', 'Message Text1', 'Message Text2'] }
LOOP LoopIndex FROM 0 TO List.Count - 1 STEP 1
    Text.ParseText.RegexParse Text: List[LoopIndex] TextToFind: $'''(?<=Firstname:).*(?=DOB)''' StartingPosition: 0 IgnoreCase: False Matches=> FirstnameMatches
    Text.ParseText.RegexParse Text: List[LoopIndex] TextToFind: $'''(?<=DOB:).*''' StartingPosition: 0 IgnoreCase: False Matches=> DOBMatches
    Text.ParseText.RegexParse Text: List[LoopIndex] TextToFind: $'''(?<=Lastname:).*(?=Fileno)''' StartingPosition: 0 IgnoreCase: False Matches=> LastnameMatches
    Text.ParseText.RegexParse Text: List[LoopIndex] TextToFind: $'''(?<=Fileno:).*''' StartingPosition: 0 IgnoreCase: False Matches=> FilenoMatches
    Text.ParseText.RegexParse Text: List[LoopIndex] TextToFind: $'''(?<=Message Text:).*''' StartingPosition: 0 IgnoreCase: False Matches=> MessageTextMatches
    IF MessageTextMatches.Count = 2 THEN
        Variables.AddRowToDataTable.AppendRowToDataTable DataTable: Datatable RowToAdd: [FirstnameMatches[0].Trimmed, DOBMatches[0].Trimmed, LastnameMatches[0].Trimmed, FilenoMatches[0].Trimmed, MessageTextMatches[0].Trimmed, MessageTextMatches[1].Trimmed]
    ELSE
        Variables.AddRowToDataTable.AppendRowToDataTable DataTable: Datatable RowToAdd: [FirstnameMatches[0].Trimmed, DOBMatches[0].Trimmed, LastnameMatches[0].Trimmed, FilenoMatches[0].Trimmed, MessageTextMatches[0].Trimmed, '']
    END
END
**ENDREGION

Alex256 · ‎04-12-2024

So If you don't want to use script you can use my way.

First when you get content of the file you can split it into a list:

Then you could use for loop to iterate through the list in order to get the information.

In the for each loop use will have an if condition to check where the item is empty

and then you could search for if the item contains other information or just the additional message.

After that is the same list of actions to extract the information

And add that information to the table.

In that If action you need an else action so you can add the additional message to the table:

So in the Else action you first extract the message then update the last row in the table with the update data table item

Th11 · ‎04-15-2024

Hi @kinuasa ,

Thank you so much for your response. Its really helpful. Now I am facing a challenge for some of the file Data's. Inside a folder there might be plenty of files and each file has different Number of "Message Text" value. The Bot has to extract all the "Message Text" data related to the person. But not the "Message code" Data related person details.

Eg:
Firstname: sita DOB:3/5/2023
Lastname: Rama Fileno:12322
Message Text: Accepted
-------------------------------
Firstname: Peter DOB:3/5/2027
Lastname: Test1 Fileno:12888
Message Code: Error has occured
-------------------------------
Firstname: smith DOB:3/5/2029
Lastname: Test2 Fileno:99999
Message Text: Accepted
-----------------------------
Message code: NA Type: Q
Message Text: processed for next level.
---------------------------------------
Message code: NA Type: E
Message Text: All the levels are done successfully.
-------------------------------------------
Firstname: Janav DOB:3/9/2023
Lastname: Test3 Fileno:17777
Message Text: Rejected
-----------------------------------
Message code: NA Type: I
Message Text: Sorry, All the levels are rejected.
-------------------------------------------------

I am trying to write the data table value into the excel. so in excel i just want to write the Message Text value into single column. Because i don't know how many "Message Text" value will be there for each person.

The output should be

Firstname Lastname DOB Fileno MessageText
sita Rama 3/5/2023 12322 Accepted

smith Test2 3/5/2029 99999 Accepted
processed for next level.
All the levels are done suceesfully

Janav Test3 3/9/2023 17777 Rejected
Sorry, All the levels are rejected.

Kindly help. Thanks in advance

kinuasa · ‎04-15-2024

A simple modification would be as follows:

SampleText.txt

Firstname:sita	DOB: 3/5/24
Lastname:Rama	Fileno:12345
Message Text:Accepted
------------------------------
Firstname: Peter	DOB:3/5/27
Lastname: Test1	Fileno:12888
Message Code:Error has occured
------------------------------
Firstname: smith	DOB:3/5/29
Lastname: Test2	Fileno:99999
Message Text:Accepted
-----------------------------
Message code:NA Type: Q
Message Text:processed for next level.
-----------------------------
Message code:NA Type: E
Message Text:All the levels are done successfully.
-----------------------------
Firstname:John	DOB: 3/3/24
Lastname:catch	Fileno:88888
Message Text:Rejected
------------------------------
Firstname:Smith	DOB: 3/3/33
Lastname:Test	Fileno:88000
Message Text:Rejected
------------------------------
Message Text:Rejected for this client because he did notpay the bill
------------------------------
Firstname:Janav	DOB: 8/9/35
Lastname:Micheal	Fileno:33333
Message Text:Accepted
------------------------------
Message code:NA Type: I
Message Text:Sorry, All the levels are rejected.
------------------------------

Desktop flow

Text.Replace Text: $'''NEWLINE''' TextToFind: $'''NEWLINE''' IsRegEx: False IgnoreCase: False ReplaceWith: $'''\\n''' ActivateEscapeSequences: True Result=> NL
**REGION SetList
Variables.CreateNewList List=> List
DISABLE Variables.AddItemToList Item: $'''%''%''' List: List
File.ReadTextFromFile.ReadText File: $'''C:\\Test\\Text\\SampleText.txt''' Encoding: File.TextFileEncoding.UTF8 Content=> FileContents
Text.SplitText.Split Text: FileContents StandardDelimiter: Text.StandardDelimiter.NewLine DelimiterTimes: 1 Result=> TextList
LOOP LoopIndex FROM 0 TO TextList.Count - 1 STEP 1
    Text.Replace Text: TextList[LoopIndex] TextToFind: $'''-{1,}''' IsRegEx: True IgnoreCase: False ReplaceWith: $'''%''%''' ActivateEscapeSequences: False Result=> Replaced
    IF Replaced.IsEmpty = $'''False''' THEN
        Text.ParseText.ParseForFirstOccurrence Text: TextList[LoopIndex] TextToFind: $'''Firstname:''' StartingPosition: 0 IgnoreCase: True OccurrencePosition=> Position
        IF Position >= 0 THEN
            Variables.AddItemToList Item: TextList[LoopIndex] List: List
        ELSE
            SET List[List.Count - 1] TO $'''%List[List.Count - 1]%%NL%%TextList[LoopIndex]%'''
        END
    END
END
**ENDREGION
**REGION SetDatatable
SET Datatable TO { ^['Firstname', 'DOB', 'Lastname', 'Fileno', 'Message Text'] }
LOOP LoopIndex FROM 0 TO List.Count - 1 STEP 1
    SET MessageText TO $'''%''%'''
    Text.ParseText.RegexParse Text: List[LoopIndex] TextToFind: $'''(?<=Firstname:).*(?=DOB)''' StartingPosition: 0 IgnoreCase: False Matches=> FirstnameMatches
    Text.ParseText.RegexParse Text: List[LoopIndex] TextToFind: $'''(?<=DOB:).*''' StartingPosition: 0 IgnoreCase: False Matches=> DOBMatches
    Text.ParseText.RegexParse Text: List[LoopIndex] TextToFind: $'''(?<=Lastname:).*(?=Fileno)''' StartingPosition: 0 IgnoreCase: False Matches=> LastnameMatches
    Text.ParseText.RegexParse Text: List[LoopIndex] TextToFind: $'''(?<=Fileno:).*''' StartingPosition: 0 IgnoreCase: False Matches=> FilenoMatches
    Text.ParseText.RegexParse Text: List[LoopIndex] TextToFind: $'''(?<=Message Text:).*''' StartingPosition: 0 IgnoreCase: False Matches=> MessageTextMatches
    IF MessageTextMatches.Count > 0 THEN
        LOOP LoopIndex2 FROM 0 TO MessageTextMatches.Count - 1 STEP 1
            IF LoopIndex2 = 0 THEN
                SET MessageText TO MessageTextMatches[LoopIndex2].Trimmed
            ELSE
                SET MessageText TO $'''%MessageText%%NL%%MessageTextMatches[LoopIndex2].Trimmed%'''
            END
        END
    END
    Variables.AddRowToDataTable.AppendRowToDataTable DataTable: Datatable RowToAdd: [FirstnameMatches[0].Trimmed, DOBMatches[0].Trimmed, LastnameMatches[0].Trimmed, FilenoMatches[0].Trimmed, MessageText]
END
**ENDREGION

Th11 · ‎04-17-2024

Hi @kinuasa , Thank you so much for your response and time. It is working fine for the sample which i have shared before. But in real scenario the file structure looks like below mentioned one. Before "Firstname" it has some data. So It is not creating a list. Its throwing the error( Index 'List.Count-1' is not valid).

In my real scenario the file extension is not .txt. The file extension is .ebt. So in the Split text action it is not splitting the files by newline. The "TextList " variable has all the data inside a single Index. If I copy and paste it into a notepad and deleted the data before firstname then it is working perfectly. Since i am very new to PAD struggling to find the exact answer. Please help. Thanks in advance.

Example:

Available Customer ID:11111

Available Customer Report

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

Date Received:4/17/24 Time Received:11.11.13.023

American Batch ID:11111111111111 File Control Number:00000000000

File Name: sample.ebt

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

Payer: Kishore Payer ID:33333

Accepted Claims: 5 Charges:2023.00

Rejected Claims: 0 Charges:0.00

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

Firstname: sita DOB:3/5/2023
Lastname: Rama Fileno:12322
Message Text: Accepted
-------------------------------
Firstname: Peter DOB:3/5/2027
Lastname: Test1 Fileno:12888
Message Code: Error has occured
-------------------------------
Firstname: smith DOB:3/5/2029
Lastname: Test2 Fileno:99999
Message Text: Accepted
-----------------------------
Message code: NA Type: Q
Message Text: processed for next level.
---------------------------------------
Message code: NA Type: E
Message Text: All the levels are done successfully.
-------------------------------------------
Firstname: Janav DOB:3/9/2023
Lastname: Test3 Fileno:17777
Message Text: Rejected
-----------------------------------
Message code: NA Type: I
Message Text: Sorry, All the levels are rejected.
-------------------------------------------------

The output should be

Firstname Lastname DOB Fileno MessageText
sita Rama 3/5/2023 12322 Accepted

smith Test2 3/5/2029 99999 Accepted
processed for next level.

Janav Test3 3/9/2023 17777 Rejected
Sorry, All the levels are rejected.

Kindly help. Thanks in advance

kinuasa · ‎04-24-2024

@Th11 wrote:

If I copy and paste it into a notepad and deleted the data before firstname then it is working perfectly.

I think it would be better to use the “Parse text” action to get only “Firstname” and after.

Text.Replace Text: $'''NEWLINE''' TextToFind: $'''NEWLINE''' IsRegEx: False IgnoreCase: False ReplaceWith: $'''\\n''' ActivateEscapeSequences: True Result=> NL
**REGION SetList
Variables.CreateNewList List=> List
File.ReadTextFromFile.ReadText File: $'''C:\\Test\\Text\\SampleText2.txt''' Encoding: File.TextFileEncoding.UTF8 Content=> FileContents
Text.ParseText.RegexParse Text: FileContents TextToFind: $'''Firstname[\\s\\S]*''' StartingPosition: 0 IgnoreCase: False Matches=> Matches
SET FileContents TO Matches[0]
Text.SplitText.Split Text: FileContents StandardDelimiter: Text.StandardDelimiter.NewLine DelimiterTimes: 1 Result=> TextList
LOOP LoopIndex FROM 0 TO TextList.Count - 1 STEP 1
    Text.Replace Text: TextList[LoopIndex] TextToFind: $'''-{1,}''' IsRegEx: True IgnoreCase: False ReplaceWith: $'''%''%''' ActivateEscapeSequences: False Result=> Replaced
    IF Replaced.IsEmpty = $'''False''' THEN
        Text.ParseText.ParseForFirstOccurrence Text: TextList[LoopIndex] TextToFind: $'''Firstname:''' StartingPosition: 0 IgnoreCase: True OccurrencePosition=> Position
        IF Position >= 0 THEN
            Variables.AddItemToList Item: TextList[LoopIndex] List: List
        ELSE
            SET List[List.Count - 1] TO $'''%List[List.Count - 1]%%NL%%TextList[LoopIndex]%'''
        END
    END
END
**ENDREGION
**REGION SetDatatable
SET Datatable TO { ^['Firstname', 'DOB', 'Lastname', 'Fileno', 'Message Text'] }
LOOP LoopIndex FROM 0 TO List.Count - 1 STEP 1
    SET MessageText TO $'''%''%'''
    Text.ParseText.RegexParse Text: List[LoopIndex] TextToFind: $'''(?<=Firstname:).*(?=DOB)''' StartingPosition: 0 IgnoreCase: False Matches=> FirstnameMatches
    Text.ParseText.RegexParse Text: List[LoopIndex] TextToFind: $'''(?<=DOB:).*''' StartingPosition: 0 IgnoreCase: False Matches=> DOBMatches
    Text.ParseText.RegexParse Text: List[LoopIndex] TextToFind: $'''(?<=Lastname:).*(?=Fileno)''' StartingPosition: 0 IgnoreCase: False Matches=> LastnameMatches
    Text.ParseText.RegexParse Text: List[LoopIndex] TextToFind: $'''(?<=Fileno:).*''' StartingPosition: 0 IgnoreCase: False Matches=> FilenoMatches
    Text.ParseText.RegexParse Text: List[LoopIndex] TextToFind: $'''(?<=Message Text:).*''' StartingPosition: 0 IgnoreCase: False Matches=> MessageTextMatches
    IF MessageTextMatches.Count > 0 THEN
        LOOP LoopIndex2 FROM 0 TO MessageTextMatches.Count - 1 STEP 1
            IF LoopIndex2 = 0 THEN
                SET MessageText TO MessageTextMatches[LoopIndex2].Trimmed
            ELSE
                SET MessageText TO $'''%MessageText%%NL%%MessageTextMatches[LoopIndex2].Trimmed%'''
            END
        END
    END
    Variables.AddRowToDataTable.AppendRowToDataTable DataTable: Datatable RowToAdd: [FirstnameMatches[0].Trimmed, DOBMatches[0].Trimmed, LastnameMatches[0].Trimmed, FilenoMatches[0].Trimmed, MessageText]
END
**ENDREGION