• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8465
  • Last Modified:

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.
0
patrickm12981
Asked:
patrickm12981
  • 4
  • 4
1 Solution
 
QlemoC++ DeveloperCommented:
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
 
QlemoC++ DeveloperCommented:
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now