Append field to a cmdLet export

Welcome Forums General PowerShell Q&A Append field to a cmdLet export

This topic contains 6 replies, has 4 voices, and was last updated by

 
Participant
3 months, 1 week ago.

  • Author
    Posts
  • #110792

    Participant
    Points: 1
    Rank: Member

    Hi all, I'm very newbie.. I need an help,please.

    I've a little question/problem (I hope)...

    I've this script

    $list = Import-Csv MyFile.csv

    foreach ($entry in $list)

    {

    Get-ADUser -server $($entry.DC) -Filter * -Properties UserPrincipalName  | export-csv -Append -notypeinformation c:\temp\ADusers.csv

    }

    My CSV file (MyFile.csv) has this structure:

    DC,COMPANY

    dcValue1,Company1

    dcValue2,Company2

    etc......

    The script generate a CSV file that contain this value foreach CVS row

    "UserPrincipalName"

     

    My question is....

    how can add at the end of each CSV row the value $($entry.COMPANY) ?

    Thanks to all

    Best regards

    Andrea

     

  • #110869

    Participant
    Points: 290
    Helping Hand
    Rank: Contributor
    # Name variables descriptively to make it harder to get lost in your code!
    $CompanyList = Import-Csv -Path 'MyFile.csv'
    
    foreach ($Entryin $CompanyList) {
        Get-ADUser -Server $Entry.DC -Filter * -Properties UserPrincipalName |
            Select-Object -Property *, @{
                # This is a 'calculated property' constructed with a hashtable expression
                Name       = "Company"
                Expression = { $Entry.Company }
            } | Export-Csv -Append -NoTypeInformation -Path 'C:\temp\ADusers.csv'
    }

    Something like that, I suppose.

  • #110878

    Participant
    Points: 1
    Rank: Member

    Thanks you very very much Joel !

    Unfortunely doesn't work.

    After fixed this little mistake "foreach ($Entryin $CompanyList)" in "foreach ($Entry in $CompanyList)"  I run the script but obtain this error (foreach CSV row)

    """"""""""""""

    Export-Csv : Cannot append CSV content to the following file: C:\temp\ADusers.csv. The appended object does not have a property that corresponds to the following column: CanonicalName. To

    proceed with mismatched properties, add the -Force switch and retry.

    At C:\temp\PROGETTO_EXPORT_USER_SOLGROUP\1.Script.ps1:10 char:13

    +         } | Export-Csv -Append -NoTypeInformation -Path 'C:\temp\ADusers.csv'

    +             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : InvalidData: (CanonicalName:String) [Export-Csv], InvalidOperationException

    + FullyQualifiedErrorId : CannotAppendCsvWithMismatchedPropertyNames,Microsoft.PowerShell.Commands.ExportCsvCommand

    """"""""""""""
    I add the -Force switch but no C:\temp\ADusers.csv file is generated.

    I try to explain better my what do I need...
    I have a list of Domain Controller, one for Company.
    I need to extract all user for each DC (in my example I wrote only UserPrincipalName but the field are more) and append, foreach user the Company to which it belongs.

    Get-ADUser returns
    Name,Surname,Mail,UserPrincipalName etc...
    I need to obtain
    Name,Surname,Mail,UserPrincipalName....COMPANY (obtained from the csv file)

    I hope I was clear 🙂
    Thanks
    I really appreciate your help
    Andrea

  • #110887

    Participant
    Points: 290
    Helping Hand
    Rank: Contributor

    Looks like you have existing content in the file that also has a CanonicalName property. You can either overwrite the file completely and ignore that, or you can add the CanonicalName property to your AD query as an additional property alongside UserPrincipalName. 🙂

     

  • #110896

    Participant
    Points: 1
    Rank: Member

    Try this.

    $list = Import-Csv "C:\temp\Myfile.txt"
    $csvPath = C:\temp\

    foreach($entry in $list){

    $UserLogin = (Get-ADUser  -Filter * -Properties UserPrincipalName).UserPrincipalName
    $props = [ordered]@{

    'User login' = $UserLogin
    'Company' = $entry.Company
    }

    $obj = New-Object PSObject -Property $props

    $obj | Export-csv $csvPath -Append -noType

    }

     

  • #110899

    Participant
    Points: 324
    Helping Hand
    Rank: Contributor

    Here is an example showing sudo code of what you are trying to do:

    # When you import something into Powershell, like Import-CSV, or
    # use a cmdlet like Get-ADUser, it creates a PSObject.  Below, we
    # are emulating a import a CSV with a column DomainController and Company
    $myCSV = @()
    
    $myCSV += [pscustomobject]@{
        DomainController = 'DC1'
        Company          = "Company1"
    }
    
    $myCSV += [pscustomobject]@{
        DomainController = 'DC2'
        Company          = "Company2"
    }
    
    #Now we imported our mock CSV, we are going to loop through each row
    
    $results = foreach ($row in $myCSV) {
        # Here you are doing a query using the "DomainController" property.  When you do
        # the query, it is returning a PSObject. So, now you have 2 objects
        $adQuery = [pscustomobject]@{SamAccountName='sue123';Name='Sue Smith';}
    
        # Next, we are using Select-Object to generate a new PSObject, glue it all together. We pipe the
        # adQuery which you can choose certain columns or only grab specific columns.
        # To append additional columns, we're using a calculated expression for any additional columns
        $adQuery | Select SamAccountName,
                          Name,
                          @{Name='Company';Expression={$row.Company}},
                          @{Name='DC';Expression={$row.DomainController}}, # A renamed column
                          @{Name='Hobby';Expression={'Kittens'}} # A static value 
    
    }
    
    $results
    

    Output:

    SamAccountName : sue123
    Name           : Sue Smith
    Company        : Company1
    DC             : DC1
    Hobby          : Kittens
    
    SamAccountName : sue123
    Name           : Sue Smith
    Company        : Company2
    DC             : DC2
    Hobby          : Kittens
    
  • #110977

    Participant
    Points: 1
    Rank: Member

    Thanks you all for the answers. You are very kind and helpful.
    Unfortunately I can not get what I need 🙁
    I'm really ignorant ,sorry:(
    I try to explain what I need....be patient

    -CSV ORIGIN-
    DC,COMPANY
    server1,ITALY
    server50,SPAIN

    -SCRIPT-

    $list = Import-Csv CSV_ORIGIN.csv
    foreach ($entry in $list)
    {
    Get-ADUser -server $($entry.DC) -Filter * -Properties Name,Surname,CanonicalName,CN,Company,Description,DisplayName,DistinguishedName,
    EmailAddress,Enabled,GivenName,Office,
    SamAccountName,UserPrincipalName | export-csv -Append -notypeinformation c:\temp\PROJect_EXPORT_USER\ADusers.csv
    }
    

    -RESULT- (ADusers.csv)
    "CanonicalName","CN","Company","Description","DisplayName","DistinguishedName",
    "EmailAddress","Enabled","GivenName","Name","ObjectClass",
    "ObjectGUID","Office","SamAccountName","SID","Surname","UserPrincipalName"
    Here in the file ADusers.csv there is a list of users with properties obtained by Get-ADUser.
    I do not write anything so as not to make reading difficult

    -RESULT THAT I WANT-

    "CanonicalName","CN","Company","Description","DisplayName","DistinguishedName",
    "EmailAddress","Enabled","GivenName","Name","ObjectClass",
    "ObjectGUID","Office","SamAccountName","SID","Surname",
    "UserPrincipalName","COMPANY"
    For each line, I would like the field COMPANY obtained from the CSV Origin (ITALY,SPAIN etc...)

    I hope I explained myself.

    I thank everyone again for the time dedicated to me.

    I really appreciate.

    Andrea

The topic ‘Append field to a cmdLet export’ is closed to new replies.