Solved

Powershell issue modifying a column's data.

Posted on 2013-12-20
7
201 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
  • 3
  • 2
  • 2
7 Comments
 
LVL 40

Expert Comment

by:Subsun
Comment Utility
Do you have any other columns in input csv ?
0
 
LVL 68

Accepted Solution

by:
Qlemo earned 250 total points
Comment Utility
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
Comment Utility
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
What Is Threat Intelligence?

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

 

Author Closing Comment

by:avwoolsey
Comment Utility
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 68

Expert Comment

by:Qlemo
Comment Utility
"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
Comment Utility
Thank you for taking the time to explain that. It makes sense to me now.
0
 
LVL 40

Expert Comment

by:Subsun
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This tutorial is about how to put some of your C++ program's functionality into a standard DLL, and how to make working with the EXE and the DLL simple and seamless.   We'll be using Microsoft Visual Studio 2008 and we will cut out the noise; that i…
For a while now I'v been searching for a circular progress control, much like the one you get when first starting your Silverlight application. I found a couple that were written in WPF and there were a few written in Silverlight, but all appeared o…
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…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

744 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

16 Experts available now in Live!

Get 1:1 Help Now