Combine rows in CSV file that have specified department number-CodePudding

I am trying to combine rows that have dept number that starts with '8' into one owneremail([email protected]).

Original:

| Employee ID | OwnerEmail      |DepartmentNumber |Costs    
| --------    | --------        | --------        | --------
| fg345       | [email protected]   |8894             |4654.45
| 78f54       | [email protected]  |3453             |4994.15
| hg764       | [email protected]  |8892             |6543.20

Expectation:

| Employee ID | OwnerEmail    |DepartmentNumber |Costs     | 
| --------    | --------      |--------         |--------  |
| fg345       | [email protected] |8894             |11,197.65 |
| 78f54       | [email protected] |3453             |4994.15   |

What I have attempted so far:

if($_.DepartmentNumber.StartsWith('89')-or $_.DepartmentNumber.StartsWith('9r') -or $_.DepartmentNumber.StartsWith('92') -or $_.DepartmentNumber.StartsWith('hemecor') -or $_.DepartmentNumber.StartsWith('HEMECOR') -or [string]::IsNullOrWhiteSpace($_.DepartmentNumber))   {Group-Object}

Also, I am open to other similar ways of achieving this.

CodePudding user response：

Group-Object should be helpful here

# Import your csv data 
Import-Csv .\grouping.csv | 
    # Use group-object to separate into different groupings
    Group-Object -Property { 
        # use the property parameter and pass it information on how you would like to group
        # here I've choosen to use the first character of the DepartmentNumber value by index (zero-based index)
        $_.DepartmentNumber[0] 
    } | 
        # do something with each group using ForEach-Object
        # Here I'm creating new objects for each group which contain the following properties
        # basically creating a summary with the sum of the costs
        ForEach-Object {
            [PSCustomObject]@{
                EmployeeIDs       = $_.Group.EmployeeID -join ', '
                OwnerEmails       = $_.Group.OwnerEmail -join ', '
                DepartmentNumbers = $_.Group.DepartmentNumber -join ', '
                CostSum           = ($_.Group.Costs | Measure-Object -Sum).Sum
            }
        }

Output

EmployeeIDs  OwnerEmails                   DepartmentNumbers  CostSum
-----------  -----------                   -----------------  -------
78f54        [email protected]                3453               4994.15
fg345, hg764 [email protected], [email protected] 8894, 8892        11197.65

CodePudding user response：

You can create something like "if value is equal or higher 8000, then add to the list, maybe using the command .append(variable)" , like this example:

value = 8001

list=[8446,8654,8123,8999]

if value => 8000: list.append(value)

But consider that my example was in python language, so the append command may not work in other languages