Parse email json

RPA Automation Studio Pro
1

Hi, I am trying the parse particular data from a email , IP Address, host , profile, checked columns, The data is extracted from email as json format, but i fail parse and extract the particular data from table embedded in the email. Pls suggest. Attached few of the doc i used in email and json file.Any help will be appreciated

Email
Email863×516 6.29 KB

snapshotjson
snapshotjson1234×659 116 KB

Json.zip (22.8 KB)
.

2

@raju.saini You can do it in multiple ways.

  1. You can use “extract text” activity to extract values from email content.
  2. You can use Regx to extract values
  3. The easiest way is to usse ASK AI activity for the same.
    Example:

image
Step 1: create a variable and store email body content test= email_content[0].text

Step 2: Use Ask AI and mention below prompt to chatGpt
'Given the following request ’

  • test + ‘, analyze the text and format the answer in the following JSON format: {“Extract IP address”: “value”, “host”: “value”, “profile”: “value”, “checked”: “value”}’

Step 3: store result in JSON and parse the JSON
result_json = JSON.parse(askai[“choices”][0][“text”])

Now you can fetch ipaddress,Host,Profile and Checked easily as they are in JSON format

3

Thanks for the solution. Seems Good. Although one concern although i parsing multiple rows for the table or different text , askAI seems to return the same result. Any suggestion.

Result Json
{“id”:“cmpl-7J4VayXoVYpKWseEPBvgse5pymnIS”,“object”:“text_completion”,“created”:“2023-05-22T18:25:18.000Z”,“model”:“text-davinci-003”,“choices”:[{“text”:“\n\n{“Extract IP address”: “192.168.1.1”, “Host Name”: “myhostname”, “Profile”: “default”, “Checked”: “true”}”,“index”:0,“logprobs”:null,“finish_reason”:“stop”}],“usage”:{“prompt_tokens”:73,“completion_tokens”:64,“total_tokens”:137}}

4

@raju.saini You mean, you are getting same result for different data? I assume something missing in logic. you can send .neek file here with different data you are trying to parse. we can take a look

5

Hi , PFA the json for email extract & neek.
AskAI.zip (3.2 KB)

6

modified.zip (3.5 KB)

I have modified the flow. You can now see all the table result.
Root cause: Variable was using inside ‘Set a value’, which is why you were getting same result everytime. Always use ‘Calculate a value’ when using dynamic values(varuables)

Please use correct Javascript line to parse the data from JSON

1 Like