Formatting help with Write-Output on a .CSV

PowerShell Help
1

Hi Folks,

I have a report (.csv file) that contains user IDs, domains, and what they have access to in that domain. For users that have access to multiple domains their ID appears multiple times in report.

USERID,DOMAIN,CBM,CCA,CME
IE001,AA,CBM,CCA,CME
IE002,AA,CBM,,CME
IE001,AL,CBM,CCA,CME
IE002,AL,CBM,,CME			
IE003,AL,CBM,,CME

To import this information in to our access review system I need to combine a user’s access rights into a single line then format the output like so into a .txt file:

IE001, AA CBM, AA CCA, AA CME, AL CBM, AL CCA, AL CME

IE002, AA CBM, AA CME, AL CBM, AL CME

IE003, AL CBM, AL CME

How could I accomplish this?

Thanks

2
  1. Use Import-Csv to pull the CSV data into memory
    2. <li>Get list of all unique UserIDs using either "Select-Object -Unique" or Get-Unique</li>

<li>For each of the unique UserIDs, use Where-Object to filter the CSV</li>

<li>Loop through the records returned from the Where-Object to output to the format you're looking for</li>

This may be a good start:

$data = Import-Csv 
$outData = @()
$uniques = $data | Sort-Object UserID | Select-Object UserID -Unique
$uniques | ForEach-Object {
    $outputLine = "$_"
    $filtered = $data | Where-Object UserID -eq $_
    $filtered | ForEach-Object {
        if ( $_.CBM ) {
            $outputLine = $outputLine + ",$_.Domain CBM"
        }
        if ( $_.CCA ) {
            $outputLine = $outputLine + ",$_.Domain CCA"
        }
        if ( $_.CME ) {
            $outputLine = $outputLine + ",$_.Domain CME"
        }
    }
    
    $outData += $outputLine
}

$outData
3

Thanks Charles,

From this script I am getting the following:

$data = Import-Csv C:\data.csv 
$outData = @()
$uniques = $data | Sort-Object UserID | Select-Object UserID -Unique
$uniques | ForEach-Object {
    $outputLine = "$_"
    $filtered = $data | Where-Object UserID -eq $_
    $filtered | ForEach-Object {
        if ( $_.CBM ) {
            $outputLine = $outputLine + ",$_.Domain CBM"
        }
        if ( $_.CCA ) {
            $outputLine = $outputLine + ",$_.Domain CCA"
        }
        if ( $_.CME ) {
            $outputLine = $outputLine + ",$_.Domain CME"
        }
    }
    
    $outData += $outputLine
}

$outData
@{USERID=}
@{USERID=IE001}
@{USERID=IE002}
@{USERID=IE003}

What am I missing?

4

I went back and tested it and made some changes. Try this version. It spit out what I think you were expecting.

$data = Import-Csv C:\Users\Student\Documents\data.csv
$outData = @()
$uniques = $data | Sort-Object UserID | Select-Object UserID -Unique
$uniques | ForEach-Object {
    $userID = $_
    $outputLine = "$($userID.USERID)"
    $filtered = $data | Where-Object {$_.USERID -eq $userId.USERID}
    $filtered | ForEach-Object {
        if ( $_.CBM ) {
            $outputLine = $outputLine + ",$($_.Domain) CBM"
        }
        if ( $_.CCA ) {
            $outputLine = $outputLine + ",$($_.Domain) CCA"
        }
        if ( $_.CME ) {
            $outputLine = $outputLine + ",$($_.Domain) CME"
        }
    }
    
    $outData += $outputLine
}

$outData

IE001,AA CBM,AA CCA,AA CME,AL CBM,AL CCA,AL CME
IE002,AA CBM,AA CME,AL CBM,AL CME
IE003,AL CBM,AL CME

5

Awesome. Thank you.

6

Another way to do it:

$obj = @()
$obj += [pscustomobject]@{
    USERID = "IE001"
    DOMAIN = "AA"
    CBM = "CBM"
    CCA = "CCA"
    CME = "CME"
}

$obj += [pscustomobject]@{
    USERID = "IE002"
    DOMAIN = "AA"
    CBM = "CBM"
    CCA = $null
    CME = "CME"
}
$obj += [pscustomobject]@{
    USERID = "IE001"
    DOMAIN = "AL"
    CBM = "CBM"
    CCA = "CCA"
    CME = "CME"
}
$obj += [pscustomobject]@{
    USERID = "IE002"
    DOMAIN = "AL"
    CBM = "CBM"
    CCA = $null
    CME = "CME"
}
$obj += [pscustomobject]@{
    USERID = "IE003"
    DOMAIN = "AL"
    CBM = "CBM"
    CCA = $null
    CME = "CME"
}

$test = $obj | Select USERID, 
              @{Name = "DOMAINCBM"; Expression = { if ($_.CBM) {"{0} {1}" -f $_.DOMAIN, $_.CBM} else {$null} }},
              @{Name = "DOMAINCCA"; Expression = { if ($_.CCA) {"{0} {1}" -f $_.DOMAIN, $_.CCA} else {$null} }},
              @{Name = "DOMAINCME"; Expression = { if ($_.CME) {"{0} {1}" -f $_.DOMAIN, $_.CME} else {$null} }}

$test | Group-Object -Property USERID | foreach {
    $arr = @()
    $arr += $_.Name
    foreach($item in $_.Group) {
        $arr += $item.DomainCBM
        $arr += $item.DOMAINCCA
        $arr += $item.DOMAINCME
    }
    
    Add-Content -Path C:\MyFile.txt -Value (($arr | Where{$_})  -Join ",")
}

Output:

IE001,AA CBM,AA CCA,AA CME,AL CBM,AL CCA,AL CME
IE002,AA CBM,AA CME,AL CBM,AL CME
IE003,AL CBM,AL CME