Add New column and values in CSV

This topic contains 1 reply, has 2 voices, and was last updated by  Curtis Smith 1 week, 5 days ago.

  • Author
    Posts
  • #85363

    Hrvoje
    Participant

    Hi everyone,

    I'm trying to customize my report for DSC, and I need help with customizing csv file.
    This is just part of my CSV file:

    "PSComputerName","ResourceName","InstanceName","InDesiredState","ConfigurationName","StartDate"
    "P5W-FR4-DSC","Script","Firewall","False","VM_ibfr4eq","11/30/2017 12:44:17 PM"
    "P5W-FR4-DSC","Script","HighPerformance","True","VM_ibfr4eq","11/30/2017 12:44:17 PM"
    "P5W-FR4-DSCTEST","Script","Firewall","True","VM_ibfr4eq","11/30/2017 12:44:18 PM"
    "P5W-FR4-DSCTEST","Script","HighPerformance","True","VM_ibfr4eq","11/30/2017 12:44:19 PM"
    "P5W-FR4-MNG-1","WindowsFeature","Telnet","False","VM_ibfr4eq","11/30/2017 12:44:23 PM"
    "P5W-FR4-MNG-1","WindowsFeature","SMB","False","VM_ibfr4eq","11/30/2017 12:44:23 PM"

    My goal is to add new column and values. For example, If I have value InDesiredState "false", then I want to create new column "Status" with value "False" for every computer which has at least one resource which is not in desired state, otherwise I want to create same column with value "True".

    This is example of my script, I succeeded to create column with value false, but i have problem when I'm trying to add value "True" for servers which has all resource in desired state (I get duplicate rows in CSV). Maybe this is wrong aproach, can you point me into the right direction.

    $csv1 = Import-csv "C:\Users\hbasic\Desktop\results.csv" | where {$_.InDesiredState -eq "False"}
    $a = $csv1.pscomputername
    $b = $a | sort -Unique
    
    
    
     
    $csv = Import-Csv "C:\Users\hbasic\Desktop\results.csv" | ForEach-Object  {
        $Data1 = 'False'
        $Data2 = 'True'
        $ServerName = $_.PScomputerName
        $Resource = $_.ResourceName
        $Instance = $_.InstanceName
        $State = $_.InDesiredState
        $Configuration = $_.ConfigurationName
        $Date = $_.StartDate
    
        For ($i = 0; $i -lt $b.count ; $i++) {
            If ($ServerName -eq $b[$i]) {
             $_ | Add-Member -MemberType NoteProperty -Name Status -Value $Data1 -PassThru -Force
            } 
            
            
        } 
        
    
        }  | Export-Csv C:\Users\hbasic\Desktop\revised.csv

    Thank you in advance!

  • #85535

    Curtis Smith
    Participant

    You haven't shown what you are doing to try and add the "True" columns, but it does appear you are making it a little more complicated than it needs to be. You don't need to read your dataset in multiple times, you don't need to assign each object property to a variable if you are not going to use them for something, and you don't need to create your own for loop to check if the current server is in your list of false desired state servers. That Extra for loop is what is making it difficult. If you eliminate that and use the -in operator instead. It makes it much easier.

    $dataset = Import-Csv "C:\Users\hbasic\Desktop\results.csv"
    $falseDesiredState = $dataset | Where-Object {$_.InDesiredState -eq $false} | Select-Object -ExpandProperty PSComputerName -Unique
    
    $dataset | ForEach-Object {
        If ($_.PSComputerName -in $falseDesiredState) {
            $_ | Add-Member -MemberType NoteProperty -Name Status -Value $False
        } Else {
            $_ | Add-Member -MemberType NoteProperty -Name Status -Value $True
        }
    }
    
    $dataset | Export-Csv "C:\Users\hbasic\Desktop\revised.csv"

    Of course there are always multiple ways to do things such as:

    $dataset = Import-Csv "C:\Users\hbasic\Desktop\revised.csv"
    
    $dataset | ForEach-Object {
        $server = $_.PSComputerName
        If ($false -in ($dataset | Where-Object {$_.PSComputerName -eq $server} | Select-Object -ExpandProperty InDesiredState)) {
            $_ | Add-Member -MemberType NoteProperty -Name Status -Value $False
        } Else {
            $_ | Add-Member -MemberType NoteProperty -Name Status -Value $True
        }
    }
    
    $dataset | Export-Csv "C:\Users\hbasic\Desktop\revised.csv"

    Or Even:

    Import-Csv "C:\Users\hbasic\Desktop\revised.csv" |
    Group-Object PSComputerName |
    ForEach-Object {
        if ($false -in $_.group.InDesiredState) {
            $_.group |
            ForEach-Object {
                $_ | Add-Member -MemberType NoteProperty -Name Status -Value $False -PassThru
            }
        } Else {
            $_.group |
            ForEach-Object {
                $_ | Add-Member -MemberType NoteProperty -Name Status -Value $True -PassThru
            }
        }
    } | Export-Csv "C:\Users\hbasic\Desktop\revised.csv"

You must be logged in to reply to this topic.