Add Column to End of CSV File Using 'Awk' in Bash Script

Add Column to end of CSV file using 'awk' in BASH script

You may add a comma to OFS (Output Field Separator):

awk -F"," 'BEGIN { OFS = "," } {$6="2012-02-29 16:13:00"; print}' input.csv > output.csv

Output:

2012-02-29,01:00:00,Manhatten,New York,234,2012-02-29 16:13:00
2012-02-29,01:00:00,Manhatten,New York,843,2012-02-29 16:13:00
2012-02-29,01:00:00,Manhatten,New York,472,2012-02-29 16:13:00
2012-02-29,01:00:00,Manhatten,New York,516,2012-02-29 16:13:00

---

EDIT to answer the comment of SirOracle:

From awk man page:

       -v var=val
       --assign var=val
              Assign the value val to the variable var, before execution of the program begins.  Such 
              variable values are available to the BEGIN block of an AWK program.

So assign your date to a shell variable and use it inside awk:

mydate=$(date)
awk -v d="$mydate" -F"," 'BEGIN { OFS = "," } {$6=d; print}' input.csv > output.csv

How to add a column in a csv file (at middle and end) in bash linux using awk?

This

awk -F"," 'BEGIN { FS=OFS = "," } {$2="2021-06-23"; print}' input.csv

does overwrite 2nd column using 2021-06-23. Note also that this code is redundant - you are setting field seperator twice, once using -F then setting FS in BEGIN, code above has same effect as

awk 'BEGIN { FS=OFS = "," } {$2="2021-06-23"; print}' input.csv

You want to add column, if you know in advance number of columns in your .csv file you might do, for 3 columns:

awk 'BEGIN{FS=OFS=", "}{print $1,"2021-06-23",$2,$3}' input.csv

which for input.csv content being

id, name, shortName
1, Example1, Ex1
2, Example2, Ex2

output

id, 2021-06-23, name, shortName
1, 2021-06-23, Example1, Ex1
2, 2021-06-23, Example2, Ex2

(tested in gawk 4.2.1)

How to add new column with header to csv with awk

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.

Adding a new column to a CSV file

With sed:

sed 'N;s/^/"YYYY-MM-DD HH:MM:SS",/' file

If you want to make sure there is a single " on next line:

sed 'N;/\n"$/s/^/"YYYY-MM-DD HH:MM:SS",/' file

Edit:

To insert the string after 5th field:

sed 'N;s/","/","YYYY-MM-DD HH:MM:SS","/5' file

How can I add a column to a specific position in a csv file using cat, sed, awk or cut?

This is the best I could do with awk

$  awk -v FS=',' -v OFS=',' 'BEGIN{print "col1, col2, col3, col4"} NR>1{k=$3; $3="\t"; $4=k; print $0}' newfile
col1, col2, col3, col4
    1,    1,    ,    1
    2,    2,    ,    2
    3,    3,    ,    3
    4,    4,    ,    4

You could write it to a new file using the redirection operator (> newfile at the end)

The logic is straight-forward:-

• -v FS=',' -v OFS=',' sets the input and output field separator to comma(,)
• BEGIN{print "col1, col2, col3, col4"} creates a new header with the updated column name, remember the BEGIN block in awk is executed before actual processing of the file
• For the <action> part in awk, NR>1{k=$3; $3="\t"; $4=k; print $0} am skipping the header from the original file, taking a backup of the 3rd column in k, over-writing it and replacing with a empty tab-space and 4th column is restored as the older 3rd column value.

Another cool suggestion by fedorqui in the comments, the following even simpler way to do it.

$ awk 'BEGIN {FS=OFS=","} NR==1{$0="col1, col2, col3, col4"} NR>1{$3=sprintf("\t,%s",$3)}1' file
col1, col2, col3, col4
    1,    1,    ,    1
    2,    2,    ,    2
    3,    3,    ,    3
    4,    4,    ,    4

How to redirect output for change specific csv column and write into same file. Logically: awk 'BEGIN{FS=OFS=,} {$19 = $1} 1' a.csv toSameFile

GNU awk:

awk -i inplace 'BEGIN{FS=OFS=","} {$19 = $1} 1' a.csv > a.csv

any awk:

tmp=$(mktemp) &&
awk 'BEGIN{FS=OFS=","} {$19 = $1} 1' a.csv > "$tmp" &&
mv -- "$tmp" a.csv

The latter works for any command. You can't just do cmd file > file because your shell can do the > file part first to init the output file and then when it tries to run the cmd file part file is already empty as you experienced. The &&s are required to ensure the current step succeeded before you do the next step otherwise you could end up zapping your input file and losing all it's contents if mktemp or awk failed for some reason.

Add a new column to the file

try:

awk 'BEGIN{getline to_add < "f3"}{print $0,to_add}' f

Reads the column to add from file "f3" and saves it in the variable to_add. After that it adds the column to each line of file f.

HTH Chris

shell script to add a new column at last

You can just use this awk:

awk 'BEGIN { OFS = "," } {print $0, "z"}' input.csv > output.csv

Or even simpler to use sed:

sed 's/$/,z/' input.csv > output.csv

---

Related Topics