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
– Nico Nekoru, Commented Jun 24, 2020 at 3:49
It's commas. I spaced it out for easy viewing, but the data is name,login,id,mgrid,mgrname etc,. — user2635212
– user2635212, Commented Jun 24, 2020 at 4:09
What is your full import-csv command? Remember to quote your items, "name","login","id","etc." — Nico Nekoru
– Nico Nekoru, Commented Jun 24, 2020 at 4: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
– Lee_Dailey, Commented Jun 24, 2020 at 4:55

Lee_Dailey · Accepted Answer · 2020-06-24 09:41:19Z

0

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

edited Jun 24, 2020 at 9:41

answered Jun 24, 2020 at 5:39

Lee_Dailey

7,5292 gold badges25 silver badges27 bronze badges

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

4 Comments

AdminOfThings Over a year ago

I would use First mode in your where() method.

Lee_Dailey Over a year ago

@AdminOfThings - OOOO! [grin] i keep forgetting those are there ... thank you for the reminder - added that.

user2635212 Over a year ago

This is exactly what I had in mind! Thank you both.

Lee_Dailey Over a year ago

@user2635212 - you are most welcome! glad to have helped ... [grin]

iRon · Accepted Answer · 2020-06-24 07:37:10Z

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?

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related