Powershell replace one instance in a csv-CodePudding

I need to keep track of port assignments for users. I have a csv that contains this:

USERNAME,GUI,DCS,SRS,LATC,TRSP
joeblow,8536,10631,5157,12528,14560
,8118,10979,5048,12775,14413
,8926,10303,5259,12371,14747
,8351,10560,5004,12049,14530
johndoe,8524,10267,5490,12809,14493
,8194,10191,5311,12275,14201
,8756,10813,5714,12560,14193
,8971,10006,5722,12078,14378
janblow,8410,10470,5999,12123,14610
bettydoe,8611,10448,5884,12040,14923
,8581,10965,5832,12400,14230
,8708,10005,5653,12111,14374
,8493,10016,5464,12827,14115

I need to be able to add users and remove users which will leave the csv looking as it does now. I have the remove part with this bit of code:

[io.file]::readalltext("c:\scripts\RNcsv.csv").replace("$username","") | Out-File c:\scripts\RNcsv.csv -Encoding ascii -Force

I tried the reverse of the code above but it does not want to work with empty value in that context. I have been unsuccessful finding a way to add $username to a single record. The first record with an empty name column to be precise. So when joeshmo comes along he ends up in the record below joeblow. This csv represents that people have come and gone.

CodePudding user response：

I would take an object oriented approach using Import-Csv and a re-usable function that takes the input from pipeline:

function Add-User {
    param(
        [Parameter(Mandatory)]
        [string] $Identity,

        [Parameter(Mandatory, ValueFromPipeline, DontShow)]
        [object] $InputObject
    )

    begin { $processed = $false }
    process {
        # if the user has already been added or the UserName column is populated
        if($processed -or -not [string]::IsNullOrWhiteSpace($InputObject.UserName)) {
            # output this object as-is and go to the next object
            return $InputObject
        }
        # if above condition was not met we can assume this is an empty value in the
        # UserName column, so set the new Identity to this row
        $InputObject.UserName = $Identity
        # output this object
        $InputObject
        # and set this variable to `$true` to skip further updates on the csv
        $processed = $true
    }
}

Adding a new user to the Csv would be:

(Import-Csv .\test.csv | Add-User -Identity santiago) | Export-Csv .\test.csv -NoTypeInformation

Note that, since the above is reading and writing to the same file in a single pipeline, the use of the Grouping operator ( ) is mandatory to consume all output from Import-Csv and hold the object in memory. Without it you would end up with an empty file.

Otherwise just break it into 2 steps (again, this is only needed if reading and writing to the same file):

$csv = Import-Csv .\test.csv | Add-User -Identity santiago
$csv | Export-Csv .\test.csv -NoTypeInformation

CodePudding user response：

In addition to where you ask for and @Santiago's helpful answer (and note), you might want to be able to add multiple usernames at once to avoid that you need to recreate the whole file for each user you want to add.

$Csv = ConvertFrom-Csv @'
USERNAME,  GUI,   DCS,  SRS,  LATC,  TRSP
joeblow,  8536, 10631, 5157, 12528, 14560
,         8118, 10979, 5048, 12775, 14413
,         8926, 10303, 5259, 12371, 14747
,         8351, 10560, 5004, 12049, 14530
johndoe,  8524, 10267, 5490, 12809, 14493
,         8194, 10191, 5311, 12275, 14201
,         8756, 10813, 5714, 12560, 14193
,         8971, 10006, 5722, 12078, 14378
janblow,  8410, 10470, 5999, 12123, 14610
bettydoe, 8611, 10448, 5884, 12040, 14923
,         8581, 10965, 5832, 12400, 14230
,         8708, 10005, 5653, 12111, 14374
,         8493, 10016, 5464, 12827, 14115
'@

$NewUser = 'Santiago', '4evernoob', 'mrX', 'iRon' 

$Csv |ForEach-Object { $i = 0 } {
    if (!$_.USERNAME) { $_.USERNAME = $NewUser[$i  ] }
    $_
} |Format-Table

USERNAME  GUI  DCS   SRS  LATC  TRSP
--------  ---  ---   ---  ----  ----
joeblow   8536 10631 5157 12528 14560
Santiago  8118 10979 5048 12775 14413
4evernoob 8926 10303 5259 12371 14747
mrX       8351 10560 5004 12049 14530
johndoe   8524 10267 5490 12809 14493
iRon      8194 10191 5311 12275 14201
          8756 10813 5714 12560 14193
          8971 10006 5722 12078 14378
janblow   8410 10470 5999 12123 14610
bettydoe  8611 10448 5884 12040 14923
          8581 10965 5832 12400 14230
          8708 10005 5653 12111 14374
          8493 10016 5464 12827 14115

Note that an outbound index (as e.g. NewUser[99]) returns a $Null (which is casted to an empty string) by default. This feature will produce an error if you set the StricMode to a higher level.
To overcome this, you might also do something this instead:

if (!$_.USERNAME -and $i -lt @($NewUser).Count) { ...