I've been trying to setup a script to run on gitbash, to process a CSV file so each record that has a specific value, the last field is changed from empty ("") to a value that iterates from 1 to 16. Also, the updating value is prepended by some text.

The field should look like REP0001, end up as REP0100, then start over REP0001, for each record in the CSV file that matches.

Here's a sample of the input text:

"01:00:00","01:30:00","10/14/2014","RENT OUT","HOUSTON","TX","00:30:00","sedan","","","personal",""

The first line example, I don't want to change, but still include it in the output. The second line example, I want to change the last field from "" to a value that starts with REP0001 and iterates to REP0100, then starts over at REP0001.

Here's a sample of the desired text:

"01:00:00","01:30:00","10/14/2014","RENT OUT","HOUSTON","TX","00:30:00","sedan","","","personal","REP0001"

I did try sed, as well as awk but I'm not a pro at scripting. I have only been able to put together the part that finds the records with the value I want, and insert the value I want. But I don't know how to do the iteration magic:

awk 'BEGIN{FS=",";OFS=","} $4 ~ /"RENT OUT"/ {$12="\042""REP0001""\042"}1' Rentals.csv  > output

Can someone point me in the right direction to do this? The file itself has about 2000 lines in it.


I believe that this does what you want:

$ awk 'BEGIN{FS=",";OFS=","} $4 ~ /"RENT OUT"/ {NF--;printf $0; x=x%100;x++; printf ",\"REP%04i\"\n",x;next} 1' rentals.csv 
"01:00:00","01:30:00","10/14/2014","RENT OUT","HOUSTON","TX","00:30:00","sedan","","","personal","REP0001"

The only part that is changed is this command:

$4 ~ /"RENT OUT"/ {NF--;printf $0; x=x%100;x++; printf ",\"REP%04i\"\n",x;next}

Taking each new piece one at a time:

  • NF--

    This removes the (blank) last field from the line.

  • `printf $0

    This prints the line (now without its last field).

  • x=x%100;x++

    This takes the counter x modulo 100 and then increments it by 1. In this way, the counter will cycle from 1 to 100 and back to 1.

  • printf ",\"REP%04i\"\n",x

    This prints our new last field that includes the counter.

  • next

    Since we have already printed this line, we tell awk to skip the rest of the commands and start over with the next line.

  • this works great! and thanks very much for the explanation! – Manny Lornu Oct 23 '14 at 16:43

Another (a little bit more compacted) version with sprintf:

awk 'BEGIN{FS=OFS=","} $4 ~ /"RENT OUT"/ {$12=sprintf("\"REP%04i\"",++i);i=i%100}1'
  • +1. I think this one is less complicated. – user78605 Oct 23 '14 at 9:41
  • this is less complicated, but works great also! thanks for providing another great answer – Manny Lornu Oct 23 '14 at 16:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.