CODEWITHSUNDEEP
powershellcsv
Melle van Aar
Melle van Aar

Reputation: 27

Edit value in CSV using powershell

For a small project, I want to create AD users from a small CSV file. I made jobs (PowerShell scripts) that run every few minutes, to pick-up the user creation based on the status of the user I define in the script. Per example; when a user is created in the AD, I change the status to addmailbox. Then another job will come and add the mailbox.

Everything is going well, but I noticed a bug. The way I am editing the status in the CSV file is wrong, because I change the status for all users in the CSV when I only want to change one. But I can't seem to get any other method working.

#When encountering any error, stop the script.
$ErrorActionPreference = "Stop";

#Check if there is a data file.
$FileExists = Test-Path C:\inetpub\wwwroot\melle\data.csv

if($FileExists -eq $true) {
    $Users = Import-Csv -Path "C:\inetpub\wwwroot\melle\data.csv"
    foreach ($user in $Users) {
        $Status = $User.Status;
        $userPrincipalName = $User.userPrincipalName;
        $SAMAccountName = $User.SAMAccountName;
        $userInstance =  $User.userInstance;
        $Name = $User.Name;
        $displayName = $User.DisplayName;
        $Path = '"' + $User.Path + '"';
        $GivenName = $User.GivenName;
        $Surname = $User.Surname;
        $SIP = $userPrincipalName;

        if ($Status -eq "CreateUser") {
            try {
                #create user
                Import-Module ActiveDirectory; 
                New-ADUser -SAMAccountName $SAMAccountName -Instance $userInstance -Name $name -DisplayName $displayName -Path "correct.path.com" -GivenName $givenname -Surname $surname -userPrincipalName $userprincipalname -AccountPassword (ConvertTo-SecureString -String "bla" -AsPlainText -Force) -PassThru | Enable-ADAccount;

                #change status
                (Get-Content  C:\inetpub\wwwroot\melle\data.csv) | Foreach-Object {$_ -replace 'CreateUser','AddMailbox'}  | Out-File  C:\inetpub\wwwroot\melle\data.csv

                #exit on completion
                Exit(Write-Host 'User was created in AD.');
            } Catch {
                #write any errors to error log.
                $_ | Out-File C:\inetpub\wwwroot\melle\errors.log -Append;
            }
        } else {
            Exit(Write-Host 'No user with status CreateUser was found.');
        }
    }
}else {
    Exit(Write-Host 'No data.csv file was found.');
}

The way I do it now is (Get-Content C:\inetpub\wwwroot\melle\data.csv) | Foreach-Object {$_ -replace 'CreateUser','AddMailbox'} | Out-File C:\inetpub\wwwroot\melle\data.csv but I want to define it for only the row that the script is talking to in the foreach.

I tried searching for a solution on here and different sites, but I wasn't able to get the solutions others are using to work on my script. Some scripts were too advanced for me to understand.

What would be the correct approach to only change the status for the line I'm working on in the loop?

UPDATE: Solved Working script:

#Check if there is a data file.
$FileExists = Test-Path C:\inetpub\wwwroot\melle\data.csv

if($FileExists -eq $true) {
$Users = Import-Csv -Path "C:\inetpub\wwwroot\melle\data.csv"

$UsersThatNeedToBeCreated = $Users | Where-Object {$_.Status -eq 'CreateUser'};

# Check that we have users that need to be created, might need fine-tuning.
if($UsersThatNeedToBeCreated -eq $null -or $UsersThatNeedToBeCreated.Count -eq 0){
    # Write-Host doesn't have a meaningful return code, so you can separate those lines.

    Exit(Write-Host 'No user with status CreateUser was found.');
}else{
    # This way it's only run once
    Import-Module ActiveDirectory; 
}

$Users | ForEach-Object {
    if($_.Status -eq 'CreateUser'){
        try {
            #create user
            New-ADUser -SAMAccountName $_.SAMAccountName -Instance "'" + $_.userInstance + "'" -Name $_.name -DisplayName $_.displayName -Path "working.path.here" -GivenName $_.givenname -Surname $_.surname -userPrincipalName $_.userprincipalname -AccountPassword (ConvertTo-SecureString -String "Welcome01" -AsPlainText -Force) -PassThru | Enable-ADAccount;

            #change status
            $_.Status = 'AddMailbox';
        } Catch {
            #write any errors to error log.
            $_ | Out-File C:\inetpub\wwwroot\melle\errors.log -Append;
        }
    }
}

$Users | ConvertTo-Csv | Out-File 'C:\inetpub\wwwroot\melle\data.csv'


Exit(Write-Host 'User was created in AD.');
}else {

Exit(Write-Host 'No data.csv file was found.');
}

