I am fairly new to scripting and have searched around on the internet but cannot seem to find a specific solution for what I'm trying to achieve so I'm hoping someone could please shed some light.
I have a .txt file that contains various lines of data which is organised by text starting at specific column numbers - basically a table of data. See example below which shows where each column starts:
| | |
|1214000 |1234567890 |ISRBWPX0001000001
| | |
|MD-3300 |+12345678912 |MDABWPX0001000001
| | |
| | |
| | |
Col:620 Col:632 Col:672
Please click here for screenshot if above example makes no sense
I want the script to find all lines which contains 'MD-' in column 620 and delete this so only the number is left. So I ran the Replace command in PowerShell which did delete all lines containing 'MD-' however it misaligned the rest of the columns;
PowerShell Command used:
(Get-Content "test.txt") |
Foreach-Object {$_.replace("MD-", "")} |
Set-Content "testedited.txt"
Output of above command:
| | |
|1214000 |1234567890 |ISRBWPX0001000001
| | |
|3300 |+12345678912 |MDABWPX0001000001
| | |
| | |
| | |
Col:620 Col:632 Col:672
Click here for screenshot if above example makes no sense
As you can see '+12345678912' is no longer aligned with Column 632 as well as 'MDABWPX0001000001' which is no longer aligned with Column 672.
Is there a way to carry out the above command without affecting other columns? I read somewhere that Hash tables can do this however I did not completely understand the method.
Desired output:
| | |
|1214000 |1234567890 |ISRBWPX0001000001
| | |
|3300 |+12345678912 |MDABWPX0001000001
| | |
| | |
| | |
Col:620 Col:632 Col:672
Please click here to see screenshot of desired output
I am open to use any scripting languages / methods to carry out this task so any suggestions would be much appreciated.
Thank you so much in advance.
$_ -replace '(?<=.{620})MD-([0-9]+)',('$1'+' '*3)