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

[VidRocksKay]

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!

[#2 · Accepted solution]

@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:

[#3]

@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 :) !!

[#4]

Is your question based on the same import-thing as your original question? Perhaps it's better to create a new topic for this :)

[#5]

Hi @Harm_Veenstra

Yes its the same thing. Just that I wanted to combine all 3 steps in to a single script :). I have posted it as a new question here if you can help out - https://techcommunity.microsoft.com/t5/windows-powershell/generating-a-combined-csv-from-multiple-so...

Appreciate it ! Thank you