How to export a new separate csv in PowerShell

Question

I have an existing csv file that have multiple columns, I'm trying to use the "Owner" column value and check if that user exists in AzureAD or not.

If a user exists then Ignore and if it's not then export it to the new CSV file called $OrphanOneDrive.

I already have this script so far but some reason it's not working yet so I would be really appreciated if I can get any help or suggestion.

I check it using status message because this is how it look if a user in not existed.


$CSVImport = Import-CSV $LitHoldUserSites

ForEach ($CSVLine in $CSVImport) {

    $CSVOwner = $CSVLine.Owner
    try{
        $CheckinAzureAD = Get-AzureADUser -ObjectId $CSVOwner 
    }catch{
        $StatusMessage = $_.Exception.Message
        if($Null -eq $StatusMessage ){
            #Ignore because User Exists
        }else{
            #Export to new csv for every owner that got an error
            $CheckinAzureAD  | Export-Csv $OrphanOneDrive -notypeinformation -force
        }

    }

so, if a user does not exist, then the variable $CheckinAzureAD becomes null, because of this Export-Csv is never called. Maybe you want to export the CSV line were the user couldnt be found, in which ase it would be $CSVLine | Export-Csv... — Santiago Squarzon
– Santiago Squarzon, Commented Sep 21, 2022 at 17:14
Hi @SantiagoSquarzon , It work and thank you for your help but one more quick question. How can I append the list because right now only 1 item show up in my new csv file and it just got replaced instead of appending the list. — aasenomad
– aasenomad, Commented Sep 21, 2022 at 17:24
I should clarify on my last comment to avoid any misunderstanding, Export-Csv is called but only it's begin and end blocks are called, however the logic of this cmdlet runs in the process block. — Santiago Squarzon
– Santiago Squarzon, Commented Sep 21, 2022 at 17:32

Sage Pourpre · Accepted Answer · 2022-09-21 17:39:15Z

1

You already got an answer in the comments but that being said, I want to submit a different take on this problem.

Instead of doing it your way, I would suggest getting all the users first, then working with the local cache of user you just obtained. This should enhance performance since you only have to make a single call to Get-AzureADUser instead of many.


# Getting all users once
$AllUsers = Get-AzureADUser -All $true

$CSVImport = Import-CSV $LitHoldUserSites
$OrphanedUsers = [System.Collections.Generic.List[PSObject]]::new()
ForEach ($CSVLine in $CSVImport) {

    $CSVOwner = $CSVLine.Owner
    # Same as Get-AzureADUser -ObjectId $CsvOwner but we don't have to make that external call anymore
    $CheckinAzureAD = $AllUsers | Where ObjectId -eq $CSVOwner
    if ($null -eq $CheckinAzureAD) {
        # Instead of exporting each users individually, we collect the users
        $OrphanedUsers.Add($CSVLine) 
        Continue 
    }
   
}
# 1 single export vs exporting each users is more efficient.
$OrphanedUsers | Export-Csv $OrphanOneDrive -NoTypeInformation

answered Sep 21, 2022 at 17:39

Sage Pourpre

10.4k3 gold badges30 silver badges43 bronze badges

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

1 Comment

Santiago Squarzon Over a year ago

This wouldn't be an option in a Tenant having hundreds of thousands of users. OP's code is following a sensible logic, the minor improvement he could do is using a List<T> to collect all rows in the CSV in the catch block and then export it

Collectives™ on Stack Overflow

How to export a new separate csv in PowerShell

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related