Solved

Modify data in a specific csv file column using powershell

Posted on 2012-04-09
8
6,323 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 68

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 68

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
 

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 68

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 68

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Disabling the Directory Sync Service Account in Office 365 will stop directory synchronization from working.
This article will help you understand what HashTables are and how to use them in PowerShell.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

707 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now