Removing String Data from CSV Column

This topic contains 0 replies, has 1 voice, and was last updated by  Forums Archives 5 years, 7 months ago.

  • Author
    Posts
  • #5953

    by allenr74 at 2012-11-13 13:04:15

    I have a csv file that is a source for importing data into AD. The following are the column headers:

    FirstName|LastName|Office|Sector|Practice

    The file has some records with a middle initial or middle name in the FirstName column:

    Alan S.
    Alex John

    Those records with the middle initial or middle name do not update in AD because they do not exist in the FirstName (gn) field in AD.

    I have a command that splits the first name at the space and selects the first record [0]. But how do I write that back to the CSV file without changing the rest of the file so I can update those records properly in AD.

    C:\WINDOWS\system32\WindowsPowerShell\v1.0> foreach ($User in (Import-csv -path E:\data\powershell\Excel\HR_EMPLID_EXP_NOV.csv -Delimiter "|")) {
    $fn = $User.FirstName
    $fn.split()[-0]
    }

    by ArtB0514 at 2012-11-14 09:00:42

    Close. All you need to do is to save the changes and then export them back to a new CSV file.

    $Data = Import-csv -path E:\data\powershell\Excel\HR_EMPLID_EXP_NOV.csv -Delimiter "|"
    foreach ($User in $Data) {$User.FirstName = ($User.FirstName -split " ")[0]}
    $Data | Export-Csv E:\data\powershell\Excel\HR_EMPLID_EXP_NOV.csv -Delimiter "|" -NoTypeInformation

You must be logged in to reply to this topic.