Extract Data

(0) Share

Report

Posted on by DN1

Hi ,

I am new to PAD. Trying to extract particular text from a form using Regex.

Eg:

Sales Id:22222

Occupation: Manager

Unattended operation: Yes

Sales id:33333

Occupation: Associate

Attended operation: NO

Sales Id:88888

Occupation: VP

Unattended operation: N0

From this I need to extract the data " Sales, Occupation and only Unattended Operation" Data's. PAD has to skip the Data which is related to Attended operation. Thanks in advance.

Categories:

Power Automate Desktop

I have the same question (0)

All responses (6)

Answers (2)

Sort by

Deenuji_Loganathan_ 6,255 Moderator on at

Like (1)

Report
Copy link

Link copied!
@DN1

Please follow the below suggested flow:

Code:
SET DataTable TO { ^['Sales Id', 'Occupation', 'Unattended operation'] } File.ReadTextFromFile.ReadTextAsList File: $'''C:\\Deenu\\Salesdata.txt''' Encoding: File.TextFileEncoding.UTF8 Contents=> FileContents SET StrSalesId TO $'''%''%''' SET StrOccupation TO $'''%''%''' SET StrUnattended TO $'''%''%''' LOOP FOREACH CurrentItem IN FileContents IF (IsNotEmpty(CurrentItem.Trimmed) AND NotStartsWith(CurrentItem.Trimmed, $'''--''', False)) = True THEN Text.ParseText.RegexParse Text: CurrentItem TextToFind: $'''(?<=Sales Id:)\\d+''' StartingPosition: 0 IgnoreCase: False Matches=> SalesIdMatches IF SalesIdMatches.Count > 0 THEN SET StrSalesId TO SalesIdMatches[0].Trimmed END Text.ParseText.RegexParse Text: CurrentItem TextToFind: $'''(?<=Occupation:\\s*)\\w+''' StartingPosition: 0 IgnoreCase: False Matches=> OccupationMatches IF OccupationMatches.Count > 0 THEN SET StrOccupation TO OccupationMatches[0].Trimmed END IF StartsWith(CurrentItem, $'''Unattended operation''', True) THEN Text.ParseText.RegexParse Text: CurrentItem TextToFind: $'''(?<=Unattended operation:\\s*)\\w+''' StartingPosition: 0 IgnoreCase: False Matches=> UnattendedMatches IF UnattendedMatches.Count > 0 THEN SET StrUnattended TO UnattendedMatches[0].Trimmed Variables.AddRowToDataTable.AppendRowToDataTable DataTable: DataTable RowToAdd: [StrSalesId, StrOccupation, StrUnattended] SET StrSalesId TO $'''%''%''' SET StrOccupation TO $'''%''%''' SET StrUnattended TO $'''%''%''' END END END END

New to power automate desktop? Not sure how to copy/paste the above code into your PAD designer? Refer below image:

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 🙏

Was this reply helpful? Yes No
Verified answer

Agnius Bartninkas Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
Use Parse text with regular expressions enabled and use the following regex patterns to retrieve the values:

Sales Id: (?<=Sales Id:)\d+(?=\r?\n.+\r?\nUnattended operation)

Occupation: (?<=Occupation:\s).+(?=\r?\nUnattended operation)

Unattended operation: (?<=Unattended operation:\s).+

You will need to use Parse text 3 times in total to retrieve the three values into three individual lists of items. Follow the below screenshot for a sample on how to set them up correctly - they should all be the same, with only the pattern and the produced variable name being different:

Enabling the "Is regular expression" toggle will make PAD interpret the "Text to find" as a regex pattern. Disabling the "First occurrence only" toggle will make it retrieve all matches into a list, instead of only the first one. And enabling the "Ignore case" toggle will make it ignore the text case, as I can see your sample uses a lowercase i in "Sales id" in one of the items, so it might make sense to have that in place.

Please note you need to use an actual variable where your items are stored in place of %Text% in my sample.

Was this reply helpful? Yes No
DN1 90 on at

Like (0)

Report
Copy link

Link copied!

@Agnius ,
Thank you so much for your response. I tried to use Regex (parse text) for the below pattern example. But it is not working.

Customer Name: Peter, Test1
From Date: 422024 To Date: 422025
Customer Contact Number: 1 333345 ABCDEF Total: 100.00
Customer ID: 5656565656 Customer Trace #: NA
Customer Claim #: NA Customer Book #: 9999999999
Message Initiator: None Message Type : X
Message Code: NA
Text Message: This person is approved to buy a house. City: Mars
------------------------------------------------------------------------------------------------------------
Customer Name: First, Last
From Date: 422025 To Date: 422029
Customer Contact Number: 1 300345 ABCDEF Total: 180.00
Customer ID: 9956565656 Customer Trace #: NA
Customer Claim #: NA Customer Book #: 9999999000
Message Initiator: None Message Type : X
Message Code: NA
Message: This person is not approved to buy a house. City: Moon

From the above example, how to use Regex expression to extract "From Date, Customer Contact Number, Text Message" Data. It should skip the Data's which is related to "Message". I Need to extract only the data if it contains "Text Message " as a keyword. Please help. Thanks in advance!

