none
PowerShell Append to a CSV RRS feed

  • Question

  • I have 2 scripts that run, the first Pulls Several attributes from AD IE Name, Displayname, email, enabled (and several others) I have this writing to a CVS file.

    The Second one Checks Membersof to find a specific Group and comes back with a true/false response, what I need to be able to is Append a new column in to the current CSV with the true false response. 

    The Import file is a text file with UserID's.

     
    Get-Content .\users.txt | ForEach-Object { 
    Get-ADUser -Filter  {Name -eq $_} -Property Name,displayName,Enabled,LockedOut,PasswordExpired,EmailAddress,msExchHideFromAddressLists,msRTCSIP-Userenabled,PasswordLastSet,homeDirectory,DistinguishedName,UID | Select-Object Name,displayName,Enabled,LockedOut,PasswordExpired,PasswordLastSet,EmailAddress,msExchHideFromAddressLists,msRTCSIP-Userenabled,homeDirectory,DistinguishedName,@{name=”UID”;expression={$_.UID -join “;”}}}| Export-CSV "ADinfo.csv" -NoTypeInformation
    
    
    2nd Script
    
    Get-Content C:\DRV\users.txt | ForEach-Object { 
    {if ((Get-ADUser $User -Properties memberof).memberof -like "CN=Securitygroup*"){$true}Else {$false}}
    
    <create csv code>
    }}
    I know you can append to created a running Log but can you append to a new column?

    Thursday, August 20, 2015 6:28 PM

Answers

  • One way to do this is to iterate the existing CSV data, and for each row, output a new object containing all of the properties of the existing row along with any new needed properties.

    Here's a simple example:


    # List of PSObjects, like you get with Import-Csv
    $existingData = @(
      New-Object PSObject -Property @{
        "A" = "A1"
        "B" = "B1"
        "C" = "C1"
      }
      New-Object PSObject -Property @{
        "A" = "A2"
        "B" = "B2"
        "C" = "C2"
      }
    )
    
    # Iterate the object list, and add new property to each
    $existingData | ForEach-Object {
      New-Object PSObject -Property @{
        "A" = $_.A
        "B" = $_.B
        "C" = $_.C
        "D" = "New row data"
      }
    }
    


    -- Bill Stewart [Bill_Stewart]


    Thursday, August 20, 2015 6:39 PM
    Moderator

All replies

  • One way to do this is to iterate the existing CSV data, and for each row, output a new object containing all of the properties of the existing row along with any new needed properties.

    Here's a simple example:


    # List of PSObjects, like you get with Import-Csv
    $existingData = @(
      New-Object PSObject -Property @{
        "A" = "A1"
        "B" = "B1"
        "C" = "C1"
      }
      New-Object PSObject -Property @{
        "A" = "A2"
        "B" = "B2"
        "C" = "C2"
      }
    )
    
    # Iterate the object list, and add new property to each
    $existingData | ForEach-Object {
      New-Object PSObject -Property @{
        "A" = $_.A
        "B" = $_.B
        "C" = $_.C
        "D" = "New row data"
      }
    }
    


    -- Bill Stewart [Bill_Stewart]


    Thursday, August 20, 2015 6:39 PM
    Moderator
  • Trying to hack my way through it now, Ill let you know how it goes.

    Thursday, August 20, 2015 9:58 PM
  • Guesswork will only make things more difficult.

    My short example illustrates the technique. If you read and understand it, you will be able to apply it to your scenario to get the results you need.


    -- Bill Stewart [Bill_Stewart]

    Thursday, August 20, 2015 10:02 PM
    Moderator
  • So far I've gotten it to Partially work in 2 scenarios.  This is the best it writes to the csv file in the correct place. the only issue is it writes "N" whether or not it is Y or N.   But the Write-host $Net writes the correct response in the ISE display.
    Get-Content C:\DRV\users.txt | ForEach-Object { 
    Get-ADUser -Filter  {Name -eq $_} -Property Name,displayName | Select-Object Name,displayName} | Export-CSV "C:\DRV\Test.csv" -NoTypeInformation
    
    $Users = Get-Content C:\DRV\users.txt 
    foreach($User in $Users) {
    if ((Get-ADUser $User -Properties memberof).memberof -like "CN=SecurityGroup*")
    {$net = "Y"}
    Else
    {$net = "N"}
    Write-Host $net
    $test2 = Import-Csv C:\DRV\test.csv | select-object Name,displayName,@{Name="Internet";Expression={ $net}} | Export-Csv C:\DRV\Test2.csv -notypeinformation
    }
    
    Just using for testing to see if the data is correct.
    C:\DRV\Test.csv
    C:\DRV\Test2.csv

    Based on a little more testing (adding a couple more usernames) when it writes to the new csv file it just uses the last Variable used for $net, So if the user has the group it assigns Y, if the last Doesn't it assigns N. So some how I need my if statement in the @{Name="Internet";Expression={$net} Location where the Variable is but I'm not sure how to get it there and still work.


    • Edited by Technodruid Thursday, August 20, 2015 11:12 PM
    Thursday, August 20, 2015 10:45 PM
  • Look at my example and the comments and make sure you understand it first. It explains fully how to do what you are asking.

    If you don't understand what it's doing, ask for clarification with the part you don't understand.


    -- Bill Stewart [Bill_Stewart]

    Friday, August 21, 2015 2:11 PM
    Moderator
  • Still fairly new to coding. I Pulled in the code you showed but I'm not sure how I populate the Existing Data with my current csv. This data changes every time I run the initial script.

    Okay I figured out the way to pull in the data and have it populate in to the 2nd part of the code.

    Now to try to get the New field to populate.

    • Edited by Technodruid Friday, August 21, 2015 5:28 PM
    Friday, August 21, 2015 5:23 PM
  • Thanks for the help Bill, I had the code for the "securitygroup" I just needed to figure out how to have it cycle through as it was going through each object.  This is what I ended up with:
    $existingData = Import-Csv C:\DRV\test.csv | select-object Name,displayName
    # Iterate the object list, and add new property to each
    $existingData | ForEach-Object {
      New-Object PSObject -Property @{
        "Pic" = $_.Name
        "Name" = $_.displayName
        "Internet" = " $(if ((Get-ADUser $_.Name -Properties memberof).memberof -like "CN=securityGroup*")
    {$net = "Y"}
    Else
    {$net = "N"})$net "
     }
    }| Export-Csv C:\DRV\Test.csv -notypeinformation

    Friday, August 21, 2015 5:55 PM
  • Here's a cleaner copy of your New-Object inside the loop:


     New-Object PSObject -Property @{
      "Pic" = $_.Name
      "Name" = $_.displayName
      "Internet" = (((Get-ADUser $_.Name -Properties memberOf).memberOf) -like "CN=securityGroup*").Count -gt 0
    }
    


    -- Bill Stewart [Bill_Stewart]

    Friday, August 21, 2015 6:14 PM
    Moderator
  • Thanks, does clean it up and still lets me verify. I appreciate your time and patience.
    Friday, August 21, 2015 11:07 PM