Hi @Anonymous
I have used .Net script for your use case and got your expected output. Please follow the below approach.
My CSV file looks like below table:
Flow screenshot:
PAD Output:
Code:
File.ReadFromCSVFile.ReadCSV CSVFile: $'''C:\\Boot\\Email_CSV.csv''' Encoding: File.CSVEncoding.UTF8 TrimFields: True FirstLineContainsColumnNames: True ColumnsSeparator: File.CSVColumnsSeparator.SystemDefault CSVTable=> CSVTable
Excel.LaunchExcel.LaunchUnderExistingProcess Visible: True Instance=> ExcelInstance
Variables.CreateNewDatatable InputTable: { } DataTable=> DataTable
Scripting.RunDotNetScript Language: System.DotNetActionLanguageType.CSharp Script: $''' // Create a new DataTable for the output
outputTable = new DataTable();
outputTable.Columns.Add(\"ID\", typeof(int));
// Group rows by ID
Dictionary<int, List<string>> groupedEmails = new Dictionary<int, List<string>>();
foreach (DataRow row in inputTable.Rows)
{
int id = Convert.ToInt32(row[\"ID\"]);
string email = Convert.ToString(row[\"Email\"]);
if (!groupedEmails.ContainsKey(id))
{
groupedEmails[id] = new List<string>();
}
if (!groupedEmails[id].Contains(email))
{
groupedEmails[id].Add(email);
}
}
// Add columns for each email
foreach (var emails in groupedEmails.Values)
{
for (int i = 0; i < emails.Count; i++)
{
string columnName = \"Email\" + (i + 1);
outputTable.Columns.Add(columnName, typeof(string));
}
break;
}
// Populate the output table
foreach (var kvp in groupedEmails)
{
DataRow newRow = outputTable.NewRow();
newRow[\"ID\"] = kvp.Key;
for (int i = 0; i < kvp.Value.Count; i++)
{
newRow[\"Email\" + (i + 1)] = kvp.Value[i];
}
outputTable.Rows.Add(newRow);
}''' @'name:inputTable': CSVTable @'type:inputTable': $'''Datatable''' @'direction:inputTable': $'''In''' @'name:outputTable': $'''''' @'type:outputTable': $'''Datatable''' @'direction:outputTable': $'''Out''' @outputTable=> DataTable
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: DataTable.ColumnHeadersRow Column: $'''A''' Row: 1
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: DataTable Column: $'''A''' Row: 2
How to copy/paste the above code into your power automate desktop?
Thanks,
Deenuji Loganathan 👩💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥
-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀
