Script needed to search and replace

Good day,

I am looking for a script that will do the following.

I would like script that will look into a CSV file and check and do the following:

1.      If in position 4 it sees the number 96587, then it will look in position 28, and if it sees CT, FL, GA, IA, IN, NH, NY, WI. or PA  then change 96587 to 01-EAST,
However if in position 4 it sees the number 96587, then it will look in position 28, and if it sees  CO, CA, TX, CA or WA, then change 96587 to 01-WEST.

2.      Additionally, if it sees the following number, AC00123, AC00124, AC00125. Or AC00126, then remove the AC and add a C to the end of the numbers, for example 00123C, 00124C, 00125C, 00126C…

The content of the file looks like this:

5,A,01234,96587,Y,1251/2,1772345,,20181003,20181107,20181114,,1234567890000,COMP ACCOUNTS PAYABLE,123 ROAD WAY,,TREEWAY,FL,01234-5678,,,,1234564870004,Somewhere Warehouse,200 Treeview Court NW,,Everywhere,GA,12345-1234,,,018-441-5522,41245,ABCORPBC,,USD,N,,,,,,,,,,N,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,00987894561230,AC00123,152324,STEEL,,,,123.00,US,0.00,US,0.00,US,10.5000,0.0000,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
5,A,01234,96587,Y,1251/2,1772345,,20181003,20181107,20181114,,1234567890000,COMP ACCOUNTS PAYABLE,123 ROAR WAY,,TREEWAY,FL,01234-5678,,,,1234564870004,Somewhere Warehouse,200 Treeview Court NW,,Everywhere,GA,12345-1234,,,018-441-5522,41245,ABCORPBC,,USD,N,,,,,,,,,,N,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,00987894561560,AC00124,152345,WOOD,,,,789.00,US,0.00,US,0.00,US,10.5100,0.0000,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
5,A,01234,96587,Y,1251/2,1772345,,20181003,20181107,20181114,,1234567890000,COMP ACCOUNTS PAYABLE,123 ROAD WAY,,TREEWAY,FL,01234-5678,,,,1234564870004,Somewhere Warehouse,200 Treeview Court NW,,Everywhere,GA,12345-1234,,,018-441-5522,41245,ABCORPBC,,USD,N,,,,,,,,,,N,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,00987894564560,00456,152322,CANVAS,,,,123.00,US,0.00,US,0.00,US,10.5200,0.0000,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,


In the above text, each line starts with 5,A.. but it could differ.

Thanks in advance for your assistance.
100questionsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

oBdACommented:
Includes a sanity check if all lines actually have the same number of fields, since the fields aren't enclosed in quotes:
$inFile = '.\replace.csv'
$outFile = '.\replace_out.csv'
$replace_96587 = @{
	'CT' = '01-EAST'
	'FL' = '01-EAST'
	'GA' = '01-EAST'
	'IA' = '01-EAST'
	'IN' = '01-EAST'
	'NH' = '01-EAST'
	'PA' = '01-EAST'
	'NY' = '01-EAST'
	'WI' = '01-EAST'
	'CA' = '01-WEST'
	'CO' = '01-WEST'
	'TX' = '01-WEST'
	'WA' = '01-WEST'
}

$referenceCount = 0
$row = 0
Get-Content -Path $inFile | ForEach-Object {
	$row++
	$fields = $_.Split(',')
	If ($referenceCount -eq 0) {$referenceCount = $fields.Count}
	If ($fields.Count -ne $referenceCount) {
		Throw "Field count in line $($row) is incorrect (is: $($fields.Count), expected: $($referenceCount))!"
	}
	$old3 = $fields[3]
	$old99 = $fields[99]
	If ($fields[3] -eq '96587') {
		If ($replace_96587.ContainsKey($fields[27])) {
			$fields[3] = $replace_96587[$fields[27]]
		}
	}
	If ($fields[99] -match '\AAC(?<Number>0012[3-6])\Z') {
		$fields[99] = "$($Matches['Number'])C"
	}
	"line $($row): $($old3) [$($fields[27])], $($old99) --> $($fields[3]), $($fields[99])" | Write-Host
	$fields -join ','
} | Set-Content -Path $outFile

Open in new window


Note that for the AC0012x, it currently only looks at field 100 - or can those show up in other fields as well?
0
100questionsAuthor Commented:
Thanks so much.  It should only appear once however I will test the script and let you know the result.
0
100questionsAuthor Commented:
The script works just fine, thank you.  Is it possible though instead of looking for a specific file, that it can take whatever *.csv file it sees and then it can simply use the name of the .csv file it sees and just rename it to Rev (for Revised) for instance?
0
Defend Against the Q2 Top Security Threats

Were you aware that overall malware worldwide was down a surprising 42% from Q1'18? Every quarter, the WatchGuard Threat Lab releases an Internet Security Report that analyzes the top threat trends impacting companies worldwide. Learn more by viewing our on-demand webinar today!

oBdACommented:
Wouldn't be PowerShell if it weren't.
$inFolder = 'C:\Temp'
$outSuffix = '_rev'
$replace_96587 = @{
	'CT' = '01-EAST'
	'FL' = '01-EAST'
	'GA' = '01-EAST'
	'IA' = '01-EAST'
	'IN' = '01-EAST'
	'NH' = '01-EAST'
	'PA' = '01-EAST'
	'NY' = '01-EAST'
	'WI' = '01-EAST'
	'CA' = '01-WEST'
	'CO' = '01-WEST'
	'TX' = '01-WEST'
	'WA' = '01-WEST'
}

$referenceCount = 0
$row = 0
Get-ChildItem -Path $inFolder -Filter *.csv | Where-Object {$_.BaseName -notmatch "$($outSuffix)\Z"} | ForEach-Object {
	$outFile = Join-Path -Path $_.DirectoryName -ChildPath "$($_.BaseName)$($outSuffix)$($_.Extension)"
	Write-Host "Processing '$($_.Name)' ..." -NoNewline
	Get-Content -Path $_.FullName | ForEach-Object {
		$row++
		$fields = $_.Split(',')
		If ($referenceCount -eq 0) {$referenceCount = $fields.Count}
		If ($fields.Count -ne $referenceCount) {
			Throw "Field count in line $($row) is incorrect (is: $($fields.Count), expected: $($referenceCount))!"
		}
		$old3 = $fields[3]
		$old99 = $fields[99]
		If ($fields[3] -eq '96587') {
			If ($replace_96587.ContainsKey($fields[27])) {
				$fields[3] = $replace_96587[$fields[27]]
			}
		}
		If ($fields[99] -match '\AAC(?<Number>0012[3-6])\Z') {
			$fields[99] = "$($Matches['Number'])C"
		}
		"line $($row): $($old3) [$($fields[27])], $($old99) --> $($fields[3]), $($fields[99])" | Write-Host
		$fields -join ','
	} | Set-Content -Path $outFile
	Write-Host " --> '$($outFile)'"
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
100questionsAuthor Commented:
Thank you.
And one last request please...  Is it possible to specify *.csv,, and secondly not to specify the path in the C drive.. but perhaps as you had it earlier to check in the current folder?
0
oBdACommented:
Just set the $inFolder variable to whatever you want, including '.'.
The *.csv filter is already defined (line 21).
0
100questionsAuthor Commented:
Many thanks for the awesome assistance, as usual.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.