Was this reply helpful? Yes No
DN1 90 on at

Like (0)

Report
Copy link

Link copied!

Hi @Agnius , Can you please guide with the Parse text (Regex) to extract the data.
Customer Name: Peter, Test1
From Date: 422024 To Date: 422025
Customer Contact Number: 1 333345 ABCDEF Total: 100.00
Customer ID: 5656565656 Customer Trace #: NA
Customer Claim #: NA Customer Book #: 9999999999
Message Initiator: None Message Type : X
Message Code: NA
Text Message: This person is approved to buy a house. City: Mars
------------------------------------------------------------------------------------------------------------
Customer Name: First, Last
From Date: 422025 To Date: 422029
Customer Contact Number: 1 300345 ABCDEF Total: 180.00
Customer ID: 9956565656 Customer Trace #: NA
Customer Claim #: NA Customer Book #: 9999999000
Message Initiator: None Message Type : X
Message Code: NA
Message: This person is not approved to buy a house. City: Moon

From the above example, how to use Regex expression to extract "From Date, To Date, Customer Contact Number, Text Message". I do not want the Data's which is related to "Message". I Need to extract only the data if it contains "Text Message " as a keyword.

Was this reply helpful? Yes No
Agnius Bartninkas Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Well, this is a completely different text, so no wonder it doesn't work.

Here are the patterns you need to get your data:

From Date: (?<=From Date:\s)\d+(?=.+\r\n(.+\r?\n){5}Text Message)

Customer Contract Number: (?<=Customer Contact Number:\s).+?(?=\s+Total.+\r\n(.+\r?\n){4}Text Message)

Text Message: (?<=Text Message:\s).+?(?=\s+City)

Was this reply helpful? Yes No
Verified answer

Deenuji_Loganathan_ 6,255 Moderator on at

Like (1)

Report
Copy link

Link copied!
Hi @DN1,

@Agnius solution is the best option if it meets your requirements. Otherwise, you may also consider the following approach:

Code:
# Read the contents of the text file into a list File.ReadTextFromFile.ReadTextAsList File: $'''C:\\Deenu\\CustomerData.txt''' Encoding: File.TextFileEncoding.UTF8 Contents=> FileContents # Define the column names for the data table SET DataTable TO { ^['From Date', 'Customer Contact Number', 'Text Message'] } # Initialize variables to store extracted data SET StrFromDate TO $'''%''%''' SET StrCustomerContactNumber TO $'''%''%''' SET StrTextMessage TO $'''%''%''' # Loop through each line in the file contents LOOP FOREACH CurrentItem IN FileContents # Check if the line is not empty and does not start with '--' IF IsNotEmpty(CurrentItem.Trimmed) AND NotStartsWith(CurrentItem.Trimmed, $'''--''', False) THEN # Extract the 'From Date' if the line contains 'From Date:' IF Contains(CurrentItem, $'''From Date:''', True) THEN Text.ParseText.RegexParse Text: CurrentItem TextToFind: $'''(?<=From Date:)\\s*\\d+''' StartingPosition: 0 IgnoreCase: False Matches=> FromDateMatches IF FromDateMatches.Count > 0 THEN SET StrFromDate TO FromDateMatches[0].Trimmed END # Extract the 'Customer Contact Number' if the line contains 'Customer Contact Number:' ELSE IF Contains(CurrentItem, $'''Customer Contact Number:''', True) THEN Text.ParseText.RegexParse Text: CurrentItem TextToFind: $'''(?<=Customer Contact Number:)\\s*\\d{1,2}\\s+\\d{6}\\s+\\w+''' StartingPosition: 0 IgnoreCase: False Matches=> CustomerContactNumberMatches IF CustomerContactNumberMatches.Count > 0 THEN SET StrCustomerContactNumber TO CustomerContactNumberMatches[0].Trimmed END # Extract the 'Text Message' if the line contains 'Text Message:' ELSE IF Contains(CurrentItem, $'''Text Message:''', True) THEN Text.ParseText.RegexParse Text: CurrentItem TextToFind: $'''(?<=Text Message:)\\s*[^\\r\\n]+''' StartingPosition: 0 IgnoreCase: False Matches=> TextMessageMatches IF TextMessageMatches.Count > 0 THEN Text.CropText.CropTextBeforeFlag Text: TextMessageMatches[0].Trimmed ToFlag: $'''city''' IgnoreCase: True CroppedText=> StrTextMessage Variables.AddRowToDataTable.AppendRowToDataTable DataTable: DataTable RowToAdd: [StrFromDate, StrCustomerContactNumber, StrTextMessage] SET StrFromDate TO $'''%''%''' SET StrCustomerContactNumber TO $'''%''%''' SET StrTextMessage TO $'''%''%''' END ELSE IF Contains(CurrentItem, $'''Message:''', True) THEN SET StrFromDate TO $'''%''%''' SET StrCustomerContactNumber TO $'''%''%''' SET StrTextMessage TO $'''%''%''' END END END

New to power automate desktop? Not sure how to copy/paste the above code into your PAD designer?
Refer the below image:

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 🚀

Was this reply helpful? Yes No