How to add new column with header to csv with awk

Question

I'm using some awk inside a bash script that's handling CSVs. The awk does this:

ORIG_FILE="score_model.csv"   
NEW_FILE="updates/score_model.csv"    
awk -v d="2017_01" -F"," 'BEGIN {OFS = ","} {$(NF+1)=d; print}' $ORIG_FILE > $NEW_FILE

Which does this transformation:

# before
model_description,      type,    effective_date, end_date
Inc <= 40K,             Retired, 08/05/2016,     07/31/2017
Inc > 40K Age <= 55 V5, Retired, 04/30/2016,     07/31/2017
Inc > 40K Age > 55 V5 , Retired, 04/30/2016,     07/31/2017

# after, bad
model_description,      type,    effective_date, end_date,   2017_01  
Inc <= 40K,             Retired, 08/05/2016,     07/31/2017, 2017_01
Inc > 40K Age <= 55 V5, Retired, 04/30/2016,     07/31/2017, 2017_01
Inc > 40K Age > 55 V5 , Retired, 04/30/2016,     07/31/2017, 2017_01

I want the new column to have a header, so that the new CSV looks like

# after, desired
model_description,      type,    effective_date, end_date,   cmpgn_group  
Inc <= 40K,             Retired, 08/05/2016,     07/31/2017, 2017_01
Inc > 40K Age <= 55 V5, Retired, 04/30/2016,     07/31/2017, 2017_01
Inc > 40K Age > 55 V5 , Retired, 04/30/2016,     07/31/2017, 2017_01

I know there's a way to specify what to do in the first row separately, but I haven't been able to figure it out.

Rahul Verma · Accepted Answer · 2017-10-06 20:52:19Z

6

using sed

$ sed '1s/$/,\tcmpgn_group/; 2,$s/$/,\t2017_01/' file

i.e for 1st line : append ,\tcmpgn_group
and for 2 to $ : append ,\t2017_01

using awk

$ awk -v d="2017_01" -F"," 'FNR==1{a="cmpgn_group"} FNR>1{a=d} {print $0",\t"a}' f1

Output:

model_description,      type,    effective_date, end_date,      cmpgn_group
Inc <= 40K,             Retired, 08/05/2016,     07/31/2017,    2017_01
Inc > 40K Age <= 55 V5, Retired, 04/30/2016,     07/31/2017,    2017_01
Inc > 40K Age > 55 V5 , Retired, 04/30/2016,     07/31/2017,    2017_01

answered Oct 6, 2017 at 20:52

Rahul Verma

3,1091 gold badge17 silver badges28 bronze badges

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

Comments

RavinderSingh13 · Accepted Answer · 2017-10-07 01:50:04Z

Following awk(a bit changed in your solution) should work for you.

ORIG_FILE="score_model.csv"   
NEW_FILE="updates/score_model.csv"    
awk -v d="2017_01" -F"," 'BEGIN {OFS = ","} FNR==1{$(NF+1)="cmpgn_group"} FNR>1{$(NF+1)=d;} 1' $ORIG_FILE > $NEW_FILE

Solution 2nd: Or let's remove this $(NF+1)(creating a new field approach) and try to directly print it.

awk -v d="2017_01" -F"," 'BEGIN {OFS = ","} {printf("%s%s",$0,FNR>1?d RS:"cmpgn_group" RS)}' $ORIG_FILE > $NEW_FILE

Explanation of above command:

awk -v d="2017_01" -F"," ' ##Setting valur of variable named d as 2017_01 and setting field separator as comma.
BEGIN{                     ##Starting BEGIN section of awk here.
  OFS = ","                ##Setting Output field separator as comma here.
}                          ##Closing BEGIN block here.
{
  printf("%s%s",$0,FNR>1?d RS:"cmpgn_group" RS) ##Using printf here to print the lines. So %s%s means to print 2 strings here. First I am simply printing $0(current line). Then while printing second string using condition FNR>1(when line number is greater than 1) then print variable d(which we want to add at last) with RS(to print a new line here). Else(if condition FNR>1 is not true) then it means it is very first line of Input_file and print string "cmpn_groups" with RS(record separator) whose default value is a new line.
}
' $ORIG_FILE > $NEW_FILE   ##Mentioning Input_file named #ORIG_FILE and redirecting it's output to $NEW_FILE here.

Ed Morton · Accepted Answer · 2017-10-06 18:42:45Z

4

awk -v d="2017_01" 'BEGIN{FS=OFS=","} {print $0, (NR>1?d:"cmpgn_group")}' file

edited Oct 6, 2017 at 18:42

answered Oct 6, 2017 at 17:19

Ed Morton

209k18 gold badges90 silver badges212 bronze badges

Collectives™ on Stack Overflow

How to add new column with header to csv with awk

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related