[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Modify data in a specific csv file column using powershell

Posted on 2012-04-09
8
Medium Priority
?
8,132 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 71

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 71

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 71

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 71

Accepted Solution

by:
Qlemo earned 2000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In this post we will be converting StringData saved within a text file into a hash table. This can be further used in a PowerShell script for replacing settings that are dynamic in nature from environment to environment.
Compliance and data security require steps be taken to prevent unauthorized users from copying data.  Here's one method to prevent data theft via USB drives (and writable optical media).
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Suggested Courses

656 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