Shell script CSV processing - adding new column with AWK

Question

I have a shell script which processes CSV file. One step in particular is adding a column and putting default value "null" in it. I got the expected change, its just that the new column to be added gets added to the next line instead of the same line.

Can anyone suggest whats wrong in the code and causing this unexpected change?

CODE:

awk 'BEGIN{FS=",";OFS=";"} {$(NF+1) = NR==1 ? "NewColm" : "NULL"} 1' source.csv > final.csv

Input CSV:

OldColm1,OldColm2,OldColm3,OldColm4,OldColm5,OldColm6
Value1,Value2,Value3,Value4,Value5,Value6

Output CSV:

OldColm1;OldColm2;OldColm3;OldColm4;OldColm5;OldColm6
;NewColm
Value1;Value2;Value3;Value4;Value5;Value6
;NULL

Expected CSV:

OldColm1;OldColm2;OldColm3;OldColm4;OldColm5;OldColm6;NewColm
Value1;Value2;Value3;Value4;Value5;Value6;NULL

Do you have CRLF line endings ? Also where is value7 coming from ? — 123
– 123, Commented Apr 20, 2016 at 10:20
The value7 was written by mistake which i corrected now. I dont know how to identify CRLF line ending or CRLF — data-bite
– data-bite, Commented Apr 20, 2016 at 10:26

mschilli · Accepted Answer · 2018-03-23 14:32:14Z

As explained in the comments, this was caused by the lines being separated by \r\n instead of \n.

The od programm can be used to illustrate this:

cat source_dos.csv

OldColm1,OldColm2,OldColm3,OldColm4,OldColm5,OldColm6
Value1,Value2,Value3,Value4,Value5,Value6

od -c source_dos.csv

0000000   O   l   d   C   o   l   m   1   ,   O   l   d   C   o   l   m
0000020   2   ,   O   l   d   C   o   l   m   3   ,   O   l   d   C   o
0000040   l   m   4   ,   O   l   d   C   o   l   m   5   ,   O   l   d
0000060   C   o   l   m   6  \r  \n   V   a   l   u   e   1   ,   V   a
0000100   l   u   e   2   ,   V   a   l   u   e   3   ,   V   a   l   u
0000120   e   4   ,   V   a   l   u   e   5   ,   V   a   l   u   e   6
0000140  \r  \n
0000142

awk 'BEGIN{FS=",";OFS=";"}
     {$(NF+1) = NR==1 ? "NewColm" : "NULL"}
     1
    ' source_dos.csv

;NewColm;OldColm2;OldColm3;OldColm4;OldColm5;OldColm6
;NULL1;Value2;Value3;Value4;Value5;Value6

awk 'BEGIN{FS=",";OFS=";"}
     {$(NF+1) = NR==1 ? "NewColm" : "NULL"}
     1
    ' source_dos.csv | od -c

0000000   O   l   d   C   o   l   m   1   ;   O   l   d   C   o   l   m
0000020   2   ;   O   l   d   C   o   l   m   3   ;   O   l   d   C   o
0000040   l   m   4   ;   O   l   d   C   o   l   m   5   ;   O   l   d
0000060   C   o   l   m   6  \r   ;   N   e   w   C   o   l   m  \n   V
0000100   a   l   u   e   1   ;   V   a   l   u   e   2   ;   V   a   l
0000120   u   e   3   ;   V   a   l   u   e   4   ;   V   a   l   u   e
0000140   5   ;   V   a   l   u   e   6  \r   ;   N   U   L   L  \n
0000157

A ~~work-around~~ solution provided in the comments is to convert the input from DOS-like (\r) to UNIX-like (\n) input:

cp source_dos.csv source_unix.csv && dos2unix source_unix.csv

dos2unix: converting file source_unix.csv to Unix format ...

od -c source_unix.csv

0000000   O   l   d   C   o   l   m   1   ,   O   l   d   C   o   l   m
0000020   2   ,   O   l   d   C   o   l   m   3   ,   O   l   d   C   o
0000040   l   m   4   ,   O   l   d   C   o   l   m   5   ,   O   l   d
0000060   C   o   l   m   6  \n   V   a   l   u   e   1   ,   V   a   l
0000100   u   e   2   ,   V   a   l   u   e   3   ,   V   a   l   u   e
0000120   4   ,   V   a   l   u   e   5   ,   V   a   l   u   e   6  \n
0000140

awk 'BEGIN{FS=",";OFS=";"}
     {$(NF+1) = NR==1 ? "NewColm" : "NULL"}
     1
    ' source_unix.csv

OldColm1;OldColm2;OldColm3;OldColm4;OldColm5;OldColm6;NewColm
Value1;Value2;Value3;Value4;Value5;Value6;NULL

awk 'BEGIN{FS=",";OFS=";"}
     {$(NF+1) = NR==1 ? "NewColm" : "NULL"}
     1
    ' source_unix.csv | od -c

