Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Powershell issue modifying a column's data.

Posted on 2013-12-20
7
Medium Priority
?
212 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 71

Accepted Solution

by:
Qlemo earned 1000 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 1000 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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 71

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

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…
With most software applications trying to cater to multiple user needs nowadays, the focus is to make them as configurable as possible. For e.g., when creating Silverlight applications which will connect to WCF services, the service end point usuall…
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

660 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