Formatting help with Write-Output on a .CSV

Welcome Forums General PowerShell Q&A Formatting help with Write-Output on a .CSV

This topic contains 5 replies, has 3 voices, and was last updated by

 
Participant
1 year, 11 months ago.

  • Author
    Posts
  • #57278

    Participant
    Points: 0
    Rank: Member

    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

  • #57290

    Participant
    Points: 0
    Rank: Member
    1. Use Import-Csv to pull the CSV data into memory
    2. Get list of all unique UserIDs using either "Select-Object -Unique" or Get-Unique
    3. For each of the unique UserIDs, use Where-Object to filter the CSV
    4. Loop through the records returned from the Where-Object to output to the format you're looking for

    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
    
  • #57293

    Participant
    Points: 0
    Rank: Member

    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?

  • #57343

    Participant
    Points: 0
    Rank: Member

    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

  • #57359

    Participant
    Points: 0
    Rank: Member

    Awesome. Thank you.

  • #57361

    Participant
    Points: 25
    Rank: Member

    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
    

The topic ‘Formatting help with Write-Output on a .CSV’ is closed to new replies.