Convert mail text into Json format

Help Studio
, , , , , , ,
1

Hallo :smiley:

I need to extract data from mails.
The Emails can have between 1-4 persons, and a person can have 1 or 2 phone numbers. If a person have 2 phone numbers, the person needs 2 lines in the json output.

I will give 2 examples of how it could look like.

Example 1:

"Adress: hej123
Information: hallo how are you…

person 1: Alex Doe, phone1: 1231453, day_night

person 2: Bob Doe, phone1: 1231515123, phone2: 333305483, day_night

person 3: Carol Doe, phone1: 33333333, day

person 4: Dave Doe, phone1: 1234, night

Have a good day

Kind regards ------
Adresss
City
Phonenumber
Email

You cant respond on this E-mail."

Output for example 1 should be:

[{“FullName”:“Alex Doe”,“PhoneNumber”:“1231453”,“Day”:true,“Night”:true},
{“FullName”:“Bob Doe”,“PhoneNumber”:“1231515123”,“Day”:true,“Night”:true},
{“FullName”:“Bob Doe”,“PhoneNumber”:“333305483”,“Day”:true,“Night”:true},{“FullName”:“Carol Doe”,“PhoneNumber”:“33333333”,“Day”:true,“Night”:false},{“FullName”:“Dave Doe”,“PhoneNumber”:“1234”,“Day”:false,“Night”:true}]

Example 2

"Adress: hej123
Information: hallo how are you…

person 1: Alex Doe, phone1: 1231453, day_night

person 2: Carol Doe, phone1: 33333333, day

person 3: Dave Doe, phone1: 1234, night

Have a good day

Kind regards ------
Adresss
City
Phonenumber
Email

You cant respond on this E-mail."

Output should be:

[{“FullName”:“Alex Doe”,“PhoneNumber”:“1231453”,“Day”:true,“Night”:true},
{“FullName”:“Carol Doe”,“PhoneNumber”:“33333333”,“Day”:true,“Night”:false},{“FullName”:“Dave Doe”,“PhoneNumber”:“1234”,“Day”:false,“Night”:true}]

Lets me know if you need more information, thanks a lot! :smiley:

2

@christian.bendtsen,

Follow this approach,

  1. Use Regex to retrieve the date from email
  2. Serialize the data to Json.

Thanks,
Ashok :slight_smile:

3

Hallo Ashokkarale :slight_smile:
Can you give an example?
Kind regards

4

Hi @christian.bendtsen ,

After reviewing your use case, I built a solution that meets the expected output. I’ve implemented the logic accordingly and would like to share some screenshots of the output with you.

Screenshot1:

image
image1337×751 39.2 KB

Screenshot2:

image
image955×795 33.7 KB

Let me know if this solution satisfies this particular question of yous

Additionally, below is the workflow for your reference
MailText_To_JSON.zip (4.0 KB)

Hope it helps you out

1 Like
5

Hallo Vikas_M

Everything works except if a person have more than one phonenumber, then we need 2 lines in the json format, can you help? :slight_smile:

As you can see Bob Doe needs to lines i json:

[{“FullName”:“Alex Doe”,“PhoneNumber”:“1231453”,“Day”:true,“Night”:true},
{“FullName”:“Bob Doe”,“PhoneNumber”:“1231515123”,“Day”:true,“Night”:true},
{“FullName”:“Bob Doe”,“PhoneNumber”:“333305483”,“Day”:true,“Night”:true},{“FullName”:“Carol Doe”,“PhoneNumber”:“33333333”,“Day”:true,“Night”:false},{“FullName”:“Dave Doe”,“PhoneNumber”:“1234”,“Day”:false,“Night”:true}]

because he has 2 numbers in example 1:

"Adress: hej123
Information: hallo how are you…

person 1: Alex Doe, phone1: 1231453, day_night

person 2: Bob Doe, phone1: 1231515123, phone2: 333305483, day_night

person 3: Carol Doe, phone1: 33333333, day

person 4: Dave Doe, phone1: 1234, night

Have a good day

Kind regards ------
Adresss
City
Phonenumber
Email

You cant respond on this E-mail."

6

there are several option to implement the extraction and JSON generation

For extraction using named groups can help to handle the dynamics:

grafik
grafik1008×353 25.4 KB

And can refer to tgroups with its names and is occurences
grafik
grafik476×863 38.9 KB

7

Hallo ppr
I have tried something like that, but i cant figure it out :frowning:
Can you show me in UiPath?

8

grafik
grafik963×356 43.6 KB

Configure the extraction, set the Groupname as it should late become the Property Names
Split the Body and filter out the relevant lines

arrTXTLines = 
strBody.Split(Environment.NewLine.toCharArray).Where(Function (x) x.Trim.ToUpper.StartsWith("PERSON")).toArray

Loop over the lines
nested Loop over the extraction Config and store the extractions under the GroupName in a dictionary.

grafik
grafik457×833 47.6 KB

Within the switch we can configure the extraction mode (Text, or Boolean)

Driven by number of Phone Nos (or any other multiple value occurence) we create the split sets

Finaly we create the JSON and get:

grafik
grafik295×555 36.9 KB

Find starter help here:
ppr_JSONBox_CB.xaml (19.9 KB)
Test Data:
Input_CB.txt (372 Bytes)