CODEWITHSUNDEEP
linuxbashawksed
ultraInstinct
ultraInstinct

Reputation: 4323

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.

Upvotes: 3

Views: 2860

Answers (3)

James Brown
James Brown

Reputation: 37394

If there really is space before the first column like the question shows, let's abuse it:

$ awk -F"(,* +)" '{for(i=j=1; ++j<NF; i++) $i=$j; $i=""} NR==1 {$3="col3"; OFS=","} 1' file
col1,col2,col3,col5
1,1,,1
2,2,,2
3,3,,3
4,4,,4

If not:

$ awk '{$(NF+=1)=$NF; $(NF-1)=(NR==1?"col3":"")","} 1' file
col1, col2, col3, col5
1, 1, , 1
2, 2, , 2
3, 3, , 3
4, 4, , 4

Upvotes: 0

Ed Morton
Ed Morton

Reputation: 203254

$ awk -v f=3 'BEGIN{FS=OFS=","} {$f = (NR>1?"":"col"f) FS $f} 1' file
col1,col2,col3,col4
1,1,,1
2,2,,2
3,3,,3
4,4,,4

The above was run on this file:

$ cat file
col1,col2,col4
1,1,1
2,2,2
3,3,3
4,4,4

add white space to taste...

Upvotes: 1

Inian
Inian

Reputation: 85560

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

Upvotes: 5

Related Questions