troubleshooting Question

Script needed to search and replace

Avatar of E=mc2
E=mc2 asked on
PowershellWindows BatchScripting Languages
7 Comments1 Solution104 ViewsLast Modified:
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.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros