Remove white spaces and replace "-" with 0 - JREPL?

[SIMMS7400]

Hi Folks -

I'm looking for an efficient way to remove leading and trailing white spaces and replace ", - " with ",0" in column 4 of my .csv.

Here is a portion of my data file:

Code: Select all

US_4672,PFP-A1045900,12/1/2017, 0.75 
US_4672,PFP-A1045900,3/1/2018, 0.75 
US_4672,PFP-A1045900,6/1/2018, 0.75 
US_4672,PFP-A1045900,9/1/2018, 0.75 
US_4672,PFP-A1045900,12/1/2018, 0.75 
US_4672,PFP-A1045900,3/1/2019, 0.75 
US_4672,PFP-A1045900,6/1/2019, 0.75 
US_4672,PFP-A1045900,9/1/2019, 0.75 
US_4672,PFP-A1045900,12/1/2019, 0.75 
US_4672,PFP-A1045900,3/1/2020, 0.75 
US_4641,PFP-A1045900,12/1/2017, 0.50 
US_4641,PFP-A1045900,3/1/2018, 0.50 
US_4641,PFP-A1045900,6/1/2018, 0.50 
US_4641,PFP-A1045900,9/1/2018, 0.50 
US_4641,PFP-A1045900,12/1/2018, 0.50 
US_4641,PFP-A1045900,3/1/2019, 0.50 
US_4641,PFP-A1045900,6/1/2019, 0.50 
US_4641,PFP-A1045900,9/1/2019, 0.50 
US_4641,PFP-A1045900,12/1/2019, 0.50 
US_4641,PFP-A1045900,3/1/2020, 0.50 
US_367,PFP-A1049600,12/1/2017, 1.00 
US_367,PFP-A1049600,3/1/2018, 0.50 
US_367,PFP-A1049600,6/1/2018, -   
US_367,PFP-A1049600,9/1/2018, -   
US_367,PFP-A1049600,12/1/2018, -   
US_367,PFP-A1049600,3/1/2019, -   
US_367,PFP-A1049600,6/1/2019, -   
US_367,PFP-A1049600,9/1/2019, -   
US_367,PFP-A1049600,12/1/2019, -   
US_367,PFP-A1049600,3/1/2020, - 

Can JREPL be employed in this case? Otherwise, i was just going to run this through a for loop and then a find&replace method. Was hoping to use JREPL to do both, however.

[dbenham]

Of course. Here is the first solution that comes to mind:

Code: Select all

jrepl ",\s*-\s*$|,\s*(?=[^,]*$)" ",0|," /t "|" /f test.txt

Dave Benham

[SIMMS7400]

Hi Dave -

Works like a charm! Wow, thank you!

Thank you!