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
– iRon, Commented Aug 12, 2018 at 7:40
You could use the [Join-Object] cmdlet from the PowerShell Gallery: Import-CSV .\userinfo.csv | LeftJoin (Import-CSV .\email.csv) userid {$Right.$_} | Export-CSV .\New.csv — iRon
– iRon, Commented Aug 12, 2018 at 7:40

Bacon Bits · Accepted Answer · 2018-08-12 17:07:23Z

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, 2018 at 17:07

answered Aug 12, 2018 at 3:27

Bacon Bits

32.5k6 gold badges63 silver badges78 bronze badges

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

8 Comments

Steve Glover Over a year ago

Thanks for the basic steps. I must be missing something. I am getting an error:

Steve Glover Over a year ago

Unable to index into an object of type System.String

Bacon Bits Over a year ago

@SteveGlover What are you running that creates an error? Which command is throwing the error? What's the complete error message?

Steve Glover Over a year ago

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

Bacon Bits Over a year ago

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

|

postanote · Accepted Answer · 2018-08-12 01:30:41Z

0

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

answered Aug 12, 2018 at 1:30

postanote

16.4k3 gold badges17 silver badges29 bronze badges

4 Comments

Steve Glover Over a year ago

$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 Over a year ago

$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 Over a year ago

Export-Csv "c:\testscripts\NewFile.csv"

Steve Glover Over a year ago

Trying to match Student_id in both files (they are unique) and replace Student_email value in csv2 with Student_email value from csv1.

Collectives™ on Stack Overflow

Replacing values in csv using powershell

2 Answers 2

8 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related