Solved

Powershell issue modifying a column's data.

Posted on 2013-12-20
7
207 Views
Last Modified: 2013-12-20
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
Comment
Question by:avwoolsey
[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
  • 3
  • 2
  • 2
7 Comments
 
LVL 40

Expert Comment

by:Subsun
ID: 39731696
Do you have any other columns in input csv ?
0
 
LVL 70

Accepted Solution

by:
Qlemo earned 250 total points
ID: 39731711
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
 
LVL 40

Assisted Solution

by:Subsun
Subsun earned 250 total points
ID: 39731747
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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Closing Comment

by:avwoolsey
ID: 39731767
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
 
LVL 70

Expert Comment

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

Author Comment

by:avwoolsey
ID: 39731862
Thank you for taking the time to explain that. It makes sense to me now.
0
 
LVL 40

Expert Comment

by:Subsun
ID: 39732653
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

Suggested Solutions

In this article, I will show how to use the Ribbon IDs Tool Window to assign the built-in Office icons to a ribbon button.  This tool will help us to find the OfficeImageId that corresponds to our desired built-in Office icon. The tool is part of…
This article describes a technique for converting RTF (Rich Text Format) data to HTML and provides C++ source that does it all in just a few lines of code. Although RTF is coming to be considered a "legacy" format, it is still in common use... po…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

739 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