0000000   O   l   d   C   o   l   m   1   ;   O   l   d   C   o   l   m
0000020   2   ;   O   l   d   C   o   l   m   3   ;   O   l   d   C   o
0000040   l   m   4   ;   O   l   d   C   o   l   m   5   ;   O   l   d
0000060   C   o   l   m   6   ;   N   e   w   C   o   l   m  \n   V   a
0000100   l   u   e   1   ;   V   a   l   u   e   2   ;   V   a   l   u
0000120   e   3   ;   V   a   l   u   e   4   ;   V   a   l   u   e   5
0000140   ;   V   a   l   u   e   6   ;   N   U   L   L  \n
0000155

An awk-only solution to deal with this would be to adjust the record separator RS accordingly.

RS, as well as its counterpart the output record separator ORS, default to \n. That's why in the \r\n input case, the \r remains part of the last input column and your new column gets 'stuck' in between this \r and the \n added as ORS.

Changing RS solves this:

awk 'BEGIN{RS="\r\n";FS=",";OFS=";"}
     {$(NF+1) = NR==1 ? "NewColm" : "NULL"}
     1
    ' source_dos.csv

OldColm1;OldColm2;OldColm3;OldColm4;OldColm5;OldColm6;NewColm
Value1;Value2;Value3;Value4;Value5;Value6;NULL

Note that this will still create UNIX-like (\n) output:

awk 'BEGIN{RS="\r\n";FS=",";OFS=";"}
     {$(NF+1) = NR==1 ? "NewColm" : "NULL"}
     1
    ' source_dos.csv | od -c

0000000   O   l   d   C   o   l   m   1   ;   O   l   d   C   o   l   m
0000020   2   ;   O   l   d   C   o   l   m   3   ;   O   l   d   C   o
0000040   l   m   4   ;   O   l   d   C   o   l   m   5   ;   O   l   d
0000060   C   o   l   m   6   ;   N   e   w   C   o   l   m  \n   V   a
0000100   l   u   e   1   ;   V   a   l   u   e   2   ;   V   a   l   u
0000120   e   3   ;   V   a   l   u   e   4   ;   V   a   l   u   e   5
0000140   ;   V   a   l   u   e   6   ;   N   U   L   L  \n
0000155

To generate DOS-like (\r\n) output, just adjust ORS, too:

awk 'BEGIN{RS="\r\n";ORS=RS;FS=",";OFS=";"}
     {$(NF+1) = NR==1 ? "NewColm" : "NULL"}
     1
    ' source_dos.csv

OldColm1;OldColm2;OldColm3;OldColm4;OldColm5;OldColm6;NewColm
Value1;Value2;Value3;Value4;Value5;Value6;NULL

awk 'BEGIN{RS="\r\n";ORS=RS;FS=",";OFS=";"}
     {$(NF+1) = NR==1 ? "NewColm" : "NULL"}
     1
    ' source_dos.csv | od -c

0000000   O   l   d   C   o   l   m   1   ;   O   l   d   C   o   l   m
0000020   2   ;   O   l   d   C   o   l   m   3   ;   O   l   d   C   o
0000040   l   m   4   ;   O   l   d   C   o   l   m   5   ;   O   l   d
0000060   C   o   l   m   6   ;   N   e   w   C   o   l   m  \r  \n   V
0000100   a   l   u   e   1   ;   V   a   l   u   e   2   ;   V   a   l
0000120   u   e   3   ;   V   a   l   u   e   4   ;   V   a   l   u   e
0000140   5   ;   V   a   l   u   e   6   ;   N   U   L   L  \r  \n
0000157

Note however that this will fail for UNIX-like (\n) input:

awk 'BEGIN{RS="\r\n";FS=",";OFS=";"}
     {$(NF+1) = NR==1 ? "NewColm" : "NULL"}
     1
    ' source_unix.csv

OldColm1;OldColm2;OldColm3;OldColm4;OldColm5;OldColm6
Value1;Value2;Value3;Value4;Value5;Value6
;NewColm

awk 'BEGIN{RS="\r\n";FS=",";OFS=";"}
     {$(NF+1) = NR==1 ? "NewColm" : "NULL"}
     1
    ' source_unix.csv | od -c

0000000   O   l   d   C   o   l   m   1   ;   O   l   d   C   o   l   m
0000020   2   ;   O   l   d   C   o   l   m   3   ;   O   l   d   C   o
0000040   l   m   4   ;   O   l   d   C   o   l   m   5   ;   O   l   d
0000060   C   o   l   m   6  \n   V   a   l   u   e   1   ;   V   a   l
0000100   u   e   2   ;   V   a   l   u   e   3   ;   V   a   l   u   e
0000120   4   ;   V   a   l   u   e   5   ;   V   a   l   u   e   6  \n
0000140   ;   N   e   w   C   o   l   m  \n
0000151

Why I think this is better than using dos2unix: Using a regular expression (RE) as RS one can make it work for both \n and \r\n-separated input without the need to know which of the two it is:

awk 'BEGIN{RS="\r?\n";FS=",";OFS=";"}
     {$(NF+1) = NR==1 ? "NewColm" : "NULL"}
     1
    ' source_dos.csv

OldColm1;OldColm2;OldColm3;OldColm4;OldColm5;OldColm6;NewColm
Value1;Value2;Value3;Value4;Value5;Value6;NULL

