3

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:

"00:30:00","01:00:00","10/14/2014","RETURN","PASADENA","TX","12:30:00","sedan","","","corporate","CO01353"
"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.

Improve this question

2 Answers 2

Reset to default
3

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 
"00:30:00","01:00:00","10/14/2014","RETURN","PASADENA","TX","12:30:00","sedan","","","corporate","CO01353"
"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.

Improve this answer
1
  • this works great! and thanks very much for the explanation! Commented Oct 23, 2014 at 16:43
1

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'
Improve this answer
1
  • this is less complicated, but works great also! thanks for providing another great answer Commented Oct 23, 2014 at 16:43

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.