Replace characters in a file

Hi,

  I have a huge flat file which has many fields.
Three of those fields are: First_Name, Last_Name and SSN (9 or <9  characters)
My requirement is to use powershell, to substitute the vowels in the First_Name and Last_Name, with 'X'
and change the SSN to XXX-XX-Last 4 SSN.

Can someone kindly help with the script for it?
pvsbandiAsked:
Who is Participating?
 
oBdACommented:
That should do the trick.
Note that the last two SSNs don't comply with your definition; Brian Doug's only has 8 numbers, Kevin Brady's has 10. Is that an issue in the real file that you want to be informed about, or is it just because of the sample having been generated on the fly?
$ReplaceChar = 'X'
Import-Csv C:\Temp\Sample.csv | ForEach-Object {
	ForEach ($Property In 'First_Name', 'Middle_Name') {
		$_.$Property = $_.$Property -replace '[aeiou]', $ReplaceChar
	}
	$_.SSN = $_.SSN -replace '(...)(..)(....)', ("$($ReplaceChar*3)-$($ReplaceChar*2)-`$3")
	$_
} | ConvertTo-Csv -NoTypeInformation |
	ForEach-Object {($_ -replace '","', ',').Trim('"')} |
	Set-Content -Path C:\Temp\Converted.csv

Open in new window


Edit: Fixed the issue where the characters in the SSN weren't replaced.
0
 
aikimarkCommented:
please post a representative sample of your CSV file.
0
 
ste5anSenior DeveloperCommented:
Just a comment: Depending on the use-case, this is may not a sufficient measure to anonymize data.
0
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

 
pvsbandiAuthor Commented:
Attached it.
Sample.csv
0
 
aikimarkCommented:
I recommend this simplification for the SSN substitution.  It produces valid output no matter the length of the SSN string.  You can use any string characters as the pad
	$a = ($_.SSN -replace '-', '').PadLeft(9, '.')
	$_.SSN = 'XXX-XX-' + $a.substring($a.length - 4)

Open in new window

0
 
pvsbandiAuthor Commented:
Thanks so much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.