cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Harpreet_Tohra
Frequent Visitor

Not able to get right data

Hi 

I am facing this issue from long time I have tried each and everything but able to solve this. Please help me solve the issue.

I am extracting outlook mail data as shown below.

Harpreet_Tohra_1-1615989316886.png

But unfortunately not getting proper data as you can see in below screenshot the data is coming like this. I have tried trim first, last both, and other possible things which i know. But till not getting proper data. It is showing extra data.

Harpreet_Tohra_0-1615989261180.png

The code for check in and checkout code is same but still it is showing nothing for check in date and for Travel Agent name it is taking more than needed data.

Code for Travel Agent Name: trim(first(split(last(split(body('Html_to_text'),'Travel Agent Name:')),'CheckIn Date'))) 
Code for CheckIn Date: first(skip(split(first(split(body('Html_to_text'),'Checkout Date:')),'CheckIn Date'),1))
Code for Check Out Date: first(skip(split(first(split(body('Html_to_text'),'Power')),'Checkout Date:'),1))
Kindly help me to solve this issue.
Thanks
1 ACCEPTED SOLUTION

Accepted Solutions
tom_riha
Super User II
Super User II

Hellon @Harpreet_Tohra ,

you could use a combination of the substring(...), indexof(...), add(...) and sub(...) expressions.

Starting with indexof(...), it'll search a specific string inside a string and return position of the first character of the 'string to search'. This way you can find where a specific string begins.

indexof(string, 'string to search')

example:
indexof('Your Email Address: abc@gmail.com Travel Agent:','Email') 
- it'll return number 5 because Email starts on the 5 character in the string.

 

Then there's the add(...) expression to skip the full length of the string you're searching for.

add(number, number)

example:
add(5,15) will give you 20
add(indexof('Your Email Address: abc@gmail.com Travel Agent:','Email'),15) 
- it'll give you 20, the position where the email abc@gmail.com starts (5 + 15 (length of the 'Email Address: ' string))

 

The last is substring(...) that will give you a substring of a string, starting from a position in the string and taking a specific number of characters.

substring(string, fromPositionNumber, howManyCharactersToTake)

Example:
substring('Your Email Address: abc@gmail.com Travel Agent:', add(indexof('Your Email Address: abc@gmail.com Travel Agent','Email'),15),13) 
- it'll take a substring from 'Your Email Address: abc@gmail.com Travel Agent' starting on position 20 and 13 characters long: 'abc@gmail.com'

 

The last part that should be calculated dynamically is the length of the string with another indexof(...) expression. The length of the string is the index of the 'after' string - length of the starting string - index of the starting string.

sub(sub(indexof('Your Email Address: abc@gmail.com Travel Agent', 'Travel'), 15), indexof('Your Email Address: abc@gmail.com Travel Agent', 'Email'))
- it'll return 14, the length of the email 'abc@gmail.com '

 

Putting it all together will give you the following expression to get the email address:

substring('Your Email Address: abc@gmail.com Travel Agent',add(indexof('Your Email Address: abc@gmail.com Travel Agent', 'Email Address: '),15), sub(sub(indexof('Your Email Address: abc@gmail.com Travel Agent', ' Travel'), 15), indexof('Your Email Address: abc@gmail.com Travel Agent', 'Email')))

 

Replacing the fixed string with dynamic content, it might look like this:

substring(outputs('Compose'),add(indexof(outputs('Compose'), 'Email Address: '),15), sub(sub(indexof(outputs('Compose'), 'Travel'), 15), indexof(outputs('Compose'), 'Email')))

 

You can try to play with the expression(s) to adjust it for each of the values.

And it'll work only if the string in email has always the same broken format.



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ I also blog about Power Automate solutions even for non-IT people. ]

View solution in original post

1 REPLY 1
tom_riha
Super User II
Super User II

Hellon @Harpreet_Tohra ,

you could use a combination of the substring(...), indexof(...), add(...) and sub(...) expressions.

Starting with indexof(...), it'll search a specific string inside a string and return position of the first character of the 'string to search'. This way you can find where a specific string begins.

indexof(string, 'string to search')

example:
indexof('Your Email Address: abc@gmail.com Travel Agent:','Email') 
- it'll return number 5 because Email starts on the 5 character in the string.

 

Then there's the add(...) expression to skip the full length of the string you're searching for.

add(number, number)

example:
add(5,15) will give you 20
add(indexof('Your Email Address: abc@gmail.com Travel Agent:','Email'),15) 
- it'll give you 20, the position where the email abc@gmail.com starts (5 + 15 (length of the 'Email Address: ' string))

 

The last is substring(...) that will give you a substring of a string, starting from a position in the string and taking a specific number of characters.

substring(string, fromPositionNumber, howManyCharactersToTake)

Example:
substring('Your Email Address: abc@gmail.com Travel Agent:', add(indexof('Your Email Address: abc@gmail.com Travel Agent','Email'),15),13) 
- it'll take a substring from 'Your Email Address: abc@gmail.com Travel Agent' starting on position 20 and 13 characters long: 'abc@gmail.com'

 

The last part that should be calculated dynamically is the length of the string with another indexof(...) expression. The length of the string is the index of the 'after' string - length of the starting string - index of the starting string.

sub(sub(indexof('Your Email Address: abc@gmail.com Travel Agent', 'Travel'), 15), indexof('Your Email Address: abc@gmail.com Travel Agent', 'Email'))
- it'll return 14, the length of the email 'abc@gmail.com '

 

Putting it all together will give you the following expression to get the email address:

substring('Your Email Address: abc@gmail.com Travel Agent',add(indexof('Your Email Address: abc@gmail.com Travel Agent', 'Email Address: '),15), sub(sub(indexof('Your Email Address: abc@gmail.com Travel Agent', ' Travel'), 15), indexof('Your Email Address: abc@gmail.com Travel Agent', 'Email')))

 

Replacing the fixed string with dynamic content, it might look like this:

substring(outputs('Compose'),add(indexof(outputs('Compose'), 'Email Address: '),15), sub(sub(indexof(outputs('Compose'), 'Travel'), 15), indexof(outputs('Compose'), 'Email')))

 

You can try to play with the expression(s) to adjust it for each of the values.

And it'll work only if the string in email has always the same broken format.



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ I also blog about Power Automate solutions even for non-IT people. ]

View solution in original post

Helpful resources

Announcements
MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Users online (64,356)