CODEWITHSUNDEEP
powershell
Rahul Trivedi
Rahul Trivedi

Reputation: 124

Transpose CSV Row to Column based on Column Value

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
    }

Upvotes: 1

Views: 293

Answers (1)

TessellatingHeckler
TessellatingHeckler

Reputation: 29033

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

Upvotes: 1

Related Questions