Send Data in a table in email

[CharlesBrewer]

Hello,

So I am using Automate Desktop to webscrap some data. It will filter it, and then look something like this :

I am trying to extract that data, and then send it in an email, but I also need it to kind of look like a table so it's readable. Instead it looks like this :

It's hard to read, and it's adding commas. What I tried to do is to just a column at a time, to separate the data in columns into different variables, and I tried adding table rows into the email with HTML, but then I just get this : 

Closer, but then I need each piece of data on it's own line. I'm not sure how it would look either if I tried to add in all 8 columns.

Any thoughts on how to achieve this? Thanks. I am very new to automate so talk to me like, well, I'm very new to automate 🙂

[CharlesBrewer]

So I've been testing with just 2 columns and I got it to work (yay).  I don't know if this is at all the most efficient, and hopefully tomorrow when my brain is less dead I can get the other columns in. But here is what I did in pictures.

So I did a replace text and put into list to get half the html

And then the other half is in the email : 

And it got me this, like I wanted : 

[CharlesBrewer]

This isn't actually working. It had the illusion of working because all of data in the 2nd column is the same. It's actually just taking the same text and putting it in all 3 cells. So yeah I have no idea what I'm doing 😞 Any help here is appreciated I've been stumped for several days.

[VJR]

Hi @CharlesBrewer 

Instead of looping into individual columns like ID and Status, use the "Extract data from webpage" to form a datatable of all the columns at once. This is possible when you capture the same fields at least twice (ie; ID) when you are using the "Extract data from webpage" on the website. PAD automatically converts it into a datatable when you capture at least two same elements. Then capture the Status on the website without using any new action and without closing the same Live web helper.

Once you get this datatable then loop through it and within the loop you need to pass the loopcolumns between the html tr and td tags.

Take a look at the 2nd approach in this direct link to the post here.

Also another post here.

[CharlesBrewer]

Hello and thanks for the reply, 

So I was having some trouble because the table is in an iframe. I got around that now though and I have it stored as Datafromwebpage. 

I don't really understand how to setup the loop, The loop wants a number for start and end but I don't know what it is? It's not going to be a static number, of course. I looked at the pages you linked but they just vaguely say 'use a loop' Can you perhaps show me how to setup the loop?  Also is there a way to manually add column headers? When I try to take the column headers off the web data it puts them into a separate column (and the text is wrong anyway).  Any help here is appreciated.

[VJR]

Hi @CharlesBrewer 

Use a loop on a datatable means to use the "For each" loop which automatically detects the total number of rows in the datatable. Simply drag a "For each" and pass the name of the Datatable.

Regarding the second question:

During the Extraction when you use the Live web helper, right click and when you select the below option of entire html table it captures along with the column headers.

On running the Datatable will have the column headers.

[CharlesBrewer]

I guess I am confused to what I'm supposed to do with the Loop still. I tried this : 

But of course this not separate the columns, and I end up getting :

I don't understand how to tell it to separate each piece of data, I guess, and make the rows and columns? Like I said I'm new to the program so if you can be a bit more specific please I would very much appreciate 😕

As for the 2nd part, I can't just extract the table because I don't actually want all the data, and when I do do that I get some weird function code. But I figured I could do the column headers in the email, which is what I was attempting in the above picture, with this :

I hate to be such a bother but if you can lay this all out for me I'd be able to see it and understand it better, I imagine there's some steps or something that are simple to you but not to me and I am missing them.

[CharlesBrewer]

Before my day ends I was messing around with it more. I assumed I had to somehow Identify the columns in the loop so it does the individual data. I was trying this :

But needless to say that didn't work. Going by my other picture, the columns are just named Value#1, Value#2, 3 and 4...so I tried variations of that too and did not work. I hope I'm getting closer? 🙂

[VJR · Accepted solution]

Assuming you have got all your required data from the website into the Datatable called DataFromWebPage

No need to use the List if everything is in the Datatable

