Deleting all the fields(columns) from a .csv file if all rows in that columns are blanks

Shell Programming and Scripting
1

Hi Friends,

I have come across some files where some of the columns don not have data.

Key, Data1,Data2,Data3,Data4,Data5 
A,5,6,,10,,
A,3,4,,3,,
B,1,,4,5,,
B,2,,3,4,,

If we see the above data on Data5 column do not have any row got filled. So remove only that column(Here Data5) and leave all the other column data as it is even tough there some cells with empty values.
In actual I have 100's of columns in my data with its own column filled in as per column1(Key).

Thanks
Sidda

2

Try sth like this.

 
$awk -F "," 'NR==FNR{for(i=1;i<=NF;i++){if($i == ""){X++;}};x=NR;next}{for(i=1;i<=NF;i++){if(X != (x-1)){s=s?s","$i:$i}}print s;s=""}' file file
 
Key, Data1,Data2,Data3,Data4
A,5,6,,10
A,3,4,,3
B,1,,4,5
B,2,,3,4
3

Hi Pamu,

Thank you so much for your reply.
But I am getting syntax error when I run the code in cygwin's awk.
I tried to play around look for { }'s combination, but still the same error.
Can you look in to it once more.

Sidda

4

I am sorry.

I've corrected in my previous post. Please check now.

5

You have a data inconsistency in your sample file: the header line has six fields, while the data rows have seven. I had to eliminate that in order to have following work. Try

awk '    {for (i=1; i<=NF; i++)                            # for every field in every line
           {LG = LG * ($i=="") + (NR==1)             # test if $i is empty, except for line 1
            Ar[NR, i] = $i                                 # save field by line and fld No.
            }
         }
     END {for (j=1; j<=NR; j++)                            # go through all lines of entire file
           {for (i=1; i<=NF; i++)                          # with all saved fields
              if (!LG) rec = rec (rec?OFS:z) Ar[j,i]    # compose output record unless field was empty in all lines
            printf "%s\n", rec; rec = ""                   # print it
           }
         }
    ' FS=, OFS=, file
Key, Data1,Data2,Data3,Data4
A,5,6,,10
A,3,4,,3
B,1,,4,5
B,2,,3,4