Replacing values in csv using powershell

Question

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,[email protected]
1235,[email protected]

userinfo.csv
u_work,u_user,u_address,u_city,u_state,u_zip,u_email,u_phonehome
1234,here,there,everywhere,55555,[email protected],555-555-5555
away,1235,there,here,everywhere,66666,[email protected],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,[email protected],555-555-5555
away,1235,there,here,everywhere,66666,[email protected],666-666-6666

Please comment/confirm/update the `userinfo.csv` in your question according to @Bacon Bits comment: "*Your CSVs as presented are not valid*". — iRon, Aug 12 '18 at 07:40
You could use the [`[Join-Object] cmdlet`](https://www.powershellgallery.com/packages/Join) from the [PowerShell Gallery](https://www.powershellgallery.com/): `Import-CSV .\userinfo.csv | LeftJoin (Import-CSV .\email.csv) userid {$Right.$_} | Export-CSV .\New.csv` — iRon, Aug 12 '18 at 07:40

Bacon Bits · Accepted Answer · 2018-08-12T17:07:23.843

3

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,[email protected],555-555-5555
away,1235,there,here,everywhere,66666,[email protected],666-666-6666

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

Your basic steps are:

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

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

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

$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.

edited Aug 12 '18 at 17:07

answered Aug 12 '18 at 03:27

Bacon Bits

30,782
5
59
66

Thanks for the basic steps. I must be missing something. I am getting an error: – Steve Glover Aug 13 '18 at 13:44
Unable to index into an object of type System.String – Steve Glover Aug 13 '18 at 13:45
@SteveGlover What are you running that creates an error? Which command is throwing the error? What's the complete error message? – Bacon Bits Aug 13 '18 at 15:48
Cannot index into a null array. At C:\TestScripts\New Text Document2.ps1:3 char:5 + $email[$_.Student_id] = $_.Student_email + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : NullArray – Steve Glover Aug 17 '18 at 18:52
@SteveGlover You've got a basic logic error then. Either `$email` was not initialized as a hash table, or you've mixed the `$_` pipeline with the non-pipelined `foreach` statement (e.g., `foreach ($Student in $CSV) { $_.Student_id }` is invalid), or either `Student_id` or `Student_email` isn't the right spelling of the header in the CSV file. – Bacon Bits Aug 21 '18 at 13:27
Just the tip I needed. Figured it out, using your code from above. – Steve Glover Aug 22 '18 at 18:06
array was foreach-object {$email[ needed to be changed to foreach-object {$emails[ Marking as completed. Thank you for not giving up on me and teaching me something new. Very helpful. – Steve Glover Aug 22 '18 at 18:13
Thank you. I have copied the working script. $emails = @{} Import-Csv C:\app\files\edit\ademail.csv | ForEach-Object { $emails[$_."Description"] = $_."mail" } $NewCSV = Import-Csv C:\app\files\edit\bademails.csv | ForEach-Object { if ($emails.ContainsKey($_."Student_id")) { $_."Student_email" = $emails[$_."Student_id"] } $_ } $NewCSV | Export-Csv c:\app\files\good\allgood.csv -NoTypeInformation – Steve Glover Aug 23 '18 at 14:50

score 0 · Answer 2 · answered Aug 12 '18 at 01:30

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:\test\test.txt) | 
Foreach-Object {$_ -replace "(?i)\b[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}\b",'<$0>'} | 
Set-Content C:\test\test.txt

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

$txt='<p class=FillText><a name="InternetMail_P3"></a>[email protected]</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

$CSV1 = Import-Csv "c:\testscripts\adstudents.csv" $CSV2 = Import-Csv "c:\testscripts\studentsnospaces.csv" $CSV1 | ForEach-Object {$StudentID = $_.Student_id, $Email = $_.Student_email} — Steve Glover, Aug 12 '18 at 23:51
$CSV2 | Where-Object { $_.StudentID -eq $StudentID } | Select-Object School_id, Student_id, Student_number, State_id, Last_name, Middle_name, First_name, Grade, Gender, DOB, Race, Hispanic_Latino, Ell_status, Frl_status, IEP_status, Student_street, Student_city, Student_state, Student_zip, @{n='Student_email';e={ $Email }}, Contact_relationship, Contact_type, Contact_name, Contact_phone, Contact_email, Username, Password — Steve Glover, Aug 12 '18 at 23:51
Trying to match Student_id in both files (they are unique) and replace Student_email value in csv2 with Student_email value from csv1. — Steve Glover, Aug 12 '18 at 23:53

Replacing values in csv using powershell

2 Answers2