<aside> 💡 How to filter and split a datatable into a list of datatables

</aside>

Problem Description

Say you have the following data stored in an Excel file:

Certificate Employee Email
Medical John Jones [email protected]
Health & Safety John Jones [email protected]
Driving John Jones [email protected]
Swimming Mike Smith [email protected]
Health & Safety Mike Smith [email protected]
Working at heights Kevin Peterson [email protected]
Cooking Kevin Peterson [email protected]
Driving Kevin Peterson [email protected]
Medical Kevin Peterson [email protected]

You would like to filter this table based on the column Email. For each unique Email found, you would like to save the filtered rows in a separate datatable. Each of the resulting datatables will be converted to a HTML table to be included in the email to be sent.

Solution

The following solution will take an input Excel file, i.e. %ExcelFile%, and return a list of datatables, i.e. %ListOfDatatables%.

Untitled

Each item in %ListOfDatatables% is derived by filtering the original datatable read from the Excel file. In this example, the datatable is filtered based on the column Email. The number of items in %ListOfDatatables% corresponds to the number of unique emails found in the original datatable.

Note that you will need to enter the following SQL query in the action Execute SQL statement:

Select * from [Sheet1$] where [Email] = '%CurrentEmail%';

In the second For each loop, a subflow ConvertDatatableToHTMLTableAndSendEmail is called to convert the datatable into a HTML table which can then be included in the email body.

Additional Information