Need help with filtering of excel datatable and sending filtered data from excel to email message with structured table

Help Activities
,
1

Flowchart.xaml (51.4 KB)

Hi guys, so I am trying to extract 2 different product from an e-commerce website. I have so far scraped 300 lines of raw data, upon finishing, I save and extracted it to my excel workbook with the sheet called; product + “_Raw”

So once I am done, my next step is to sort my data(country) in descending order, and so far this has been working well. Then after that, I created an output for that Sorted data table, and then I went on to filtering the data table, I have to create 3 filter data table for

  1. deleting of empty cells
  2. filtering and removing all data which is greater than the user’s budget (for example, if my user inputs 20 as their budget, then it would filter out those products that are greater than 20), however this is one of which I was struggling with, so the first few tries, I have successfully filtered out the data(price) according to my budget, but now with the currency sign, I am unable to filter out my data table. This is my first problem.
  3. filtering out the semi-filtered table that does not contain the word “Singapore” as I would only like to extract the products that are locally produced or shipped". So far this had been working for me.

Then afterwards I would use write range to move all that finalised data table to my excel sheets. And the sheets would be created automatically (Product + “_Filtered”)

I would like to send this “filtered data table” to my colleague, but there are two problems that I am facing, the first one is that my data table was not properly structured as it did not have the table border in the email I send. The second problem is it only extracted one of the filtered datatable, even though I have two product + “_filtered” sheets

TLDR; I need help with filtering out my datatable according to what the user have inputted in the budget variable. And secondly, I am unable to send my filtered data table to email with a table border, and thirdly I can’t extract more than one filtered datatable for my products, as I have searched for two products and it have been filtered out (eg: iphone_filtered and android_filtered) but it would only show the first data (which is iphone_filtered)

Sorry for the long paragraph, I hope you guys could understand what I needed. Thank you for reading!

2

Hi @Conning ,

Focusing on one problem at a time would do us both Good and could communicate effectively on to which are we trying to scope it out.

Regarding the Table Border in Mail, we could add the Borders to the HTML Table that is prepared. You could check the below on creating the HTML Table from Datatable if not already done :

1 Like
3

Let’s tackle your issues one by one:

First Problem: Filtering Data Table with Currency Sign

As you’re using a currency sign, the data is seen as a string and not a number. Therefore, you can’t directly compare it with a number. You need to remove the currency sign first before comparing the prices with the budget. Here’s how you can do that.

:one: :arrows_counterclockwise: Assign Activity: You can use this activity to replace the currency sign in the price column with an empty string which effectively removes it.

  • To: YourDataTable.Columns("Price")
  • Value: YourDataTable.Columns("Price").ToString().Replace("$", "")

:two: :arrows_counterclockwise: Assign Activity: Convert the price column values from string to double using another Assign activity.

  • To: YourDataTable.Columns("Price")
  • Value: Convert.ToDouble(YourDataTable.Columns("Price"))

Now, your Price column should be free of the currency sign and its type should be double. You can now proceed with the Filter Data Table activity as you were doing before.

Second Problem: Sending Filtered Data Table in an Email with Table Border

To send the filtered data table in an email with a table border, you need to convert your data table to HTML. You can use the following steps:

:one: :arrows_counterclockwise: Assign Activity: Convert your data table to HTML.

  • To: htmlTable (a string variable)
  • Value: "<table><tr><td>" + String.Join("</td><td>", YourDataTable.Columns.Cast(Of DataColumn).Select(Function(c) c.ColumnName)) + "</td></tr>" + String.Join("", YourDataTable.AsEnumerable().Select(Function(row) "<tr><td>" + String.Join("</td><td>", row.ItemArray) + "</td></tr>")) + "</table>"

:two: :incoming_envelope: Send Outlook Mail Message Activity: Send the HTML table in an email.

  • In the “Body” field, input your htmlTable variable.
  • Make sure to check the “IsBodyHtml” checkbox.

Third Problem: Extracting More Than One Filtered Data Table

For this, you need to loop through all your products and perform the filtering and emailing for each product.

:one: :arrows_counterclockwise: For Each Activity: Loop through each product.

  • In the “For Each” field, input your list of products.
  • In the “TypeArgument” drop-down, select the appropriate type for your products.

Inside this For Each activity, you can add all the steps you’re currently doing for one product, i.e., sorting, filtering, and sending the email.

Please let me know if you need further clarification on any of these points!

1 Like
4

Hi @ManFrancko

For the first step, when I tried to assign activity for the data table columns, it prompted an error “BC30526 Property item is read only”, how can i resolve that?

Thanks for helping!

5

I see, I apologize for the confusion. The DataTable columns are indeed read-only and can’t be modified directly. Instead, you can create a new column with the adjusted values.

Here’s the correct way to do this:

:one: Add Data Column Activity: Add a new column to store the price values without the currency sign.

  • In the “DataTable” field, input YourDataTable.
  • In the “ColumnName” field, input a name for the new column. For example, "PriceWithoutSign".

:two: For Each Row Activity: Iterates through each row of the data table.

  • In the “For Each Row” field, input YourDataTable.

:three: Assign Activity (inside the For Each Row activity): Replace the currency sign in the price and assign it to the new column.

  • To: row("PriceWithoutSign")
  • Value: Convert.ToDouble(row("Price").ToString().Replace("$", ""))

Now, you can use the “PriceWithoutSign” column for your filtering.

Please let me know if you need further clarification on any of these steps.

1 Like
6

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.