Avatar of E=mc2
E=mc2
Flag for Canada asked on

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.
PowershellWindows BatchScripting Languages

Avatar of undefined
Last Comment
E=mc2

8/22/2022 - Mon
oBdA

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?
E=mc2

ASKER
Thanks so much.  It should only appear once however I will test the script and let you know the result.
E=mc2

ASKER
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?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
oBdA

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
E=mc2

ASKER
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?
oBdA

Just set the $inFolder variable to whatever you want, including '.'.
The *.csv filter is already defined (line 21).
E=mc2

ASKER
Many thanks for the awesome assistance, as usual.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.