CODEWITHSUNDEEP
powershell
Aaron
Aaron

Reputation: 41

Powershell csv creating a new column with 2 others

Here is my script so far. I want to create a new column called password but only use the first letter of column Associate_First_Name, the first letter of Associate_Last_Name and the last 4 of Associate_SIN_SSN. How do I only pull certain values from a column?

$CSV = import-csv "C:\csv" | select Associate_First_Name,Associate_Last_Name,Associate_Email,Associate_SIN_SSN,@{n=’password’;e={$_.Associate_First_Name+ $_.Associate_Last_Name+ $_.Associate_SIN_SSN}}

$CSV | export-CSV "C:\csv"-NoTypeInformation

Upvotes: 1

Views: 165

Answers (1)

AdminOfThings
AdminOfThings

Reputation: 25001

You can use the following:

$CSV = Import-Csv "C:\csv" |
    Select-Object Associate_First_Name,Associate_Last_Name,Associate_Email,Associate_SIN_SSN,@{n='Password';e={$_.Associate_First_Name.Substring(0,1).ToLower()+ $_.Associate_Last_Name.Substring(0,1).ToLower() + $_.Associate_SIN_SSN.Substring(($_.Associate_SIN_SSN.Length-4))}}

To extract parts of a string, you can use the .NET method Substring() from the String class.

You can slightly enhance readability by defining your calculated property before the Select-Object command. Also a String value is indexed and individual characters can be extracted using an index ([0] is the first character).

$Password = @{n='Password'
              e={ $_.Associate_First_Name[0] + $_.Associate_Last_Name[0] + $_.Associate_SIN_SSN.Substring(($_.Associate_SIN_SSN.Length-4)) }
}

$CSV = Import-Csv "C:\csv" |
        Select-Object Associate_First_Name,Associate_Last_Name,Associate_Email,Associate_SIN_SSN,$Password

Another readability alternative is to not use calculated properties with Select-Object and create custom objects instead. This also adds flexibility to manipulate each value before outputting them.

$CSV = Import-Csv 'C:\csv' | ForEach-Object {

    $First = $_.Associate_First_Name.ToLower()
    $Last = $_.Associate_Last_Name.ToLower()
    $Email = $_.Associate_Email.ToLower()
    $SSN = $_.Associate_SIN_SSN
    $Password = $First[0]+$Last[0]+$SSN.Substring(($SSN.Length-4))

    [pscustomobject]@{ 'Associate_First_Name' = $First
                       'Associate_Last_Name' = $Last
                       'Associate_Email' = $Email
                       'Associate_SIN_SSN' = $SSN
                       'Password' = $Password
    }
}

Upvotes: 1

Related Questions