Edit value in CSV using powershell

Question

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.');
}

Seth · Accepted Answer · 2017-04-24 09:52:53Z

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
}

Hi Seth, thanks for your help this far. I like the changes you made, but the status isn't changed in the CSV in this way. It stays the same. Also, you can't use Exit(); without any expressions inside the (), I noticed. The CSV file got this line inserted: #TYPE System.Management.Automation.PSCustomObject. and the user was not created, but I noticed the variables I was using in my script aren't there anymore, so I'll define those again.
Sorry, I was focused on just changing the status. You should be able to run Exit without the parenthesis. It's a bit hard to guess what your actual goal is. Write-Host would write to an actual console and can't easily be redirected. In addition by calling Exit with parenthesis you're (probably) returning the empty result from Write-Host which isn't really going to help you in any way.
You're right, the thing is I don't need to catch those errors, I just want the script to exit upon them happening. The only error I want to log is if anything goes wrong in the command, which is working. I made some small changes and got it working. Thanks alot! I'll edit my post with the updated script.
In addition, as you can see in the edit, you would need to pass -NoTypeInformation in order to skip the ... type information for ConvertTo-Csv. I'd still not advice to use this approach if you have multiple scripts that access this file for the mentioned reasons. If it was helpful, maybe consider marking it as an answer or up-voting if you feel like it.
Thanks again, you were of great help as I was stuck on this for a while. If I had enough rep to upvote, I'd do it :)

Collectives™ on Stack Overflow

Edit value in CSV using powershell

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related