Awk add a variable to part of a column string

Question

Objective

add "67" to column 1 of the output file with 67 being the variable ($iv) classified on the difference between 2 dates.

File1.csv

display,dc,client,20572431,5383594
display,dc,client,20589101,4932821
display,dc,client,23030494,4795549
display,dc,client,22973424,5844194
display,dc,client,21489000,4251031
display,dc,client,23150347,3123945
display,dc,client,23194965,2503875
display,dc,client,20578983,1522448
display,dc,client,22243554,920166
display,dc,client,20572149,118865
display,dc,client,23077785,28077
display,dc,client,21811100,5439

Current Output 3_file1.csv

BOB-UK-,display,dc,client,20572431,5383594,0.05,269.18
BOB-UK-,display,dc,client,20589101,4932821,0.05,246.641
BOB-UK-,display,dc,client,23030494,4795549,0.05,239.777
BOB-UK-,display,dc,client,22973424,5844194,0.05,292.21
BOB-UK-,display,dc,client,21489000,4251031,0.05,212.552
BOB-UK-,display,dc,client,23150347,3123945,0.05,156.197
BOB-UK-,display,dc,client,23194965,2503875,0.05,125.194
BOB-UK-,display,dc,client,20578983,1522448,0.05,76.1224
BOB-UK-,display,dc,client,22243554,920166,0.05,46.0083
BOB-UK-,display,dc,client,20572149,118865,0.05,5.94325
BOB-UK-,display,dc,client,23077785,28077,0.05,1.40385
BOB-UK-,display,dc,client,21811100,5439,0.05,0.27195
TOTAL,,,,,33430004,,1671.5

Desired Output 3_file1.csv

BOB-UK-67,display,dc,client,20572431,5383594,0.05,269.18
BOB-UK-67,display,dc,client,20589101,4932821,0.05,246.641
BOB-UK-67,display,dc,client,23030494,4795549,0.05,239.777
BOB-UK-67,display,dc,client,22973424,5844194,0.05,292.21
BOB-UK-67,display,dc,client,21489000,4251031,0.05,212.552
BOB-UK-67,display,dc,client,23150347,3123945,0.05,156.197
BOB-UK-67,display,dc,client,23194965,2503875,0.05,125.194
BOB-UK-67,display,dc,client,20578983,1522448,0.05,76.1224
BOB-UK-67,display,dc,client,22243554,920166,0.05,46.0083
BOB-UK-67,display,dc,client,20572149,118865,0.05,5.94325
BOB-UK-67,display,dc,client,23077785,28077,0.05,1.40385
BOB-UK-67,display,dc,client,21811100,5439,0.05,0.27195
TOTAL,,,,,33430004,,1671.5

Current Code

#! bin/sh

set -eu

de=$(date +"%d-%m-%Y" -d "1 month ago")
ds="15-04-2014"
iv=$(awk -vdate1=$de -vdate2=$ds 'BEGIN{split(date1, A,"-");split(date2, B,"-");year_diff=A[3]-B[3];if(year_diff){months_diff=A[2] + 12 * year_diff - B[2] + 1;} else {months_diff=A[2]>B[2]?A[2]-B[2]+1:B[2]-A[2]+1};print months_diff}')



for f in $(find *.csv); do

    awk -F"," -v OFS=',' '{print "BOB-UK-"$iv,$0,0.05}' $f > "1_$f.csv" ##PROBLEM LINE##
    awk -F"," -v OFS=',' '{print $0,$6*$7/1000}' "1_$f.csv" > "2_$f.csv" ##calculate price
    awk -F"," -v OFS=',' '{print $0}; {sum+=$6}{sum2+=$8} END {print "TOTAL,,,,," (sum)",,"(sum2)}' "2_$f.csv" > "3_$f.csv" ##calculate total

done

Issue

When I run the first awk line (Marked as "## PROBLEM LINE##") the loop doesn't change column $1 to include the "67" after "BOB-UK-". This should be done with the print "BOB-UK-"$iv but instead it doesn't do anything. I suspect this is due to the way print works in awk but I haven't been able to work out a way to treat it within this row. Does anyone know if this is possible or do I need to create a new row to achieve this?

Good that you have shown what you have tried to solve your problem. If you explain it more clearly what you are looking for in description kind of(problem's description in detailed manner) then we will get better understanding of question and we may help better, kindly add more information in your question and let us know. — RavinderSingh13
– RavinderSingh13, Commented Nov 25, 2019 at 10:55
Also got that part like you have to read multiple csv files then you are taking dates differences but question is what should be the logic to get your expected output? And are you looking for saving the output into Input_file itself? — RavinderSingh13
– RavinderSingh13, Commented Nov 25, 2019 at 10:58
@RavinderSingh13 Apologies, I realised I mixed up the data set a bit. I've updated the post to include; the correct data, what I get, and what I'm trying to get - along with a more detailed explanation. Let me know if it makes sense now or if it's still unclear? — El_Birdo
– El_Birdo, Commented Nov 25, 2019 at 11:58

KamilCuk · Accepted Answer · 2019-11-25 14:45:23Z

3

You have to pass the variable value to awk. awk does not inherit variables from the shell and does not expand $variable variables like shell. It is another tool with it's internal language.

awk -v iv="$iv" -F"," -v OFS=',' '{print "BOB-UK-"iv,$0,0.05}' "$f"

Tested in repl with the input provided.

for f in $(find *.csv)

Is useless use of find, makes no sense, just

for f in *.csv

Also note that you are creating 1_$f.csv, 2_$f.csv and 3_$f.csv files in the current directory in your loop, so the next time you run your script there will be 4 times more .csv files to iterate through. Dunno if that's relevant.

How `$iv` works in awk?

The $<number> is the field number <number> from the line in awk. So for example the $1 is the first field of the line in awk. The $2 is the second field. The $0 is special and it is the whole line.

The $iv expands to $ + the value of iv. So for example:

echo a b c | awk '{iv=2; print $iv}'

will output b, as the $iv expands to $2 then $2 expands to the second field from the input - ie. b.

Uninitialized variables in awk are initialized with 0. So $iv is substituted for $0 in your awk line, so it expands for the whole line.

edited Nov 25, 2019 at 14:45

answered Nov 25, 2019 at 13:14

KamilCuk

146k8 gold badges84 silver badges154 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

El_Birdo Over a year ago

your example spits out a syntax error for the, in OFS=','. It seems I had to remove this to then get the code to run but in doing so it causes my rows to merge and produce the following example BOB-UK-67 display,dc,client,20572431,5383594 0.05. Is there a reaason why the OFS is rejected as it seems to be needed in order to keep my columns in the correct locations, and prevent the merging.

KamilCuk Over a year ago

Did you copy it exactly as presented? Including all the "? Who "spits out the syntax error"? bash or awk (or other tool)? How does exactly the "syntax error" message look like? Please be specific, don't describe what happens, show what happens.

Ed Morton Over a year ago

@El_Birdo there is no syntax error in the posted script, you either copy/pasted incorrectly or there's a syntax error (e.g. missing terminating quote) elsewhere in your shell script. Run it through shellcheck.net.

El_Birdo Over a year ago

Apologies, your code works. The error appeared to be coming from an additional part of my script (not mentioned in the post) which I didn't think would impact it. Thanks for your help.

Collectives™ on Stack Overflow

Awk add a variable to part of a column string

Objective

Current Code

Issue

1 Answer 1

How `$iv` works in awk?

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Objective

Current Code

Issue

1 Answer 1

How $iv works in awk?

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related

How `$iv` works in awk?