Add empty columns at the end of a CSV file

Linux and Unix-Like Linux
1

I have a .CSV file (lets say named as file.csv) with numeric and string values. The string might contain commas hence they are enclosed in double quotes as in the below format.

column1,column2,column3,column4,column5,column6,column7
12,455,"string, with, quotes, and with, commas, in between",4432,6787,890,88
4432,6787,"another, string, with, quotes, and, with, multiple, commaz, in between",890,88,12,455
11,22,"simple, string",77,777,333,22

When I am trying to add empty columns at the end of the file, using the below code

awk -F, '{NF=13}1' OFS="," file.csv > temp_file.csv

the output is not as per my requirement. The code is also counting the commas in the text qualifier fields too whihc are enclosed in double quotes. The output of the file

cat temp_file.csv

using the above command is as below:

column1,column2,column3,column4,column5,column6,column7,,,,,,
12,455,"string, with, quotes, and with, commas, in between",4432,6787,890,88,
4432,6787,"another, string, with, quotes, and, with, multiple, commaz, in between",890,88
11,22,"simple, string",77,777,333,22,,,,,

Where as I need the total number of fields in the file to be 13. Any input on this issue using either `awk` or `sed` is greatly appreciated.

2

Something like this?

awk -F, '
        {
                for ( i = 1; i <= NF; i++ )
                {
                        if ( $i ~ /"/ && f == 1)
                                f = 0
                        else
                        {
                                if ( $i ~ /"/ && f == 0 )
                                        f = 1
                        }
                        if ( f == 0 )
                                ++c
                }
                printf "%s", $0
                if ( c != 13 )
                {
                        while ( ++j < (13-c) )
                                printf ","
                }
                c = f = j = 0
                printf RS
        }
' file.csv
1 Like
3

Thanks Yoda, but variables are not defined.
Could you please explain this a bit please.

Thanks,
R. Singh

4

Not as elegant as desired, but may be worth a shot:

 awk -F'"' '{gsub (/,/,"\001",$2)}1' OFS='"' file | awk -F, '{NF=13}1' OFS=","|awk '{gsub ("\001",",")}1'
column1,column2,column3,column4,column5,column6,column7,,,,,,
12,455,"string, with, quotes, and with, commas, in between",4432,6787,890,88,,,,,,
4432,6787,"another, string, with, quotes, and, with, multiple, commaz, in between",890,88,12,455,,,,,,
11,22,"simple, string",77,777,333,22,,,,,,

and, if there's more fields with double quotes, you need to run a loop on all even fields to gsub commas...