Get-Content, replace old, new

PowerShell Help
1

I have this csv stored as 

$CSV = ‘Employees.csv’

“Response Number”,“Submission Date”,“Submitted By”,“Last Edit Date”,“Last Edited By”,“Type of Form”,“Date Effective”,“Immediate Termination”,“First Name”,“Middle Initial/Name”,“Last Name”,“Additional Comments”,“Job Title”,“Title Changed?”,“Directly Reports to:”,“Job Status”,“Branch Number/Cost Center”,“Branch/Department”,“Now Directly Reports To:”,“Old Branch Number/Cost Center”,“Old Branch/Department”
“2480”,“2016-12-01 14:05:02”,“Katie Allen”,“”,“”,“New Hire”,“2016-12-27 00:00:00”,“”,“Person”,“”,“8”,“”,“Assistant Manager”,“”,“Mitzi Gipson”,“Full-Time”,“8606”,“Hattiesburg”,“”,“”,“”
“2476”,“2016-11-30 10:55:57”,“Brooke Smith”,“”,“”,“Deletion/Termination”,“2016-11-29 00:00:00”,“Yes”,“Person”,“”,“1”,“”,“Account Representative”,“”,“”,“”,“8027”,“Shelby”,“”,“”,“”
“2460”,“2016-11-28 10:31:54”,“Brooke Smith”,“”,“”,“Transfer”,“2016-12-05 00:00:00”,“”,“Person”,“”,“2”,“”,“Account Representative”,“No”,“”,“”,“5008”,“VA Beach”,“Angela Richardson-Brown”,“7204”,“Shreveport”
“2459”,“2016-11-28 10:30:43”,“Brooke Smith”,“”,“”,“Transfer”,“2016-11-28 00:00:00”,“”,“Person”,“”,“3”,“”,“Assistant Manager”,“Yes”,“”,“”,“7601”,“Mt Pleasant”,“Katherine Schmidt”,“7604”,“Middleton”
“2479”,“2016-12-01 08:49:14”,“Brooke Smith”,“”,“”,“Deletion/Termination”,“2016-12-01 00:00:00”,“Yes”,“Person”,“”,“4”,“No Longer coming on board”,“Account Representative”,“”,“”,“”,“3514”,“Elyria”,“”,“”,“”
“2453”,“2016-11-25 16:26:02”,“Becky Ratcliff”,“”,“”,“Title Change”,“2016-11-28 00:00:00”,“”,“Person”,“”,“5”,“”,“Assistant Manager”,“”,“”,“”,“8614”,“Tupelo, MS”,“”,“”,“”
“2451”,“2016-11-25 13:22:30”,“Caitlin Morgan”,“”,“”,“Title Change”,“2016-12-01 00:00:00”,“”,“Person”,“”,“6”,“”,“Assistant Manager”,“”,“”,“”,“7001”,“Albany”,“”,“”,“”
“2474”,“2016-11-30 09:40:19”,“Katie Allen”,“”,“”,“New Hire”,“2016-12-05 00:00:00”,“”,“Person”,“”,“9”,“”,“Assistant Manager”,“”,“Angie Doran”,“Full-Time”,“7305”,“Huntsville”,“”,“”,“”
“2465”,“2016-11-28 15:36:24”,“Brooke Smith”,“”,“”,“New Hire”,“2017-01-04 00:00:00”,“”,“Sara”,“”,“Lohse”,“”,“Marketing Intern”,“”,“Dara Diniso”,“Intern”,“1059”,“Marketing”,“”,“”,“”
“2452”,“2016-11-25 15:48:51”,“Caitlin Morgan”,“”,“”,“Title Change”,“2016-11-28 00:00:00”,“”,“Person”,“”,“7”,“all Owensboro employees should report to Kevin!”,“Branch Manager”,“”,“”,“”,“3803”,“Owensboro”,“”,“”,“”

I know how to change all “New Hires”, but how would I change just "one of the “New Hires” to “HQ New Hires”?

$Employee_CSV = Import-Csv '\\hqfs1\users\tantony\PowerShell\HRSecurityForms\Employees.csv'.ToString()

$CSV = '\\hqfs1\users\tantony\PowerShell\HRSecurityForms\Employees.csv'

$HQ_Department = "Executive","Central Auto Finance","Central Auto", "Auto", "Central Approval Office", "CAO", "Central Collections", "Collections", "Bankruptcy", "Legal",
                    "Centralized Credit", "Central Credit", "Accounting", "Acquisitions", "Facilities", "Servicing Systems", "Servicing", "Audit", "Compliance", "Business Reporting",
                    "Data", "Human Resources", "HR", "Information Technology", "IT", "Marketing", "Learning & Development", "Training", "Risk", "Baltimore Mortgage", "Mortgage"

