Parsing and modifying csv with powershell-CodePudding

I'm new to Powershell but I've given it my best go.Have a .csv file, small example:

id,location_id,name,title,email,directorate 
1,1, Amy lee,Singer,, 
2,2,brad Pitt,Actor,,Production 
3,5,Steven Spielberg,Producer,[email protected],Production

Need to:

change first and last name to uppercase, example, Brad Pitt, Amy Lee.
create email with pattern first letter of first name last name, all in lowercase with @google.com and value from location_id, example - [email protected], [email protected]
save it to new file.csv, with the same structure, example:

id,location_id,name,title,email,directorate 
1,1, Amy Lee,Singer,[email protected], 
2,2,Brad Pitt,Actor,[email protected],Production 
3,5,Steven Spielberg,Producer,[email protected],Production

I wrote a script, whose commands one by one perform the task, but dunno how to save it no new .csv file:

param (
    [string] $file_path
)

$inputFile = Import-Csv -Path $file_path

foreach ($line in $inputFile) {
    $line.name = (Get-Culture).TextInfo.ToTitleCase($line.name)
    $firstName = $line.name.split(" ")[0]
    $lastName = $line.name.split(" ")[1]
    $newEmail = ($firstName[0]   $lastName   $line.location_id   "@google.com").toLower()
}

Perhaps, there is more clean solution?

CodePudding user response：

Your code is close to completion but here is a simpler way. You're already using TextInfo.ToTitleCase but for the email address you could simplify it using the -replace operator and a bit of regex.

You can find info about the regex pattern in this link: https://regex101.com/r/WKF9R5/1

$txtInfo = [cultureinfo]::InvariantCulture.TextInfo
$csv = Import-Csv path\to\csv.csv

foreach($line in $csv) {
    $name = $line.name

    $line.name  = $txtInfo.ToTitleCase($name)
    # Example: 'brad Pitt' => bPitt
    $line.email = ($name -replace '(?<=^\w{1})\w \s').ToLower()   $line.location_id   '@google.com'
}

$csv | Export-Csv path\to\newcsv.csv -NoTypeInformation

Final output using the Csv in the question:

id location_id name             title    email                  directorate
-- ----------- ----             -----    -----                  -----------
1  1           Amy Lee          Singer   [email protected]
2  2           Brad Pitt        Actor    [email protected]      Production
3  5           Steven Spielberg Producer [email protected] Production