C# Invoke Code issue for segregating Excel Data

Help Activities
, , , , , , , ,
1

I have an excel which has data in below format … ID column occurs three times & we need to collate all id’s into one & their corresponding adjacent component column values …

ID Comp1 ID Comp2 ID Comp3
12 1232 1 34565 8 90
1 34 2 63 1 45
5 2455 5 23456 6 45
6 67 12 12345 5 45

Now I need result something like this

ID Comp1 Comp2 Comp3
12 1232 12345 0
1 34 34565 45
5 2455 23456 450
6 67 0 45

For that steps I did

I read the maininputfile as it is without headers - since there are duplicates & blank column & I got the indexes of ID columns like list - {0,3,7} in this case & I tried to get the final result table using c# code .
(We know that after first occurrence its always comp1 value , second occurence of empid followed by comp2 & similarly comp3. We only will have 3 occurences of ID column).

C# code I used in invoke code

public static void Main(DataTable dt1, System.Collections.Generic.List emplist, out DataTable resultTable)
{
resultTable = new DataTable();
resultTable.Columns.Add(“Emp ID”, typeof(int));
resultTable.Columns.Add(“OT”, typeof(int));
resultTable.Columns.Add(“NSA”, typeof(int));
resultTable.Columns.Add(“ASA”, typeof(int));

    try
    {
        // Iterate through the rows of the datatable
        foreach (DataRow row in dt1.Rows)
        {
            // Iterate through each Emp ID column index
            for (int iteration = 1; iteration <= 3; iteration++)
            {
                int empIdIndex = emplist[iteration - 1];
                int componentColumnIndex = empIdIndex + iteration;
                int componentValue = 0;

                if (componentColumnIndex < row.ItemArray.Length && row[componentColumnIndex] != DBNull.Value)
                {
                    componentValue = Convert.ToInt32(row[componentColumnIndex]);
                }

                if (iteration == 1)
                {
                    DataRow newRow = resultTable.NewRow();
                    newRow["Emp ID"] = Convert.ToInt32(row[empIdIndex]);
                    newRow["OT"] = componentValue;
                    resultTable.Rows.Add(newRow);
                }
                else
                {
                    DataRow lastRow = resultTable.Rows[resultTable.Rows.Count - 1];
                    lastRow[iteration == 2 ? "NSA" : "ASA"] = componentValue;
                }
            }
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine("Error occurred: " + ex.Message);
    }
}

Its not throwing error or its not even giving output. Please help me if this code format is fine for the requirement or if I’m missing something for invoke code

2

@Ana_Patricia

Instead a simple way would be as follows

  1. Read range with range as A2 and column headers unchecked into dt1
  2. Filter datatable dt1 and select column tab and select keep and give indexes as 4 and 5 and output table as dt3
  3. Filter datatable dt1 and select column tab and select keep and give indexes as 2 and 3 and output table as dt2
  4. Filter datatable and select column tab and select keep and give indexes as 0 and 1 and output table as dt1
  5. Now use join datatable with dt1 and dt2 and left join
  6. With result of step 5 left join dt3…
  7. filter datatable and give column indexes as 0,1,3,5

This would give the required result

Cheers

1 Like
3

Hi @Ana_Patricia

Verify that the resultTable output parameter is correctly assigned after the code execution. Make sure you are capturing the output DataTable returned by this method and processing it appropriately in your UiPath workflow.