Powershell, Modify CSV field based on other field in same CSV

Question

I'm stuck trying to find a better way of modifying large (1000+ rows) CSV file than multiple foreach loops.

I have a CSV file with:

Name,login,ID,MGRID,MGRNAME
Bob Smith,bsmith,101,201,Drake Suzy
Suzy Drake,sdrake,201,300,Long Jane
John Bass,jbass,102,201,Drake Suzy
Jane Long,jlong,300,300,Long Jane

I'm trying to find the best way to import the csv and then set the MGRNAME for each employee to match the login of the corresponding MGR. I'd like to see:

Name,login,ID,MGRID,MGRNAME
Bob Smith,bsmith,101,201,sdrake
Suzy Drake,sdrake,201,300,jlong
John Bass,jbass,102,201,sdrake
Jane Long,jlong,300,300,jlong

I've tried importing the csv and then replacing the MGRNAME using:

$Sup = Import-Csv $Csvfile
Foreach ($MGR in $SUP){
$SUP1 = $MGR.MGRId
$SupID = ($sup | Where-Object {$_.login -eq $Sup1}).Login
    Foreach ($ID in $Sup) {
      ($Sup | Where-Object {$_.MGRID -eq $SupID}).MGRNAME = $supId
    }

}

I've also tried using something like:

$Users = Import-Csv $Csvfile
Foreach ($MGR in $users){
$supID=$MGR.MGRID
$RowIndex=[array]::Indexof($MGR.MGR.NAME,"$supID")
}

Any helpful suggestions welcome. Thank you

Your CSV format doesn't seem very correct. What is your delimiter? If it is space it will mess up you data since they are not enclosed in quotes. Are you sure your `Import-CSV` actually worked? You need to specify the header and delimiter for it which you have not done. See `get-help import-csv` — Nico Nekoru, Jun 24 '20 at 03:49
It's commas. I spaced it out for easy viewing, but the data is name,login,id,mgrid,mgrname etc,. — user2635212, Jun 24 '20 at 04:09
What is your full `import-csv` command? Remember to quote your items, `"name","login","id","etc."` — Nico Nekoru, Jun 24 '20 at 04:27
please replace the "easy to read" fake CSV with a real one. that allows folks to use your test data without having to hand edit it. [*grin*] — Lee_Dailey, Jun 24 '20 at 04:55

Lee_Dailey · Accepted Answer · 2020-06-24T09:41:19.007

here's one way to get the job done. [grin] what it does ...

fakes reading in a CSV
when ready to do this with real data, replace the entire #region/#endregion with a call to Import-Csv.
iterates thru the imported list
if the current user ID is the same as the MgrId, set the MgrName to the current login
otherwise, look up the Mgr with a .Where({}) method call with the mode set to First & set the MgrName with the resulting login
shows the result

the .Where({}) lookup could be slow if the import is large. if so, you likely otta replace that with a hashtable for lookups. however, premature optimization is bad coding, so i did not do that. [grin]

also, the export to CSV seems well documented, so i did not include that.

the code ...

#region >>> fake reading in a CSV file
#    in real life, use Import-CSV
$UserList = @'
Name,login,ID,MGRID,MGRNAME
Bob Smith,bsmith,101,201,Drake Suzy
Suzy Drake,sdrake,201,300,Long Jane
John Bass,jbass,102,201,Drake Suzy
Jane Long,jlong,300,300,Long Jane
'@ | ConvertFrom-Csv
#endregion >>> fake reading in a CSV file

foreach ($UL_Item in $UserList)
    {
    if ($UL_Item.Id -eq $UL_Item.MgrId)
        {
        $UL_Item.MgrName = $UL_Item.Login
        }
        else
        {
        $UL_Item.MgrName = $UserList.Where({$_.Id -eq $UL_Item.MgrId}, 'First', 1).Login
        }
    }

$UserList |
    # remember - DO NOT use the "Format-*" cmdlets for anything other than _final output as plain text_ [*grin*] 
    Format-Table

the output ...

Name       login  ID  MGRID MGRNAME
----       -----  --  ----- -------
Bob Smith  bsmith 101 201   sdrake 
Suzy Drake sdrake 201 300   jlong  
John Bass  jbass  102 201   sdrake 
Jane Long  jlong  300 300   jlong

@AdminOfThings - OOOO! [*grin*] i keep forgetting those are there ... thank you for the reminder - added that. — Lee_Dailey, Jun 24 '20 at 09:38
@user2635212 - you are most welcome! glad to have helped ... [*grin*] — Lee_Dailey, Jun 24 '20 at 14:20

score 0 · Answer 2 · answered Jun 24 '20 at 07:37

Joining objects is a quite common activity in PowerShell, therefore I have created a 'Join-Object' cmdlet sometime ago which I still maintaining by adding features along with using a hashtable (for performance reasons as @Lee_Dailey mentiones) and adding self-join capabilities:

InnerJoin $UserList -on MgrId -eq id -discern '',MGR |
Select-Object Name, Login, ID, MGRID, MGRLogin | Format-Table

Name       login  ID  MGRID MGRlogin
----       -----  --  ----- --------
Bob Smith  bsmith 101 201   sdrake
Suzy Drake sdrake 201 300   jlong
John Bass  jbass  102 201   sdrake
Jane Long  jlong  300 300   jlong

Note that the column header MGRLogin doesn't exactly match the example in your question (which is actually inconsistent with the employee Name and - Login headers), but if you really want the same header you might do:

InnerJoin $UserList -on MgrId -eq id -discern '',MGR |
Select-Object Name, Login, ID, MGRID, @{n='MGRName'; e={$_.MGRLogin}} | Format-Table

For more details, see the embedded help (Help Join-Object) and In Powershell, what's the best way to join two tables into one?

Powershell, Modify CSV field based on other field in same CSV

2 Answers2