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


Problem Description

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

Certificate Employee Email
Medical John Jones mailto:[email protected]
Health & Safety John Jones mailto:[email protected]
Driving John Jones mailto:[email protected]
Swimming Mike Smith mailto:[email protected]
Health & Safety Mike Smith mailto:[email protected]
Working at heights Kevin Peterson mailto:[email protected]
Cooking Kevin Peterson mailto:[email protected]
Driving Kevin Peterson mailto:[email protected]
Medical Kevin Peterson mailto:[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.


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


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