Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Powershell issue modifying a column's data.

Posted on 2013-12-20
7
Medium Priority
?
213 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
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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

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…
This article describes how to add a user-defined command button to the Windows 7 Explorer toolbar.  In the previous article (http://www.experts-exchange.com/A_2172.html), we saw how to put the Delete button back there where it belongs.  "Delete" is …
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…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

886 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