Take 1 variable called TableHeader which contains the header of the html table. You can add your own table styles here like width etc.

Then another called TableBody which is generated dynamically within the loop as below

and then after the loop close the table by using </table> to the header and body.

In line 7 concat all 3.

FullTable = Header + Body  + </table>

This is the FullTable html generated after running the process.

<table>
  <tr>
    <th>Company</th>
    <th>Contact</th>
    <th>Country</th>
  </tr>

<tr>
    <td>Alfreds Futterkiste</td>
    <td>Maria Anders</td>
    <td>Germany</td>
</tr>

<tr>
    <td>Centro comercial Moctezuma</td>
    <td>Francisco Chang</td>
    <td>Mexico</td>
</tr>

<tr>
    <td>Ernst Handel</td>
    <td>Roland Mendel</td>
    <td>Austria</td>
</tr>

<tr>
    <td>Island Trading</td>
    <td>Helen Bennett</td>
    <td>UK</td>
</tr>

<tr>
    <td>Laughing Bacchus Winecellars</td>
    <td>Yoshi Tannamuri</td>
    <td>Canada</td>
</tr>

<tr>
    <td>Magazzini Alimentari Riuniti</td>
    <td>Giovanni Rovelli</td>
    <td>Italy</td>
</tr>

</table>

whose output looks as below when run in an html editor

You need to use the FullTable variable in your Send email with Html turned on.

Here is the full code of the sample flow.

Copy paste it into a blank flow and check.

@@timestamp: '03/15/2022 02:52:32'
@@source: 'Recorder'
@@culture: 'en-US'
WebAutomation.LaunchChrome.AttachToChromeByUrl TabUrl: 'https://www.w3schools.com/html/tryit.asp?filename=tryhtml_table_intro' AttachTimeout: 10 BrowserInstance=> Browser
WebAutomation.ExtractData.ExtractHtmlTable BrowserInstance: Browser Control: $'''html > body > div:eq(4) > div:eq(3) > div > div > iframe > html > body > table''' ExtractionParameters: {[$'''Value #1''', $'''Value #2''', $'''Value #3'''], [$'''''', $'''''', $''''''] } PostProcessData: True ExtractedData=> DataFromWebPage
SET TableHeader TO $'''<table border=1>
  <tr>
    <th>Company</th>
    <th>Contact</th>
    <th>Country</th>
  </tr>'''
LOOP FOREACH CurrentItem IN DataFromWebPage
    SET TableBody TO $'''%TableBody%
<tr>
    <td>%CurrentItem['Value #1']%</td>
    <td>%CurrentItem['Value #2']%</td>
    <td>%CurrentItem['Value #3']%</td>
</tr>
'''
END
SET FullTable TO $'''%TableHeader%
%TableBody%
</table>'''
Display.ShowMessageDialog.ShowMessage Message: FullTable Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed

# [ControlRepository][PowerAutomateDesktop]

{
  "ControlRepositorySymbols": [],
  "ImageRepositorySymbol": {
    "Name": "imgrepo",
    "ImportMetadata": {},
    "Repository": "{\r\n  \"Folders\": [],\r\n  \"Images\": [],\r\n  \"Version\": 1\r\n}"
  }
}

You might need to add the variables.

Also the UI elements wont get copied but you need to extract the full table as shown earlier.

Also I have shared the URL of the sample website I am using, it is in the Launch action.

[CharlesBrewer]

Thanks that worked 🙂 Mostly I needed to know the 

<td>%CurrentItem['Value #1']%</td>

part because I did knot how how to reference the columns in the rows and I was definitely doing the syntax so very wrong. But appreciated, it seems to be working. 

One more question for my own learning process if you don't mind. Why make the table header and closing table tag as variables, and not just put those in the email? Does it change the speed or anything or just a personal preference?

[VJR]

The table header and footer can also be added directly while sending the email. It is only for better readability for any developer to understand the code easily.