Export a combined (from two different sources) result as a CSV using PowerShell

Hi Guys,

I have a requirement to fetch Office 365 users based on a CSV file column (Display Name) and finally update the rest of the columns in it (UPN, First Name, Last Name, IsLicensed etc..) or, export as a brand new consolidated CSV. This is simply because my source CSV have limited info (comes from a different system) and we need it to be filled with more info from O365.

I am a very beginner to PowerShell and have tired the following but it doesn't fetch O365 field values (see the result below)

-----------------------------------------------------------------------------------

$Import = @(Import-Csv D:\Data-31072022\Automation\Source2.csv)
$DisplayName = $Import | Select-Object DisplayName

foreach ($item in $Import) {
$MS = Get-MsolUser -SearchString $_.DisplayName

New-Object PSObject -Property @{
'UserPrincipalName' = $MS.UserPrincipalName
'IsLicensed' = $MS.IsLicensed
'BlockCredential' = $MS.BlockCredential
'FirstName' = $MS.FirstName
'LastName' = $MS.LastName
'ArchiveID' = $item.ArchiveID
'DisplayName' = $item.DisplayName
}
} Select-Object ArchiveName,ArchiveID,UserPrincipalName,IsLicensed,BlockCredential,FirstName,LastName | Export-Csv -NoTypeInformation "D:\Data-31072022\Automation\NewOutput.csv"

--------------------------------------------------------------------------------------------

Result: no values returned for Msol user query

Script

Source CSV

Really appreciate if any of you can shed some lights here guys ! Thanks so much!

VidRocksKay I changed your script a little bit 🙂

$Import = Import-Csv D:\temp\users.csv -Delimiter ';'
$Total = @()
foreach ($item in $Import) {
    $MS = Get-MsolUser -SearchString $Item.DisplayName

    $msoluser = [PSCustomObject]@{
        'UserPrincipalName' = $MS.UserPrincipalName
        'IsLicensed'        = $MS.IsLicensed
        'BlockCredential'   = $MS.BlockCredential
        'FirstName'         = $MS.FirstName
        'LastName'          = $MS.LastName
        'ArchiveID'         = $item.ArchiveID
        'DisplayName'       = $item.DisplayName
    }
    $Total += $msoluser
}
$Total | Select-Object DisplayName, ArchiveID, UserPrincipalName, IsLicensed, BlockCredential, FirstName, LastName | Export-Csv -NoTypeInformation "D:\Temp\NewOutput.csv" -Delimiter ';' -Encoding UTF8

Changed it to a pscustomobject and added every item to a Total variable and saved it to a .csv (Used ; as delimiter, for you it could be a , ) You also had a ArchiveName but I didn't see it in your screenshot, so I removed if for testing but you could add it again 🙂

My users.csv file:

Displayname; ArchiveID
Alex Wilber;1
Allan Deyoung;2

I ran this in my CDX environment for these two users, Excel output:

4 Replies

Harm_Veenstra
MVP
Aug 29, 2022
VidRocksKay I changed your script a little bit 🙂

$Import = Import-Csv D:\temp\users.csv -Delimiter ';' $Total = @() foreach ($item in $Import) { $MS = Get-MsolUser -SearchString $Item.DisplayName $msoluser = [PSCustomObject]@{ 'UserPrincipalName' = $MS.UserPrincipalName 'IsLicensed' = $MS.IsLicensed 'BlockCredential' = $MS.BlockCredential 'FirstName' = $MS.FirstName 'LastName' = $MS.LastName 'ArchiveID' = $item.ArchiveID 'DisplayName' = $item.DisplayName } $Total += $msoluser } $Total | Select-Object DisplayName, ArchiveID, UserPrincipalName, IsLicensed, BlockCredential, FirstName, LastName | Export-Csv -NoTypeInformation "D:\Temp\NewOutput.csv" -Delimiter ';' -Encoding UTF8

Changed it to a pscustomobject and added every item to a Total variable and saved it to a .csv (Used ; as delimiter, for you it could be a , ) You also had a ArchiveName but I didn't see it in your screenshot, so I removed if for testing but you could add it again 🙂

My users.csv file:
Displayname; ArchiveID Alex Wilber;1 Allan Deyoung;2

I ran this in my CDX environment for these two users, Excel output:
- VidRocksKay
  Copper Contributor
  Aug 30, 2022
  Harm_Veenstra
  Thank you so very much and this worked like charm ! Helped me a lot getting this sorted.
  
  If you happen to spare some time, will you kindly be able to help me with adding another source or two combining to the same script? (i.e. ExchangeOnline/Exchange On-Premise in a Hybrid scenario)
  
  ##Exchange Online (Hybrid)-----------------------------
  $EXO = Get-EXOMailbox -Identity $item.UserPrincipalName
  
  $exouser = [PSCustomObject]@{
  'RecipientTypeDetails' = $EXO.RecipientTypeDetails
  'Alias' = $EXO.Alias
  
  #Exchange On-Premise (Hybrid)-----------------
  foreach ($item in $Import) {
  
  $EX = Get-Mailbox -Identity $item.UserPrincipalName
  'ArchiveStatus' = $EX.ArchiveStatus
  'ArchiveState' = $EX.ArchiveState
  
  Thank you heaps again 🙂 !!
  - Harm_Veenstra
    MVP
    Aug 30, 2022
    Is your question based on the same import-thing as your original question? Perhaps it's better to create a new topic for this 🙂

Forum Discussion

Export a combined (from two different sources) result as a CSV using PowerShell

4 Replies

Resources