Upvotes: 1

Views: 1841

Answers (1)

Seth
Seth

Reputation: 1255

As you already noticed, your current approach doesn't work. You're grabbing the whole content of the CSV and replace every instance. That Import-Csv statement actually gives you a data structure that allows you to change values. You just need to write it back afterwards.

This approach still is going to be error prone and you will run into issues with it. If I'm understanding you correctly, you want to keep track of the state and have multiple scripts that do different things. Sooner or later you will encounter situations where they overwrite each others changes and/or lock due to competing access requests. If you want to do it this way you should consider using a database that supports row based locking (most probably do). Otherwise you will need to find a way to make them run sequentially or implement row based locking yourself.

That said, one possible solution could look like the following. I haven't run it but the basic structure should be right. An example with this for overwriting changes would be the long time it can take to create a mailbox. As the file is only read at the start of the script and written at the end, it might change in-between.

#When encountering any error, stop the script.
$ErrorActionPreference = "Stop";

#Check if there is a data file.
$FileExists = Test-Path C:\inetpub\wwwroot\melle\data.csv

if($FileExists -eq $true) {
    $Users = Import-Csv -Path "C:\inetpub\wwwroot\melle\data.csv"

    $UsersThatNeedToBeCreated = $Users | Where-Object {$_.Status -eq 'CreateUser'};

    # Check that we have users that need to be created, might need fine-tuning.
    if($$UsersThatNeedToBeCreated -eq $null -or $UsersThatNeedToBeCreated.Count -eq 0){
        # Write-Host doesn't have a meaningful return code, so you can separate those lines.
        Write-Host 'No user with status CreateUser was found.'
        Exit;
    }else{
        # This way it's only run once
        Import-Module ActiveDirectory; 
    }

    $Users | ForEach-Object {
        $Status = $User.Status;
        $userPrincipalName = $User.userPrincipalName;
        $SAMAccountName = $User.SAMAccountName;
        $userInstance =  $User.userInstance;
        $Name = $User.Name;
        $displayName = $User.DisplayName;
        $Path = '"' + $User.Path + '"';
        $GivenName = $User.GivenName;
        $Surname = $User.Surname;
        $SIP = $userPrincipalName;

        if($_.Status -eq 'CreateUser'){
            try {
                #create user
                New-ADUser -SAMAccountName $SAMAccountName -Instance $userInstance -Name $name -DisplayName $displayName -Path "correct.path.com" -GivenName $givenname -Surname $surname -userPrincipalName $userprincipalname -AccountPassword (ConvertTo-SecureString -String "bla" -AsPlainText -Force) -PassThru | Enable-ADAccount;

                # change status
                $_.Status = 'AddMailbox';
            } Catch {
                # write any errors to error log.
                $_ | Out-File C:\inetpub\wwwroot\melle\errors.log -Append;
            }
        }
    }

    $Users | ConvertTo-Csv -NoTypeInformation | Out-File 'C:\inetpub\wwwroot\melle\data.csv'

    Write-Host 'User was created in AD.'
    Exit
}else {
    Write-Host 'No data.csv file was found.'
    Exit
}

Upvotes: 1

Related Questions