Format output from powershell

lee roberts 186 Reputation points
2021-03-18T11:58:52.283+00:00

Good Morning,

I'm trying to format the output from the below PowerShell script into a table so it can be imported into an excel spreadsheet.

Get-ADGroupMember -Identity "GroupA" | ForEach-Object {
Get-ADUser -Identity $_.SamAccountName -Properties EmailAddress, LastLogonDate, Created |
Select-Object -Property Name, GivenName, Surname, UserPrincipalName, EmailAddress, Enabled, LastLogonDate, Created | Format-Table } > C:\Temp\GroupA.txt

But I can't get the formatting right. When I run the about script it comes out like:

Name GivenName Surname UserPrincipalName EmailAddress Enabled LastLogonDate Created
---- --------- ------- ----------------- ------------ ------- ------------- -------
Person A Person A Person.A@mathieu.company .co.uk PersonB@mathieu.company .co.uk True 11/03/2021 13:55:55 02/02/2021 09:43:25

Name GivenName Surname UserPrincipalName EmailAddress Enabled LastLogonDate Created
---- --------- ------- ----------------- ------------ ------- ------------- -------
Person B Person B Person.B@mathieu.company .co.uk PersonB@mathieu.company .co.uk True 11/03/2021 11:42:14 04/12/2020 14:53:45

I have added the -HideTableHeaders but i still get all the blank spaces and excel doesn't recognise the columns

Please help!!

Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,449 questions
0 comments No comments
{count} votes

Accepted answer
  1. Andreas Baumgarten 103.2K Reputation points MVP
    2021-03-18T12:15:38.627+00:00

    Hi @lee roberts ,

    instead of Format-Table and >C:\Temp\groupA.txt you could use Export-Csv.
    CSV files can be opened with Excel.

    https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/export-csv?view=powershell-7.1

    ----------

    (If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

    Regards
    Andreas Baumgarten


0 additional answers

Sort by: Most helpful