Problem while exporting data from body mail to Excel

[Anonymous]

Hello,

I'm trying to export data from body mail to Excel and this happens, when It's exported. (Check screenshots)

Could you please tell me where is the problem coming from?

Code I'm using is => 

last(split(variables('EmailBody'),'Mobile number:'))

Co

[Pstork1]

Try using the HTML to Text Action to strip out the HTML formatting before doing the Split() on the Body.  

[Anonymous]

Thank you for replying.

Same error

[Pstork1]

What error are you seeing?  Your post shows the phone number, its just embedded in HTML tags.  I thought that was your problem.  can you show me the split and last formulas you are using?  They should now be based on the output of HTML to Text, not Email Body.

[Anonymous]

Thank you for replying.

Yes, this is the problem I'm facing.

This is the code I'm using 

last(split(variables('EmailBody'),'Mobile number:'))

I have also attached for you screenshot of the outpot + of the variable.

[Pstork1]

As I said in my original post.  Process the email body using HTML to Text.  Then do your Last(Split()) on the output of the HTML to Text command instead of the email body.

[Anonymous]

Thanks for replying.

I'm sorry I'm new to this and I don't quite understand what you want me to do.

I have replaced EmailBody with HTML To Text, but now I don't understand what you mean with 

Then do your Last(Split()) on the output of the HTML to Text command instead of the email body.

Can you explain further step by step?

Here are screenshot of what I have done and the results.

[Pstork1]

LIke this.

The formula in the Compose is 

last(split(outputs('Html_to_text')?['body'], 'Mobile number: '))

That still may leave a lot of extra text after the Phone number that should also be truncated.  But it should work.

[Anonymous]

Got the same result as last time, still have extra text that should be deleted.

Any idea how to make the phone number only show without those extras?

[Pstork1]

Do another split on that phone number based on ' ' (space) then take the first() element.  That should give you +2010936.  If you then want to get rid of the plus use replace()

[Anonymous]

Created new new based on space like you said

last(split(variables('EmailBody'),' '))

the same problem, nothing changed

[Pstork1]

You keep trying to work on just the Email Body.  It takes several steps to get to the mobile number inside the email. Each step provides the output for the next step

1.  Strip all the HTML codes from the EmailBody using the Html to Text Action
2. Split the result into an array based on the location of 'Mobile Number:'.  The last item in the array will start with the phonenumber
3. Split the result of #2 based on a space.  The first item will contain the mobile number.

[Anonymous]

I'm not quite sure If I understood correctly or not, I have changed the "EmailBody" variable to array but not sure what to do next. Could you please explain to me what exactly you want me to do? also If you want a copy of the whole bodymail in HTML I can send you it

[Pstork1]

Send me a sample of the email body and I'll provide screenshots and formulas of how to get the Mobile Phone number out of it.

[Anonymous]

Thank you so much.

I have uploaded for you the whole package + txt file named "bodymail", I want to get the field "Name" and "Phone Number"

[Pstork1 · Accepted solution]

The email body you sent me doesn't look like the one pictured in your first post.  There don't seem to be any HTML tags in this one.  But here are the steps to get the Phone number out.  Similar steps would be needed to get the name.

1) I initialize two variables. One to hold the result and one to remove some the unwanted quotes and carriage return between Mobile Number: and the number itself.

2) Next use the HTML to Text action to remove any HTML Tags in the email Body. 

3) Use a Compose with Last(Split()) on the results of #2 to get all the text just after 'Mobile Number:'

last(split(outputs('Remove_HTML_Tags')?['body'],'Mobile number:'))

4) Use a Compose with First(Split()) on the results of #3 to remove any characters after the phone number

first(split(outputs('Isolate_Start_of_Phone_Number'),' '))

5) Save the phone number to the variable created earlier and remove the ",<crlf>" that were between Mobile Number: and the phone number.

replace(outputs('Remove_characters_after_Phone_Number'),variables('varWhitespace'),'')

6) The result in the variable is now +2001115421083 which you can insert into the Excel row.

[Anonymous]

Getting this error =>

Correct to include a valid reference to 'Remove_HTML_Tags' for the input parameter(s) of action 'Isolate_Start_of_Phone_Number'.

Also can you explain step 5 again? I created a new variable and added the code you sent in it.

[Pstork1]

The error you are getting is because you aren't using the output of a Remove_HTML_Tags step in your formula.  Look at Step #4 you are missing an action.

For Step #5.  The sample text you sent to me included the following around the phone number.

...MalatoMobile number:",
  "+2001115421083 Driver's sign will...

Step #5 is there to replace the following to remove it from the phone number.

",
  "

[Anonymous]

Thank you so much, it worked now but I have 2 questions

1) Sometimes it work but without the "+" sign of the country code, I mean, In this screenshot as you can see I have 2 results. One of them with the + of the country code and the other without (It's the same guest and same email) what should I do to make it always have the + sign?

here also the both results

2) What is the easiest way to add the "Name:" field

[Pstork1]

1) It looks like the formatting of the numbers in the email isn't consistent.  That's going to make getting the numbers out almost impossible.  When adding them to Excel you probably also need to add some quotation marks or something to make sure excel doesn't interpret them as numbers.  A leading space should help with that also.

2) To get the name out you'll have to do a similar exercise.  Just with whatever the prompt is for the name instead of the Mobile Number: