Modify data in a specific csv file column using powershell

I am trying to eliminate the spaces in the columns "telephoneNumber", "otherTelephone" and "mobile" from the csv:

SAMAccountName,EmployeeID,Surname,GivenName,telephoneNumber,otherTelephone,mobile
jdoe,Do e,John,5555555   55,+1555555555;ext=5555,5555555


I am able to eliminate all spaces from all columns using:

(Get-Content .\test.csv) |
Foreach-Object {$_ -replace " ", ""} |
Set-Content .\test.csv

But I'm not  sure how to specify the modification of only certain columns, thanks for the help.
patrickm12981Asked:
Who is Participating?
 
QlemoConnect With a Mentor Batchelor, Developer and EE Topic AdvisorCommented:
If you want headers, you will have to put them into the stream or file yourself, like in
"SAMAccountName,EmployeeID,Surname,GivenName,telephoneNumber,otherTelephone,mobile" | Set-Content .\test.csv
$txt | % { write-output "$($_.SAMAccountName),$($_.EmployeeID),$($_.Surname),$($_.GivenName),$($_.telephoneNumber),$($_.otherTelephone),$($_.mobile)" } |
  out-file .\test.csv -Encoding ASCII -Append

Open in new window


If you want to replace several patterns, -replace is still the operator to use, but in
combination with regular expressions:
  $_.telephoneNumber = $_.telephoneNumber -replace "[ -]", ""
  $_.otherTelephone = $_.otherTelephone  -replace "[ -]", ""
  $_.mobile = $_.mobile  -replace "[ -]", ""

Open in new window

but that only helps if you want to remove characters, or replace them all by the same.

Following now a "fully dynamic" approach, which allows you to provide both the fields to replace and the output fields only once. That's much easier if you need to change something.
$txt = import-csv .\test.csv | % {
  foreach ($attr in "telephoneNumber", "otherTelephone", "mobile") {
    $_.$attr = $_.$attr -replace "[ -]", ""
  }
  $_
}

$allFields = "SAMAccountName","EmployeeID","Surname","GivenName","telephoneNumber","otherTelephone","mobile"
$allFields -join "," | Set-Content .\test.csv
$txt | % {
  $line = ""
  foreach ($attr in $allFields) {
    if ($attr -ne $allFields[0]) { $line += "," }
    $line += $_.$attr
  }
  write-output $line
} | out-file .\test.csv -Encoding ASCII -Append

Open in new window

0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
First, you need to read the CSV as such (which generates objects with attributes named after the column names found in the first line). Get-Content will read each line as a stream of text, non-formatted.
As soon as you have objects with attributes, you can apply changes to the "columns".
$txt = import-csv .\test.csv | % {
  $_.telephoneNumber = $_.telephoneNumber -replace " ", ""
  $_.otherTelephone  = $_.otherTelephone  -replace " ", ""
  $_.mobile          = $_.mobile.Replace  -replace " ", ""
  $_
} 
export-csv -InputObject $txt -NoTypeInformation .\test.csv

Open in new window

We have to use an object here, since you want to replace the file. Using a pipe for export-csv would destroy the input file (set it empty).

If the added quotes are unwanted, we need to manually output each line instead of using export-csv.
$txt | % { write-output "$($_.SAMAccountName),$($_.EmployeeID),$($_.Surname),$($_.GivenName),$($_.telephoneNumber),$($_.otherTelephone),$($_.mobile)" } |
Set-Content .\test.csv

Open in new window

With the latter example we could also do the replacement stuff in the write-output line. But that does not simplify things (much), so I'll skip that.
0
 
patrickm12981Author Commented:
Thank you for the response.

Results from the first example:

"SAMAccountName","EmployeeID","Surname","GivenName","telephoneNumber","otherTelephone","mobile"
"jdoe","","Do e","John","555555555","+1555555555;ext=5555","stringReplace(charoldChar,charnewChar),stringReplace(stringoldValue,stringnewValue)"

Open in new window


Results from the second:

jdoe,,Do e,John,555555555,+1555555555;ext=5555,stringReplace(charoldChar,charnewChar),stringReplace(stringoldValue,stringnewValue)

Open in new window

0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Typo in the $txt generation code, hence neither export-csv nor set-content did work:
$txt = import-csv .\test.csv | % {
  $_.telephoneNumber = $_.telephoneNumber -replace " ", ""
  $_.otherTelephone  = $_.otherTelephone  -replace " ", ""
  $_.mobile          = $_.mobile          -replace " ", ""
  $_
}
# either:
export-csv -InputObject $txt -NoTypeInformation .\test.csv
# or:
$txt | % { write-output "$($_.SAMAccountName),$($_.EmployeeID),$($_.Surname),$($_.GivenName),$($_.telephoneNumber),$($_.otherTelephone),$($_.mobile)" } |
  Set-Content .\test.csv

Open in new window

0
 
patrickm12981Author Commented:
Still getting the same results.

CSV:

SAMAccountName,EmployeeID,Surname,GivenName,telephoneNumber,otherTelephone,mobile
jdoe,,Do e,John,5555555   55,+155555 5555;ext=5555,55 55555

Open in new window


Script I am running, I am using write-host for testing but I am getting consistent results writing to a file. I do need to maintain the column headers which the second option doesn't do.

$txt = import-csv .\test.csv | % {
  $_.telephoneNumber = $_.telephoneNumber -replace " ", ""
  $_.otherTelephone = $_.otherTelephone  -replace " ", ""
  $_.mobile = $_.mobile.Replace  -replace " ", ""
  $_
}
Write-Host $txt

Open in new window


Results:

@{SAMAccountName=jdoe; EmployeeID=; Surname=Do e; GivenName=John; telephoneNumber=555555555; otherTelephone=+1555555555;
ext=5555; mobile=stringReplace(charoldChar,charnewChar),stringReplace(stringoldValue,stringnewValue)}

Open in new window

0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Please check my code again, in particular line 4 in your and my script. The .Replace after $_.mobile needs to get removed.
You can't just use write-host for $txt, as that results in a hash array output (@{...}). My lines 10f should handle that (try without the last pipe and Set-Content, and you will see).
0
 
patrickm12981Author Commented:
Doh  - thank you.

The Write-host was working just to check the output, I know it was ugly but worked, the write-output is a lot better thank you.

I am trying to use your second method so I don't get the quotes but how do i maintain the column headers?

Current Script:
$txt = import-csv .\test.csv | % {
  $_.telephoneNumber = $_.telephoneNumber -replace " ", ""
  $_.otherTelephone = $_.otherTelephone  -replace " ", ""
  $_.mobile = $_.mobile  -replace " ", ""
  $_
}
$txt | % { write-output "$($_.SAMAccountName),$($_.EmployeeID),$($_.Surname),$($_.GivenName),$($_.telephoneNumber),$($_.otherTelephone),$($_.mobile)" } |
  Set-Content .\test.csv

Open in new window


Results:

jdoe,,Do e,John,555555555,+1555555555;ext=5555,5555555

If I want to add more to this, such as removing "-", this works but is there a better method?

  $_.telephoneNumber = $_.telephoneNumber -replace " ", ""
  $_.otherTelephone = $_.otherTelephone  -replace " ", ""
  $_.mobile = $_.mobile  -replace " ", ""
    $_.telephoneNumber = $_.telephoneNumber -replace "-", ""
  $_.otherTelephone = $_.otherTelephone  -replace "-", ""
  $_.mobile = $_.mobile  -replace "-", ""
  $_

Open in new window

0
 
patrickm12981Author Commented:
Thanks for the help!
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.