$Department = $Employee_CSV."Branch/Department"

For($Queue=0; $Queue -lt $Employee_CSV.Length; $Queue++)
{

$Form_Type = ($Employee_CSV."Type of Form")[$Queue]

    if($HQ_Department -contains $Department[$Queue])
    {
        #Write-Host $Department.GetValue($Queue), $Employee_CSV."Type of Form"[$Queue]

        Write-Host $Queue

        $Test =  $Form_Type[$Queue]

        (Get-Content $CSV) -replace (("New Hire", "HQ New Hire")) | Set-Content $CSV -Force
    }
}

Thanks,

Tony

2

You need to rethink your logic.

You’re already loading the file as a table (remove the .tostring() part). Just loop through the table and change the selected records based on your criteria, then export the whole table at the end.

3

I removed the tostring() from csv

I’m trying to change only the select record from “New Hire” to “HQ New Hire”, that’s the part I’m stuck on.

The Write-Host $Queue is selecting the correct record, just not sure how to replace it.

I’m not sure how to access only one that record. This is what I have now.

It’s replacing all “New Hire” to “HR New Hire”, I only want to change it for index 8.

$Employee_CSV = Import-Csv '\\hqfs1\users\tantony\PowerShell\HRSecurityForms\Employees.csv'

$CSV = '\\hqfs1\users\tantony\PowerShell\HRSecurityForms\Employees.csv'

$HQ_Department = "Executive","Central Auto Finance","Central Auto", "Auto", "Central Approval Office", "CAO", "Central Collections", "Collections", "Bankruptcy", "Legal",
                    "Centralized Credit", "Central Credit", "Accounting", "Acquisitions", "Facilities", "Servicing Systems", "Servicing", "Audit", "Compliance", "Business Reporting",
                    "Data", "Human Resources", "HR", "Information Technology", "IT", "Marketing", "Learning & Development", "Training", "Risk", "Baltimore Mortgage", "Mortgage"

$Department = $Employee_CSV."Branch/Department"

For($Queue=0; $Queue -lt $Employee_CSV.Length; $Queue++)
{

$Form_Type = ($Employee_CSV."Type of Form")[$Queue]

    if($HQ_Department -contains $Department[$Queue])
    {
        Write-Host $Department.GetValue($Queue), $Employee_CSV."Type of Form"[$Queue]

        Write-Host $Queue  
        
        $test = $Employee_CSV."Type of Form"[$Queue]      

        (Get-Content $CSV) -replace ($Employee_CSV."Type of Form"[$Queue], "HQ New Hire") | Set-Content $CSV -Force
    }
}
4

Now I have this in side the if statement.

($Employee_CSV[$Queue]."Type of Form") -replace ('New Hire', 'HQ New Hire') | Set-Content $CSV

It changes that one “New Hire” to “HQ New Hire”, which is good, ofcourse it deletes the whole csv, and now only have “HQ New Hire” in the csv.

I know it’s deleting the rest and keeping “HQ New Hire” because I told it to, but I can’t figure out how to keep the rest, and only change that one word in that one line.

How do I make it still keep the rest of the text?

5

Something like this should work. Create a tool based on this if you are making more changes.

$csv = Import-Csv \\path\to\Employees.csv

# Check each line
$output = 
switch ($csv){
   {$_.'submitted by' -eq 'katie allen'}{$_.'type of form' = 'HQ New Hire'}
   $_ {$_} 
}

# Output csv with new changes
$output | Export-Csv \\path\to\NewEmployees.csv -NoTypeInformation
6

@random commandline

I tried that and it didn’t work either. May be what I’m trying to do is not possible?

7

This will be untested, but should give you and idea of the approach to use.

$CSV = '\\hqfs1\users\tantony\PowerShell\HRSecurityForms\Employees.csv'
$Employee_CSV = Import-Csv $CSV

$HQ_Department = "Executive","Central Auto Finance","Central Auto", "Auto", "Central Approval Office", "CAO", "Central Collections", "Collections", "Bankruptcy", "Legal",
                    "Centralized Credit", "Central Credit", "Accounting", "Acquisitions", "Facilities", "Servicing Systems", "Servicing", "Audit", "Compliance", "Business Reporting",
                    "Data", "Human Resources", "HR", "Information Technology", "IT", "Marketing", "Learning & Development", "Training", "Risk", "Baltimore Mortgage", "Mortgage"

foreach ($emp in $Employee_CSV) {
  if($HQ_Department -contains $emp."Branch/Department" -and $emp."Type of Form" -eq "New Hire") {
    $emp."Type of Form" = "HQ New Hire"
  }
}

$Employee_CSV|Export-CSV -notype $CSV
8

That works perfect, thanks Ron