Remove special characters and add data in that column

PowerShell Help
1

I am very new to Powershell scripting. I have only used it for pulling data from sql and creating csv files with the data. I am finding issues with some data entered in the database and need to clean it up and modify it a little before creating the final csv. Any help would be greatly appreciated.

First issue I have is spaces leading and trailing some of the data in columns. I have resolved this using trim cleaning every column. The next thing I need to do is select a column containing phone numbers (could be multiple columns) and remove all the extra characters ()-. etc. Then I need to add to the beginning and end of the phone number which could include # ,* or additional numbers. (1234567890 to 34512345678905*** as example) This is what I have so far the Where-Object does not currently work but left it in as example.

$CSV = import-csv 'C:\new.csv'
$CSV | Foreach-Object { 
$_.PSObject.Properties | Foreach-Object { $_.Value = $_.Value.Trim() }
} 
$CSV | Where-Object { 
$_.Name -like 'Phone*' -replace '(,),-,.''' 
} 


$CSV | ConvertTo-Csv -UseCulture -NoTypeInformation | Out-File 'C:\modified.csv'
2

Where-Object is used only to filter data based on conditions, we cannot change data. You can use Where-Object to filter then pipe to ForEach-Object to do replace action for each object

3

Thanks for your reply. Yes that is what I gathered. Once I get the filter I dont know how to add the replace function. I imagine it would go something like this but I dont know the correct syntax to get the replace to work.

| ForEach-Object { ??? -replace '(,),-,.''' }
4

It’s much easier to assist if you post mock data. One of the typical approaches that can be used is a calculated expression. This lets you manipulate column data, rename columns, etc.:

$csv = @"
Name,Phone1,Phone2
John    ,(945) 555-4567,543-555-5567
Sue  ,(674) 555-4328,(654)-555-0642
   Ralph,(345) 555-6730,540555-2397
"@ | ConvertFrom-Csv

$newCsv = $csv | 
          Select-Object @{Name='Name';Expression={$_.Name.Trim()}},
                        @{Name='Phone1';Expression={'1,,,,{0}' -f ($_.Phone1 -replace '[^\d]')}},
                        @{Name='Phone2';Expression={$_.Phone2 -replace '[^\d]'}}

$newCsv

Here is the basic output:

Name  Phone1         Phone2
----  ------         ------
John  (945) 555-4567 543-555-5567
Sue   (674) 555-4328 (654)-555-0642
Ralph (345) 555-6730 540555-2397

PS C:\Users\rasim> $newCsv


Name  Phone1          Phone2
----  ------          ------
John  1,,,,9455554567 5435555567
Sue   1,,,,6745554328 6545550642
Ralph 1,,,,3455556730 5405552397
5

Thank you Rob that got me in the direction I needed to complete my task. This is a piece of what I used that is directly in line with my original question. This cleared all extra spaces in all columns and where there was a phone number such as (123)456-7890. It removed all special characters and then added a 1 in front and pound at the end to look like this 11234567890#. It then checks for not empty or incomplete rows and exports only complete ones into the new csv file.

 

$CSV = Import-CSV 'C:\new.CSV' -Delimiter ','
$CSV | ForEach-Object {$_.PSObject.Properties | ForEach-Object { $_.Value = $_.Value.Trim() }}
$CSV | ForEach-Object { if($_.phone1 -ne "") {$_.phone1 = '1' +($_.phone1 -replace '[^\d]') +'#'}}
$CSV | Where-Object {($_.First_Name -ne "") -and ($_.Last_Name -ne "") -and ($_.phone1 -ne "")} 
| Export-CSV -LiteralPath 'C:\NEW.CSV'