How to filter and split a datatable into a list of datatables
💡 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 | |
Medical | John Jones | |
Health & Safety | John Jones | |
Driving | John Jones | |
Swimming | Mike Smith | |
Health & Safety | Mike Smith | |
Working at heights | Kevin Peterson | |
Cooking | Kevin Peterson | |
Driving | Kevin Peterson | |
Medical | Kevin Peterson |
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%.
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
Last updated on: 11 Feb 2023
Tested version(s): 2.28.135.23016
Prerequisites: Excel
Dependencies: None
Known issues: None
References:
Nil