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

Powershell issue modifying a column's data.

I have a CSV file from which I need to read a column that contains phone numbers. The column's heading is "Number Called DNIS". What I need to do is determine if any entries have 11 characters, and if so, does the starting number begin with a "1". If both of those criteria are true, I need to remove the leading "1", otherwise leave it alone. I seem to have kinda, sorta found how to make this work, though all I get is the below output to my PS window. My code doesn't make the change and create a new CSV.

When I run the script as-is, I get the following as sample output. It does seem to remove the leading "1", but I also can't seem to get a working "and" operator to function correctly with my if statement:

String modified.
2077610950
String modified.
7184777738
String modified.
8037670989
String modified.
2016152192
String modified.
2027306232
String modified.
9897218391
String modified.
2022324384
String modified.
8034459806
String modified.
9739064512
String modified.
No modification needed.
No modification needed.
2033606032
String modified.
9734666782
String modified.

Here is the code I have so far. What am I doing wrong? As you can see, I've tried different variations with my "if" statement, to no avail...

$Test = Import-Csv c:\scripts\file\DCA_001718_20131213.csv

$Test | ForEach-Object {
	
	foreach ($property in $_.PSObject.Properties) {
			}
	if ($_."Number Called DNIS".Length -eq "11")# -and ($_."Number Called DNIS".StartsWith -eq "1")
	#if (($_."Number Called DNIS".Length -eq "11") -and ($_."Number Called DNIS".StartsWith -eq "1"))
	{
	
		$_."Number Called DNIS".TrimStart("1")
		Write-Host "String modified."
	}
	else
	{
		Write-Host "No modification needed."
	}
}
$Test | Export-Csv -Path c:\scripts\file\TestOutput.csv -NoTypeInformation

Open in new window

0
avwoolsey
Asked:
avwoolsey
  • 3
  • 2
  • 2
2 Solutions
 
SubsunCommented:
Do you have any other columns in input csv ?
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Lines 5 and 6 don't do anything, and are not required anyway.

StartsWith is a method, you can't compare it with something, at that would compare the method (i.e. the scriptblock).

TrimStart would remove all leading ones - certainly not what you are after. And it doesn't change the basic object, it results in a copy of the modified string.
Import-Csv c:\scripts\file\DCA_001718_20131213.csv | ForEach-Object {
	if ($_."Number Called DNIS".Length -eq 11) -and ($_."Number Called DNIS"[0] -eq '1')
	{
		$_."Number Called DNIS" = $_."Number Called DNIS".Remove(0,1)
		Write-Host "String modified."
	}
	else
	{
		Write-Host "No modification needed."
	}
       $_
} | Export-Csv -Path c:\scripts\file\TestOutput.csv -NoTypeInformation

Open in new window

0
 
SubsunCommented:
There is a typo in second line..
if (($_."Number Called DNIS".Length -eq 11) -and ($_."Number Called DNIS"[0] -eq '1'))

Open in new window

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
avwoolseyAuthor Commented:
I had to accept both solutions, because, while the "[0]" and ".Remove(0,1)" portions fixed it, I also needed my if statement to by completely closed in ().

I really appreciate the help. I'm sorry to say I'd have never come up with this, and even after seeing the solution, I don't understand it. ".Remove(0,1)" makes no sense to me, neither does the need to add the [0] in my if statement. Oh well. Thanks again, both of you, very much.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
"Just a string"[0] will return the first character of the string, "J". In your case we want to check against "1".

"Just a string".Remove(0,1) creates a new string with the first character removed, because we remove from the first position (all indicies in PS start with 0, not 1), and one character only (1).

You manage PowerShell best if you construct very simple test cases, like my string example above, and just try out. And keep in mind that PS will almost always generate copies of objects or data you change.
0
 
avwoolseyAuthor Commented:
Thank you for taking the time to explain that. It makes sense to me now.
0
 
SubsunCommented:
In your script you may use the StartsWith method with if conditions..
If  (($_."Number Called DNIS".Length -eq "11") -and ($_."Number Called DNIS".StartsWith("1")))

Open in new window


But you may not get expected results if you use TrimStart method to trim the first character..
$_."Number Called DNIS" = $_."Number Called DNIS".TrimStart("1") will remove all leading one's. 11123456789 will be come 23456789
TrimstartBut you can use remove method as explained by Qlemo or use replace or Substring methods..

$_."Number Called DNIS" = $_."Number Called DNIS".Substring (1)

or 

$_."Number Called DNIS" = $_."Number Called DNIS" -replace "^1"

Open in new window


Here is some interesting articles to read for details on String manipulation with Powershell..

http://technet.microsoft.com/en-us/library/ee692804.aspx
http://blogs.technet.com/b/heyscriptingguy/archive/2011/09/21/two-simple-powershell-methods-to-remove-the-last-letter-of-a-string.aspx
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.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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