Modify powershell script to look for specific digits and write in a specific field

Member_2_5306354
Member_2_5306354 used Ask the Experts™
on
I would like to change this script so that when lookin at position position 99,  it looks only at the last 5 digits and then writes those 5 digits in position 100...



Param(
	$filename = 'c:\Temporary\Import1.csv',
	$newFile  = 'c:\Temporary\Import2.csv'
)
$content = Get-Content $filename

$outRecords = @()

foreach( $record in $content )
{
	$array = $record.Split( ',' )
	## entries in $array are zero-based
	if ($array.Length -lt 101) {
		$outRecords += $record
	} else {
	     if ($array[3] -eq '65-ABCDEF' ) {
		if ($array[99] -eq "") {
		      if ($array[98].Length -gt 5) {
			$array[99] = $array[98].substring($array[98].length-6,5)
		      }
		}
	     }
	     $outRecords += $array -join ","
	}
}
$outRecords | Out-File $newFile -Encoding ASCII

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
oBdA
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
Like this?
Param(
	$filename = 'c:\Temporary\Import1.csv',
	$newFile  = 'c:\Temporary\Import2.csv'
)
$content = Get-Content $filename

$outRecords = @()

foreach ($record in $content) {
	$array = $record.Split(',')
	## entries in $array are zero-based
	if ($array.Length -lt 101) {
		$outRecords += $record
	} else {
		if ($array[3] -eq '65-ABCDEF' ) {
			if (($array[99] -eq "") -and ($array[98].Length -gt 5)) {
				$array[99] = $array[98].Substring($array[98].length-6, 5)
			}
			if ($array[99] -match '\d{5}$')) {
				$array[100] = $Matches[0]
			}
		}
		$outRecords += $array -join ","
	}
}
$outRecords | Out-File $newFile -Encoding ASCII

Open in new window

Test your restores, not your backups...
Top Expert 2016
Commented:
Try this, it sounds like you just want the right 5 positions of column 100 rather than the first 5 positions of the right 6 characters,

Param(
	$filename = 'c:\Temporary\Import1.csv',
	$newFile  = 'c:\Temporary\Import2.csv'
)
$content = Get-Content $filename

$outRecords = @()

foreach( $record in $content )
{
	$array = $record.Split( ',' )
	## entries in $array are zero-based
	if ($array.Length -lt 101) {
		$outRecords += $record
	} else {
	     if ($array[3] -eq '65-ABCDEF' ) {
		if ($array[99] -eq "") {
		      if ($array[98].Length -gt 5) {
			$array[99] = $array[98].substring($array[98].length-5,5)
		      }
		}
	     }
	     $outRecords += $array -join ","
	}
}
$outRecords | Out-File $newFile -Encoding ASCII

Open in new window

»bp
Member_2_5306354

Author

Commented:
Perfect thanks very much, worked excellent.
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
Welcome.

»bp

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial