Get-Content, replace old, new

Tagged: 

This topic contains 7 replies, has 3 voices, and was last updated by Profile photo of Tony Antony Tony Antony 7 months, 4 weeks ago.

  • Author
    Posts
  • #59235
    Profile photo of Tony Antony
    Tony Antony
    Participant

    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

  • #59241
    Profile photo of Ron
    Ron
    Participant

    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.

  • #59250
    Profile photo of Tony Antony
    Tony Antony
    Participant

    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
        }
    }
  • #59254
    Profile photo of Tony Antony
    Tony Antony
    Participant

    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?

  • #59262
    Profile photo of random commandline
    random commandline
    Participant

    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
    
  • #59265
    Profile photo of Tony Antony
    Tony Antony
    Participant

    @random commandline

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

  • #59266
    Profile photo of Ron
    Ron
    Participant

    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
    
    • #59272
      Profile photo of Tony Antony
      Tony Antony
      Participant

      That works perfect, thanks Ron

You must be logged in to reply to this topic.