Append field to a cmdLet export

PowerShell Help
1

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

 

2 
# 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.

3

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 :slight_smile:
Thanks
I really appreciate your help
Andrea

4

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. :slight_smile:

 

5

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

}

 

6

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
7

Thanks you all for the answers. You are very kind and helpful.
Unfortunately I can not get what I need :frowning:
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