Powershell CSV ordered Export with specific columns

Question

i have following problem:

I have a query with Get-ADUser and have there specific fields to get (only test query)

$AllADUsers = get-aduser -Identity m.mustermann -Properties * | select mail, title, l, GivenName, Surname, company, department, mobile

Also i must add some static fields how language or comment in to the CSV, i take some variable for that:

$Language = "german"
$Comment = ""
$Link = ""
$gender = ""
$exportto = "C:\Temp\Export01.csv"

Than i want to export all entries in a ordered UTF8 CSV with Delimiter ":". I do a foreach but always i get mistakes or not complete lists - i show you my code (but i have mistakes)

Foreach ($ADUser in $AllADUsers) {

  $MailAddress = $ADUser.mail
  $FullName = "$ADUser.GivenName" + "$ADUser.Surname"
  $Title = $AdUser.Title
  $Location = $ADUser.l
  $Comment = $Comment
  $Link = $Link
  $MobilePhone = $ADUser.mobile
  $Language = $Language
  $Divison = $ADUser.Department
  $GivenName = $ADUser.GivenName
  $Surname = $ADUser.Surname 


}

$ADUser | Export-CSV -Path $exportto -NoTypeInformation -Delimiter ":" -Encoding UTF8

Can you help me?

The export must be with the ordered list and without "" and just in this order.

Thank you. Max

Can you isolate the question to one issue instead mentioning you get mistakes? I think you're assigning the values to the variables, but you never use them. Quick tip: formula for FullName should be `"$($ADUser.GivenName) $($ADUser.Surname)"` — Robert Dyjas, Dec 17 '20 at 09:54
Sorry. Problem is, i got no export in my foreach. Also my values and variables are not in the export CSV. Whats wrong? My export command in the loop don't work and the columns from the select are in the csv, not from my loop — max95, Dec 17 '20 at 10:19
Your query with the paramter `-Identity` can only return one single object. So you do not need a loop. Consider using calculated properties like @robdy's answer down here. ;-) — Olaf, Dec 17 '20 at 10:58
I know the parameter -Identity, this was just in my example, in my right query this is with more results. — max95, Dec 17 '20 at 12:49

score 0 · Answer 1 · answered Dec 17 '20 at 10:49

There are some high-level issues in your code.

Incorrect assignment

In your code you should assign the values to specific properties of $AdUser. Something like:

foreach ($ADUser in $AllADUsers) {
  $ADUser.FullName = "$($ADUser.GivenName) $($ADUser.Surname)"
  $ADUser.Comment = $Comment
  $ADUser.Link = $Link
}

No export

Currently you export only last entry, you should pipe $AllADUsers to Export-Csv:

$AllADUsers| Export-CSV -Path $exportto -NoTypeInformation -Delimiter ":" -Encoding UTF8

No use of Select-Object calculated properties

In general, you could have your code shorten to one-liner like:

$AllADUsers | Select-Object @{n="Comment";{$Comment}},@{n="FullName";e={"$($ADUser.GivenName) $($ADUser.Surname)"}} ... | Export-CSV ...

Read more about calculated properties here.

score 0 · Accepted Answer · answered Dec 17 '20 at 10:58

Your code doesn't work, mainly because you are creating a series of variables that in the end you do not use.

To output to CSV, you need to collect objects with properties, not single variables. A very elegant way is to create those objects using [PsCustomObject] syntax, where at the same time the order in which you set the properties make up for the order in the final output file.

Using the colon as delimiter character is unusual and may get you into trouble when other applications need to read and understand this, but that is up to you.

Another thing is that you mainly seem to use LDAP attribute names, where PowerShell has most of them mapped to more friendly attribute names (like 'City' which maps to LDAP 'l').

Your code revised:

# Get-ADUSer already returns these properties by default:
# DistinguishedName, Enabled, GivenName, Name, ObjectClass, ObjectGUID, SamAccountName, SID, Surname, UserPrincipalName

# your hardcoded static variables:
$Language = "german"
$Comment  = ""
$Link     = ""
$gender   = ""
$exportto = "C:\Temp\Export01.csv"

# these are the extra properties you want to fetch
$userprops = 'EmailAddress','Title','Company','City','Department','MobilePhone'

# if you want this for all users, remove "-Identity 'm.mustermann'" and use "-Filter *" instead
$result = Get-ADUser -Identity 'm.mustermann' -Properties $userprops | ForEach-Object {
    # output an object to be collected in variable $result
    # the order in which you put them also determines the field order in the CSV
    [PsCustomObject]@{
        MailAddress = $_.EmailAddress
        FullName    = $_.DisplayName
        Title       = $_.Title
        Company     = $_.Company
        Location    = $_.City
        Comment     = $Comment      # static field
        Link        = $Link         # static field
        MobilePhone = $_.MobilePhone
        Language    = $Language     # static field
        Department  = $_.Department
        GivenName   = $_.GivenName
        Surname     = $_.Surname
    }
}

$result | Export-CSV -Path $exportto -NoTypeInformation -Delimiter ":" -Encoding UTF8

A note about Export-Csv:

Export-Csv will always quote everything, be it a header or data field.

Simply trying to remove all quotes in a CSV file is risking mis-alignment in the field order and rendering the csv as unusable.
With PowerShell version 7 you have the option to use parameter -UseQuotes AsNeeded, but for older versions, you may safely do this using my function ConvertTo-CsvNoQuotes

^{Since this code is all about getting AD information, I don't see why you would want to construct the FullName here. Just use the DisplayName property that is set in the AD as the code above does}

I will try now import in my program (if it works) - i will answer again. — max95, Dec 17 '20 at 13:09
How i can convert the CSV without quotes with your function and stay encoding to UTF8? With the function i have problem that the special characters are broken ("ü", "ä" )... — max95, Dec 17 '20 at 14:14
@max95 Just use it like `$result | ConvertTo-CsvNoQuotes -Delimiter ":" | Set-Content -Path $exportto -Encoding UTF8`. The function itself does not alter the string encoding, which internally in PowerShell is Unicode (UTF16). — Theo, Dec 17 '20 at 15:41
Perfect. Thank you really Theo. Now i want to remove headers at the $result command. How? -NoTypeInformation doesn't work? :/ — max95, Dec 18 '20 at 08:03
@max95 The function also has a switch `-NoHeaders`, so just add that `ConvertTo-CsvNoQuotes -Delimiter ":" -NoHeaders` — Theo, Dec 18 '20 at 08:33
@max95 For writing UTF8 without BOM, see [this](https://stackoverflow.com/a/63970205/9898643) or [this](https://stackoverflow.com/a/34969243/9898643) In this case, output the file like: `[System.IO.File]::WriteAllText($exportto, ($result | ConvertTo-CsvNoQuotes -Delimiter ":" -NoHeaders))` — Theo, Dec 18 '20 at 09:50

Powershell CSV ordered Export with specific columns

2 Answers2

Incorrect assignment

No export

No use of Select-Object calculated properties