Clash Royale CLAN TAG#URR8PPP

Replacing values in csv using powershell

I need to overwrite the email value in userinfo.csv with the email address from email.csv
userid and u_user values are matching and unique in both csv's. The email address value in userinfo.csv in not good and needs to be overwritten with the email value from email.csv.

How do I match userid in both csv's and append email value?

No idea where to even start. Any help, please.

email.csv
userid,email
1234,user4@email.com
1235,user5@email.com

userinfo.csv
u_work,u_user,u_address,u_city,u_state,u_zip,u_email,u_phonehome
1234,here,there,everywhere,55555,1234@bad.org,555-555-5555
away,1235,there,here,everywhere,66666,1235@bad.com,666-666-6666

new.csv
u_work,u_user,u_address,u_city,u_state,u_zip,u_email,u_phonehome
1234,here,there,everywhere,55555,user4@email.com,555-555-5555
away,1235,there,here,everywhere,66666,user5@email.com,666-666-6666


userinfo.csv

[Join-Object] cmdlet

Import-CSV .userinfo.csv | LeftJoin (Import-CSV .email.csv) userid $Right.$_ | Export-CSV .New.csv

2 Answers
2

Your CSVs as presented are not valid. The header row has 8 fields. Row 1 has 7 fields. That's not valid. I'm assuming that it should look like this:

userinfo.csv
u_work,u_user,u_address,u_city,u_state,u_zip,u_email,u_phone
home,1234,here,there,everywhere,55555,1234@bad.org,555-555-5555
away,1235,there,here,everywhere,66666,1235@bad.com,666-666-6666


In other words, that u_phonehome is actually u_phone and home is on the wrong row in your examples.

u_phonehome

u_phone

home

Your basic steps are:

A. Import email.csv into a hash table for quick lookup.

email.csv

$emails = @
Import-Csv email.csv | ForEach-Object
$email[$_.userid] = $_.email



B. Import userinfo.csv, and replace the email addresses where necessary.

userinfo.csv

$NewCSV = Import-Csv userinfo.csv | ForEach-Object
if ($emails.ContainsKey($_.u_user))
$_.u_email = $emails[$_.u_user]

$_



C. Write the output file.

$NewCSV | Export-Csv new.csv -NoTypeInformation


You could also do step B with a Select-Object and a calculated property, but this is a bit easier to write.

Select-Object

$email

$_

foreach

foreach ($Student in $CSV) $_.Student_id

Student_id

Student_email

You'd use Regex for the match and replace for the modification of specific stings. This is a common thing that is done, and there are many articles and posts on the topic. So, give the below resources a shot and come back with your effort if you need further assistance.

For example:

Windows PowerShell: Writing Regular Expressions

https://technet.microsoft.com/en-us/library/2007.11.powershell.aspx

Powershell: The many ways to use regex - Kevin Marquette

https://kevinmarquette.github.io/2017-07-31-Powershell-regex-regular-expression

"Hello. Yes, this is a cat." -replace 'cat','dog'
"Hello. Yes, this is a dog." -replace [regex]::Escape('.'),'!'
("Hello. Yes, this is a dog.").Replace('.','!')


PSTip A difference between the –replace operator and String.Replace method

https://www.powershellmagazine.com/2012/11/12/pstip-a-difference-between-the-replace-operator-and-string-replace-method/

(Get-Content C:testtest.txt) |
Foreach-Object $_ -replace "(?i)b[A-Z0-9._%+-]+@[A-Z0-9.-]+.[A-Z]2,4b",'<$0>' |
Set-Content C:testtest.txt


Powershell Regex find emails and replace email with (<email>)

$txt='<p class=FillText><a name="InternetMail_P3"></a>First.Last@company-name.com</p>'
$re="[a-z0-9!#$%&'*+/=?^_`~-]+(?:.[a-z0-9!#$%&'*+/=?^_`~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?"
[regex]::MAtch($txt, $re, "IgnoreCase ")


Using Regex in Powershell to grab email

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.