Parsing and modifying csv with powershell

Question

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?

Santiago Squarzon · Accepted Answer · 2022-11-05 15:45:11Z

1

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

edited Nov 5, 2022 at 15:45

answered Nov 5, 2022 at 14:48

Santiago Squarzon

65.5k5 gold badges26 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Art Over a year ago

Thank you very much! Can I ask you, to explain what’s going on in a first line of script?

Santiago Squarzon Over a year ago

@lunnyj the cultureinfo one? It's just the same as Get-Culture

Art Over a year ago

can you help me again?:) how I can save new file in the same directory where old file was located? Run my script, ./my_script.ps1 file.csv which located at the same directory where file.csv stored. But when I run script from the another directory, new file save in directory when the script located, but I need to save it on the same directory, where file.csv stored.

Collectives™ on Stack Overflow

Parsing and modifying csv with powershell

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related