PowerShell Exporting Variables into Excel CSV file

Question

I have created a Power Shell Script for Office 365 groups to export certain data we are looking for.

the part I'm stuck on is when I use Write-Host that creates a variable, this displays correctly, so I know it's getting the data:

Write-Host "Newest Inbox Traffic:" $CheckDate = (Get-MailboxFolderStatistics -Identity $Group.Alias -IncludeOldestAndNewestItems -FolderScope Inbox).NewestItemReceivedDate -ForegroundColor White

Now when I create the export to Excel, I'm selecting fields in the order I want them to appear in the table but how do I add the variable:

Get-UnifiedGroup -Identity $Group.Id | 
Select DisplayName, AccessType, WhenCreated, WhenChanged, $CheckDate, GroupMemberCount, GroupExternalMemberCount, IsMembershipDynamic | 
Export-CSV $CSVPath2  -NoTypeInformation -Append

Ideally, I would also like to label the variable in excel so it doesn't show in the title column as $CheckDate

Here is full code, this might explain what I'm trying to achieve, the display on screen part works perfectly, I just need that to transfer to Excel:

$CSVPath = "C:\AllGroupNoMembers.csv"
$CSVPath2 = "C:\AllGroupNoOwner.csv"
$TodaysDate = (Get-Date)



#Connect to Exchange Online
Connect-ExchangeOnline -ShowBanner:$False
 
#Remove the CSV file if exists
If(Test-Path $CSVPath) { Remove-Item $CSVPath}
If(Test-Path $CSVPath) { Remove-Item $CSVPath2}


#Find M365 groups with no owners
$o365GroupsNO=Get-UnifiedGroup | Where-Object {-Not $_.ManagedBy}
ForEach ($Group in $o365GroupsNO)
{

$CheckDate=(Get-MailboxFolderStatistics -Identity $Group.DisplayName -IncludeOldestAndNewestItems -FolderScope Inbox).NewestItemReceivedDate
$Members=(Get-UnifiedGroupLinks -Identity $Group.DisplayName -LinkType Members) -join '; '


#Display on screen
    Write-Host "Group Name:" $Group.DisplayName -ForegroundColor Green
    Write-Host "Owner:" $Group.ManagedByDetails -ForegroundColor Red
    Write-Host "Name:" $Group.DisplayName -ForegroundColor Yellow
    Write-Host "Access:" $Group.AccessType -ForegroundColor Yellow
    Write-Host "Created:" $Group.WhenCreated -ForegroundColor White
    Write-Host "Changed:" $Group.WhenChanged -ForegroundColor White
    Write-Host "Newest Inbox Traffic:" $CheckDate -ForegroundColor White
    Write-Host "Group Member Count:" $Group.GroupMemberCount -ForegroundColor Gray
    Write-Host "Group Members:" $Members -ForegroundColor Gray
    Write-Host "Ext Group Member Count:" $Group.GroupExternalMemberCount -ForegroundColor Gray
    Write-Host "Dynamic Membership:" $Group.IsMembershipDynamic -ForegroundColor Gray
    Write-Host "SharePoint URL:" $Group.SharePointSiteUrl -ForegroundColor Gray
    Write-Host "SharePoint Document URL:" $Group.SharePointDocumentsUrl -ForegroundColor Gray
    Write-Host "Notes:" $Group.Notes -ForegroundColor Gray

}
#Export to CSV
 Get-UnifiedGroup -Identity $Group.Id | Select DisplayName, AccessType, WhenCreated, WhenChanged, $CheckDate, GroupOwnerCount, GroupMemberCount, $Members, GroupExternalMemberCount, IsMembershipDynamic, SharePointSiteUrl, SharePointDocumentsUrl, Notes| Export-CSV $CSVPath2  -NoTypeInformation -Append -Force

#Disconnect Exchange Online
Disconnect-ExchangeOnline -Confirm:$False

Your Write-Host ... $CheckDate = ... isn't assigning a value to a variable called $CheckDate - it's printing the contents of a variable called $CheckDate (which is unassigned unless you've previously given it a value). Your code is equivalent to Write-Host @( "Newest Inbox Traffic:", $CheckDate, "=", (Get-MailboxFolderStatistics ...).NewestItemReceivedDate ) -ForegroundColor White.. Try assigning the value to the $CheckDate variable on a separate line and then just Write-Host "Newest Inbox Traffic: $CheckDate" -ForegroundColor White. — mclayton
– mclayton, Commented Jul 8, 2024 at 13:11

Dom · Accepted Answer · 2024-07-12 09:00:45Z

0

Since "the display on screen part works perfectly" here is my edited answer:

    $o365GroupsNO=Get-UnifiedGroup | Where-Object {-Not $_.ManagedBy}
    $CSVPath2 = "C:\AllGroupNoOwner.csv"
    $FileHeaderValue="DisplayName;AccessType;WhenCreated;WhenChanged;CheckDate;GroupOwnerCount;GroupMemberCount;Members;GroupExternalMemberCount;IsMembershipDynamic;SharePointSiteUrl;SharePointDocumentsUrl;Notes"
    New-Item -ItemType File -Path $CSVPath2 
    Add-content -path $CSVPath2 -value $FileHeaderValue 
    ForEach ($Group in $o365GroupsNO){
       $CheckDate=(Get-MailboxFolderStatistics -Identity $Group.DisplayName -IncludeOldestAndNewestItems -FolderScope Inbox).NewestItemReceivedDate
       $Members=(Get-UnifiedGroupLinks -Identity $Group.DisplayName -LinkType Members) -join '; '
       #Adding group line to CSV
       $CSVgroupValue="$($Group.DisplayName);$($Group.AccessType);$($Group.WhenCreated);$($Group.WhenChanged);$CheckDate;$($Group.GroupOwnerCount);$($Group.GroupMemberCount);$Members;$($Group.GroupExternalMemberCount);$($Group.IsMembershipDynamic);$($Group.SharePointSiteUrl);$($Group.SharePointDocumentsUrl);$($Group.Notes)"
       Add-content -path $CSVPath2 -value $CSVgroupValue
    }
    #Disconnect Exchange Online
    Disconnect-ExchangeOnline -Confirm:$False

I only changed the output from write-host to Add-content so there should be no problems. I added "GroupOwnerCount" to the output which was not part of your write-host statements put part of your inquiry.

edited Jul 12, 2024 at 9:00

answered Jul 8, 2024 at 16:31

Dom

4184 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

mclayton Over a year ago

Is there a reason you’re avoiding Export-Csv? That will handle some edge cases that your code won’t - what happens in your code if displayname contains a semicolon or a line break, for example?

Michael Frost Over a year ago

This didn't work it only deployed one result as a custom object in Excel

Dom Over a year ago

can u post the content of the csv file copied from text editor? @mclayton to break down the problem and take it step by step

Dom Over a year ago

since u allready can ouput the fields yust try to create $string=" $($Group.DisplayName);$($$Group.ManagedByDetails);$($Group.AccessType)" and than add-content -path "C:\path\to\my365gtoups.csv“ -value $string for each of your objects, i will ajust my solution tomorrow for clarity.

Collectives™ on Stack Overflow

PowerShell Exporting Variables into Excel CSV file

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related