awk 'BEGIN{RS="\r?\n";FS=",";OFS=";"}
     {$(NF+1) = NR==1 ? "NewColm" : "NULL"}
     1
    ' source_unix.csv

OldColm1;OldColm2;OldColm3;OldColm4;OldColm5;OldColm6;NewColm
Value1;Value2;Value3;Value4;Value5;Value6;NULL

In both cases, UNIX-like (\n) output will be generated:

awk 'BEGIN{RS="\r?\n";FS=",";OFS=";"}
     {$(NF+1) = NR==1 ? "NewColm" : "NULL"}
     1
    ' source_dos.csv | od -c

0000000   O   l   d   C   o   l   m   1   ;   O   l   d   C   o   l   m
0000020   2   ;   O   l   d   C   o   l   m   3   ;   O   l   d   C   o
0000040   l   m   4   ;   O   l   d   C   o   l   m   5   ;   O   l   d
0000060   C   o   l   m   6   ;   N   e   w   C   o   l   m  \n   V   a
0000100   l   u   e   1   ;   V   a   l   u   e   2   ;   V   a   l   u
0000120   e   3   ;   V   a   l   u   e   4   ;   V   a   l   u   e   5
0000140   ;   V   a   l   u   e   6   ;   N   U   L   L  \n
0000155

awk 'BEGIN{RS="\r?\n";FS=",";OFS=";"}
     {$(NF+1) = NR==1 ? "NewColm" : "NULL"}
     1
    ' source_unix.csv | od -c

0000000   O   l   d   C   o   l   m   1   ;   O   l   d   C   o   l   m
0000020   2   ;   O   l   d   C   o   l   m   3   ;   O   l   d   C   o
0000040   l   m   4   ;   O   l   d   C   o   l   m   5   ;   O   l   d
0000060   C   o   l   m   6   ;   N   e   w   C   o   l   m  \n   V   a
0000100   l   u   e   1   ;   V   a   l   u   e   2   ;   V   a   l   u
0000120   e   3   ;   V   a   l   u   e   4   ;   V   a   l   u   e   5
0000140   ;   V   a   l   u   e   6   ;   N   U   L   L  \n
0000155

To set the output type according to the input type, the ORS can be set per record to the actual text that matched the RS RE, RT:

awk 'BEGIN{RS="\r?\n";FS=",";OFS=";"}
     {ORS=RT}
     {$(NF+1) = NR==1 ? "NewColm" : "NULL"}
     1
    ' source_dos.csv

OldColm1;OldColm2;OldColm3;OldColm4;OldColm5;OldColm6;NewColm
Value1;Value2;Value3;Value4;Value5;Value6;NULL

awk 'BEGIN{RS="\r?\n";FS=",";OFS=";"}
     {ORS=RT}
     {$(NF+1) = NR==1 ? "NewColm" : "NULL"}
     1
    ' source_unix.csv

OldColm1;OldColm2;OldColm3;OldColm4;OldColm5;OldColm6;NewColm
Value1;Value2;Value3;Value4;Value5;Value6;NULL

awk 'BEGIN{RS="\r?\n";FS=",";OFS=";"}
     {ORS=RT}
     {$(NF+1) = NR==1 ? "NewColm" : "NULL"}
     1
    ' source_dos.csv | od -c

0000000   O   l   d   C   o   l   m   1   ;   O   l   d   C   o   l   m
0000020   2   ;   O   l   d   C   o   l   m   3   ;   O   l   d   C   o
0000040   l   m   4   ;   O   l   d   C   o   l   m   5   ;   O   l   d
0000060   C   o   l   m   6   ;   N   e   w   C   o   l   m  \r  \n   V
0000100   a   l   u   e   1   ;   V   a   l   u   e   2   ;   V   a   l
0000120   u   e   3   ;   V   a   l   u   e   4   ;   V   a   l   u   e
0000140   5   ;   V   a   l   u   e   6   ;   N   U   L   L  \r  \n
0000157

awk 'BEGIN{RS="\r?\n";FS=",";OFS=";"}
     {ORS=RT}
     {$(NF+1) = NR==1 ? "NewColm" : "NULL"}
     1
    ' source_unix.csv | od -c

0000000   O   l   d   C   o   l   m   1   ;   O   l   d   C   o   l   m
0000020   2   ;   O   l   d   C   o   l   m   3   ;   O   l   d   C   o
0000040   l   m   4   ;   O   l   d   C   o   l   m   5   ;   O   l   d
0000060   C   o   l   m   6   ;   N   e   w   C   o   l   m  \n   V   a
0000100   l   u   e   1   ;   V   a   l   u   e   2   ;   V   a   l   u
0000120   e   3   ;   V   a   l   u   e   4   ;   V   a   l   u   e   5
0000140   ;   V   a   l   u   e   6   ;   N   U   L   L  \n
0000155

Note that using an RE as RS as well as the RT built-in variable are GNU awk (gawk) extensions and might not be supported by all awk implementations.

Collectives™ on Stack Overflow

Shell script CSV processing - adding new column with AWK

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related