ebertbm ebertbm - 4 months ago 8
Linux Question

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

I've got a csv file with this content:

col1, col2, col5
1, 1, 1
2, 2, 2
3, 3, 3
4, 4, 4


I need to add a column in the third position of the file with a header but with empty values.

col1, col2, col3, col4
1, 1, , 1
2, 2, , 2
3, 3, , 3
4, 4, , 4


Is there any way to do it using one of the Linux commands like cat, sed, awk or cut?

I know that with cut is possible to modify a file by columns cut -d , -f1,2,3 file1 > file2. But it seems does not have an option to add a custom column.

Any help would be much appreciated.

Answer

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