Author Posts

May 24, 2017 at 1:00 am

I have a Csv file with following data with two cloumns

Region Type
—— ———–
EuropeWest Operational
EuropeWest Operational
EuropeWest Operational
EuropeWest Operational
EuropeWest Operational
EuropeWest Operational
EuropeWest Operational
EuropeWest Operational
EuropeWest Operational
EuropeWest Operational
EuropeWest Operational
EuropeWest Operational
EuropeWest Operational
EuropeWest Operational
EuropeWest Operational
EuropeWest Operational
EuropeWest Operational
EuropeWest Operational
EuropeWest Operational
EuropeWest Operational
EuropeWest Operational
EuropeWest Operational
EuropeNorth MigrateSource
EuropeNorth Operational
USCentral Operational
USCentral Operational
AsiaEast MigrateSource
AsiaEast Operational
AsiaEast Operational
AsiaEast Operational

how can i get the count in this way in three columns and there count

Region Operational MigrateSource
—— ———– ———–
EuropeWest 22 0
EuropeNorth 1 1
USCentral 2 0
AsiaEast 3 1

May 24, 2017 at 2:06 am

The request demands an unnecessary level of complexity in the output. Here's the code the produces the desired output:

$RawData  = Import-Csv .\data1.csv

$Operational   = $RawData | ? { $_.Type -eq 'Operational' }   | 
    Group-Object -Property Region | Select Name,Count | sort Name
$MigrateSource = $RawData | ? { $_.Type -eq 'MigrateSource' } | 
    Group-Object -Property Region | Select Name,Count | sort Name

$myOutput = foreach ($Item in $Operational) {
    $FoundMatch = $false
    $MigrateSource | % {
        if ($Item.Name -eq $_.Name) {
            $FoundMatch = $true
            [PSCustomObject]@{
                Region        = $Item.Name
                Operational   = $Item.Count
                MigrateSource = $_.Count
            }
        }
    }
    if (! $FoundMatch) {
        [PSCustomObject]@{
            Region        = $Item.Name
            Operational   = $Item.Count
            MigrateSource = 0
        }
    }
} 

foreach ($Item in $MigrateSource) {
    $FoundMatch = $false
    $Operational | % {
        if ($Item.Name -eq $_.Name) {
            $FoundMatch = $true
            [PSCustomObject]@{
                Region        = $Item.Name
                MigrateSource = $Item.Count
                Operational   = $_.Count
            }
        }
    }
    if (! $FoundMatch) {
        [PSCustomObject]@{
            Region        = $Item.Name
            MigrateSource = $Item.Count
            Operational   = 0
        }
    }
    if ($_ -notin $myOutput) { $myOutput += $_ }
} 

$myOutput | FT -a 

and here's how the output looks like:

Region      Operational MigrateSource
------      ----------- -------------
AsiaEast              3             1
EuropeNorth           1             1
EuropeWest           22             0
USCentral             2             0

May 24, 2017 at 10:42 am

close-to-onliner 🙂

$states = 'Operational','MigrateSource'; $csv = Import-Csv .\data1.csv;
 $csv | Group-Object -Property Region | ForEach-Object {
 $types = $_.Group | Group-Object Type -AsHashTable;
 $states | Foreach-Object -Begin { $o = [PSCustomObject]@{ Region = $_.Name } } -Process { $o | Add-Member -MemberType NoteProperty -Name $_ -Value ($types[$_]).Count } -End { $o }
}

May 24, 2017 at 3:17 pm

Dynamic version, creates a column for each value by name, in case the types expand. Columns are sorted alphabetically.

$types = $csv |select -Unique -expand Type                                                                                                                          
$regions = $csv |select -Unique -expand Region
$s = [array]"Region" + ($types | Sort)
$regions | %{
  $obj = New-Object –TypeName PSObject
  $r = $_
  $obj | Add-Member –MemberType NoteProperty –Name Region -value $r
  $types | %{
    $t = $_
    $obj | Add-Member –MemberType NoteProperty –Name ($t) -value ($csv|?{$_.Region -eq $r -and $_.Type -eq $t} | Measure-Object).Count
  }
  $obj
} | Select $s

May 24, 2017 at 4:57 pm

Thanks Sam,Max & Ron –It is working–That was a quick help !!!

I would prefer Ron Code as it is taking less time in execution..

May 24, 2017 at 6:50 pm

I have one more question
Can we achieve the same output if we have two arrays and region column has no same values
like

$data1=

Region Type
—— ———–
EuropeWest Operational
EuropeWest Operational
EuropeWest Operational
EuropeNorth Operational
USCentral Operational
USCentral Operational
AsiaEast Operational
AsiaEast Operational
AsiaEast Operational

$data2=

Region Type
—— ———–
EuropeWest MigrateSource
EuropeWest MigrateSource
EuropeNorth MigrateSource
USCentral MigrateSource
USCentral MigrateSource

output should be :
Region Operational MigrateSource
—— ———– ———–
EuropeWest 4 2
EuropeNorth 1 1
USCentral 2 0
AsiaEast 3 1

May 25, 2017 at 6:24 am

What stop you to $data = $data1 + $data2 ??