how to concatenate columns with multiple rows using awk

Question

i have csv as below

col1,col2,col3,col4,col5

1,val1,57,val1,TRUE
,val2,,val2,    
,val3,,val3,    
,val4,,val4,    
,val5,,val5,    
2,val1,878,val1,FALSE
,val2,,val2,    
,val3,,val3,    
,val4,,val4,    
,val5,,val5,

i need to display the output using awk as below

col1,col2,col3,col4,col5

1,val1#val2#val3#val4#val5,57,val1#val2#val3#val4#val5,TRUE
2,val1#val2#val3#val4#val5,878,val1#val2#val3#val4#val5,FALSE

Answer 1

Keeping it simple, readable and portable (mostly a function of my not having that much awk experience, hehe):

BEGIN { FS=","; OFS="," }

NR < 3 { 
    print  # just echo header and separator lines
}

/^[0-9]/ { 
    if (NR > 3) {
        # concatenate all parts (note: csv because of OFS not the commas here)
        print part1,part2,part3
    }

    part1 = $1 OFS $2
    part2 = $3 OFS $4
    part3 = $5
}

/^,/ {
    part1 = part1 "#" $2
    part2 = part2 "#" $4
}

END { print part1,part2,part3 }

Result:

col1,col2,col3,col4,col5

1,val1#val2#val3#val4#val5,57,val1#val2#val3#val4#val5,TRUE
2,val1#val2#val3#val4#val5,878,val1#val2#val3#val4#val5,FALSE

Answer 2

Complex awk solution for any number of fields:

awk 'function pr(a, len){    # print an integral(joined) line
         for (i=1; i<=len; i++) printf "%s%s",a[i],(i==len? ORS : ",") 
     }
     NR<3;{ f=($0~/^[0-9]+,/? 1:0) }  # `f` flag, points to a `basic` line(starting with digit)
     NR>2{ 
         if (f && a[1]){ pr(a, len) } 
         len=(f? split($0,a,",") : split($0,b,","));  # split into different arrays
         if (!f) {                                    # encountering subsequent line
             for(i=2; i<=len; i++) 
                 if(b[i]!="") a[i]=a[i]"#"b[i]        # append subsequent values to `basic` line
         } 
     }END{ pr(a, len) }' file

The output:

col1,col2,col3,col4,col5

1,val1#val2#val3#val4#val5,57,val1#val2#val3#val4#val5,TRUE
2,val1#val2#val3#val4#val5,878,val1#val2#val3#val4#val5,FALSE