Solved

Modify data in a specific csv file column using powershell

Posted on 2012-04-09
8
7,077 Views
Last Modified: 2012-09-05
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
Comment
Question by:patrickm12981
  • 4
  • 4
8 Comments
 
LVL 69

Expert Comment

by:Qlemo
ID: 37825789
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
 

Author Comment

by:patrickm12981
ID: 37827736
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
 
LVL 69

Expert Comment

by:Qlemo
ID: 37827865
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
Independent Software Vendors: 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!

 

Author Comment

by:patrickm12981
ID: 37828009
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
 
LVL 69

Expert Comment

by:Qlemo
ID: 37828939
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
 

Author Comment

by:patrickm12981
ID: 37829542
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
 
LVL 69

Accepted Solution

by:
Qlemo earned 500 total points
ID: 37830410
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
 

Author Comment

by:patrickm12981
ID: 37830420
Thanks for the help!
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article outlines the process to identify and resolve account lockout in an Active Directory environment.
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question