Merge Unique I'd records using power automate desktop

[Anonymous]

I have csv file multiple records for unique I'd and want to merge them into single row. Suppose the unique I'd has 4 email id the it should create single row for each unique I'd and store 4 email id's in different columns. Store this data into new excel. Want to achieve this using power automate desktop. I have attached the images for reference data is like 1st image and want the output as 2nd image.

[Deenuji]

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 🚀

[Anonymous]

Hii 

Thanks for the solution. But I'm getting the below error message in the .net code.

I have attached the SS of the error message.

[Deenuji]

@Anonymous 

I believe you've crafted those steps manually, with some elements copied and pasted.

Please copy the above code like below screenshot:

And create new flow in your power automate desktop and paste like below screenshot

You will see the actions in your PAD (Just change the excel path):

Please note, you excel file must include "ID" and " Email" Columns else .net code may throw the error.

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 🚀

[Anonymous]

@Deenuji 

Below is the c# code. I have copied the steps as you mentioned.

@File.ReadFromCSVFile.ReadCSV CSVFile: $'''C:\Users\Preethi Ambati\Downloads\PersonData.csv''' Encoding: File.CSVEncoding.UTF8 TrimFields: True FirstLineContainsColumnNames: True ColumnsSeparator: File.CSVColumnsSeparator.SystemDefault CSVTable=> CSVTabl
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

But still getting same error.

[Anonymous · Accepted solution]

@Deenuji 

I have pasted the code as mentioned. Still getting the same error message.

I'm attaching the SS of CSV table, create new datatable and run.net script. Let me know if I'm doing something wrong. 

Thanks!

[preettyy_312024]

File.ReadFromCSVFile.ReadCSV CSVFile: $'''C:\Users\Preethi Ambati\Downloads\PersonData.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

This is the c# code.

[Deenuji · Accepted solution]

@Anonymous 

I think you are copy and pasting the code into .Net script actions. Please follow the above suggested steps and copy/paste the code into the PAD. It should work 🙂

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 🚀

[Anonymous]

Yes it's working now. Thank you.