E=mc2
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,1 772345,,20 181003,201 81107,2018 1114,,1234 567890000, COMP ACCOUNTS PAYABLE,123 ROAD WAY,,TREEWAY,FL,01234-5678 ,,,,123456 4870004,So mewhere Warehouse,200 Treeview Court NW,,Everywhere,GA,12345-12 34,,,018-4 41-5522,41 245,ABCORP BC,,USD,N, ,,,,,,,,,N ,,,,,,,,,, ,,,,,,,,,, ,,,,,,,,,, ,,,,,,,,,, ,,,,,,,,,, ,,00987894 561230,AC0 0123,15232 4,STEEL,,, ,123.00,US ,0.00,US,0 .00,US,10. 5000,0.000 0,,,,,,,,, ,,,,,,,,,, ,,,,,,,,,, ,,,
5,A,01234,96587,Y,1251/2,1 772345,,20 181003,201 81107,2018 1114,,1234 567890000, COMP ACCOUNTS PAYABLE,123 ROAR WAY,,TREEWAY,FL,01234-5678 ,,,,123456 4870004,So mewhere Warehouse,200 Treeview Court NW,,Everywhere,GA,12345-12 34,,,018-4 41-5522,41 245,ABCORP BC,,USD,N, ,,,,,,,,,N ,,,,,,,,,, ,,,,,,,,,, ,,,,,,,,,, ,,,,,,,,,, ,,,,,,,,,, ,,00987894 561560,AC0 0124,15234 5,WOOD,,,, 789.00,US, 0.00,US,0. 00,US,10.5 100,0.0000 ,,,,,,,,,, ,,,,,,,,,, ,,,,,,,,,, ,,
5,A,01234,96587,Y,1251/2,1 772345,,20 181003,201 81107,2018 1114,,1234 567890000, COMP ACCOUNTS PAYABLE,123 ROAD WAY,,TREEWAY,FL,01234-5678 ,,,,123456 4870004,So mewhere Warehouse,200 Treeview Court NW,,Everywhere,GA,12345-12 34,,,018-4 41-5522,41 245,ABCORP BC,,USD,N, ,,,,,,,,,N ,,,,,,,,,, ,,,,,,,,,, ,,,,,,,,,, ,,,,,,,,,, ,,,,,,,,,, ,,00987894 564560,004 56,152322, CANVAS,,,, 123.00,US, 0.00,US,0. 00,US,10.5 200,0.0000 ,,,,,,,,,, ,,,,,,,,,, ,,,,,,,,,, ,,
In the above text, each line starts with 5,A.. but it could differ.
Thanks in advance for your assistance.
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,1
5,A,01234,96587,Y,1251/2,1
5,A,01234,96587,Y,1251/2,1
In the above text, each line starts with 5,A.. but it could differ.
Thanks in advance for your assistance.
ASKER
Thanks so much. It should only appear once however I will test the script and let you know the result.
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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?
Just set the $inFolder variable to whatever you want, including '.'.
The *.csv filter is already defined (line 21).
The *.csv filter is already defined (line 21).
ASKER
Many thanks for the awesome assistance, as usual.
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?