Transpose CSV Row to Column based on Column Value

Question

I am trying to convert a 80K row data csv with 30 columns to sorted and filtered CSV based on specific column data from orignal CSV.

For Example My Data is in below format:

PatchName   MachineName IPAddress       DefaultIPGateway    Domain Name USERID          UNKNOWN NOTAPPLICABLE   INSTALLED   APPLICABLE  REBOOTREQUIRED  FAILED
KB456982    XXX1002     xx.yy.65.148    xx.yy.64.1          XYZ.NET     XYZ\ayzuser     YES     
KB589631    XXX1003     xx.yy.65.176    xx.yy.64.1          XYZ.NET     XYZ\cdfuser             YES
KB456982    ABC1004     xx.zz.83.56     xx.zz.83.1          XYZ.NET     XYZ\mnguser     YES
KB456982    8797XCV     xx.yy.143.187   xx.yy.143.184       XYZ.NET     WPX\abcuser                                                                     YES

Here MachineName would be filtered to Uniq and PatchName would transpose to Last Columns headers with holding "UNKNOWN, NOAPPLICABLE, INSTALLED, FAILED, REBOOTREQUIRED columns Values if YES occurred -

Expected Result:

MachineName IPAddress       DefaultIPGateway    Domain Name USERID          KB456982 KB589631 
XXX1002     xx.yy.65.148    xx.yy.64.1          XYZ.NET     XYZ\ayzuser     UNKNOWN  
XXX1003     xx.yy.65.176    xx.yy.64.1          XYZ.NET     XYZ\cdfuser              NOTAPPLICATBLE
ABC1004     xx.zz.83.56     xx.zz.83.1          XYZ.NET     XYZ\mnguser     UNKNOWN
8797XCV     xx.yy.143.187   xx.yy.143.184       XYZ.NET     WPX\abcuser     FAILED

Looking for help to achieve this, so far I am able to transpose PathcName rows to columns but not able to include all the columns along with and apply the condition. [It takes 40 Minutes to process this]

$b = @()
    foreach ($Property in $a.MachineName | Select -Unique) {
        $Props = [ordered]@{ MachineName = $Property }
        foreach ($Server in $a.PatchName | Select -Unique){ 
            $Value = ($a.where({ $_.PatchName -eq $Server -and $_.MachineName -eq $Property })).NOTAPPLICABALE
            $Props += @{ $Server = $Value }
        }
        $b += New-Object -TypeName PSObject -Property $Props
    }

Note that all properties should exist on the first object of the stream otherwise they will not picked up (or shown) by the next cmdlet. See: https://stackoverflow.com/q/44428189 — iRon, May 13 '18 at 13:13

score 1 · Accepted Answer · answered May 13 '18 at 21:47

This is what I came up with:

$data = Import-Csv -LiteralPath 'C:\path\to\data.csv'

$lookup = @{}
$allPatches = $data.PatchName | Select-Object -Unique

# Make 1 lookup entry for each computer, to keep the username and IP and so on.
# Add the patch details from the current row (might add more than one patch per computer)
foreach ($row in $data)
{
    if (-not $lookup.ContainsKey($row.MachineName))
    {     
        $lookup[$row.MachineName] = ($row | Select-Object -Property MachineName, IPAddress, DefaultIPGateway, DomainName, UserID)
    }

    $patchStatus = $row.psobject.properties | 
            Where-Object {
                $_.name -in @('applicable', 'notapplicable', 'installed', 'rebootrequired', 'failed', 'unknown') -and 
                -not [string]::IsNullOrWhiteSpace($_.value)
            } | 
            Select-Object -ExpandProperty Name

    $lookup[$row.MachineName] | Add-Member -NotePropertyName $row.PatchName -NotePropertyValue $patchStatus

}

# Pull the computer details out of the lookup, and add all the remaining patches
# so they will convert to CSV properly, then export to CSV
$lookup.Values | ForEach-Object {
    $computer = $_
    foreach ($patch in $allPatches | where-object {$_ -notin $computer.psobject.properties.name})
    {
        $computer | Add-Member -NotePropertyName $patch -NotePropertyValue ''
    }
    $computer
} | Export-Csv -LiteralPath 'c:\path\to\output.csv' -NoTypeInformation

Thanks much for your response, I apologise for the late comment - here is what I am getting an error - `Add-Member :Cannot add a member with the name "KB4057117" because a member with that name already exists. To overwrite the member anyway, add the Force parameter to your command. At line:22 char:33 + ..hineName] | Add-Member -NotePropertyName $row.PatchName -NoteProperty + CategoryInfo: InvalidOperation: (@{MachineName=A..U KB4057117=}:PSObject) [Add-Member],InvalidOperationException + FullyQualifiedErrorId :MemberAlreadyExists,Microsoft.PowerShell.Commands.AddMemberCommand` — Rahul Trivedi, May 19 '18 at 23:24
I just suppressed the Error `-ErrorAction SilentlyContinue` - and It's generating the data as expected now I`ll run this on 1.8 Million Rows :) — Rahul Trivedi, May 19 '18 at 23:53

Transpose CSV Row to Column based on Column Value

1 Answers1