awk sed cut? to rearrange random number of fields into 3 fields

Shell Programming and Scripting
1

I'm working on formatting some attendance data to meet a vendors requirements to upload to their system. With some help on the forums here, I have the data close. But they've since changed what they want.

The vendor wants me to submit three fields to them. Field 1 is the studentid field, field 2 is to be the building number and field 3 is a comma (and space char) delimited field holding periods.

This is what I have out of our records system now. Basically studentid, the periods, then the building number last. Each student is only listed once, so field 1 is unique in the input file 'absentees'.

250,1,2,3,45
255,2,4,6,8,10,45
1005,1,50
2099,1,2,51

I'd like to move the last field to the second position. Then enclose the remaining fields into one field with double quotes. Only if more than one period though (see third record for example of a record with only 1 period). So basically I need to get it looking like this (header row just fyi):

sid,bldg,periods
250,45,"1, 2, 3"
255,45,"2, 4, 6, 8, 10"
1005,50,1
2099,51,"1, 2"

I've used sed and cut to rearrange the order of fields when the number of fields are fixed. Not sure how to do it with a random number of fields though. Really not sure how to get the double quotes in there with comma and a space char.

I'm on hp-ux, so just awk is avail to me. (no gawk or nawk)

thanks in advance for any suggestions

2

Assuming the given file format:

awk -F, '{ if(NF>3){ $1=$1 FS $NF; $2=q $2; $NF=q; sub(/,"$/,q) }
            else { $0=$1 FS $3 FS $2 } } 1'  q='"'  OFS=, filename

_____

HP-UX's awk chokes for huge files ( if so, think of a different tool ).

3 
awk -v OFS=, -F, '{
  $1 = $1 "," $NF
  $NF = ""
  printf "%s,", $1
  $1 = ""
  if ( NF > 3 )
     printf "\"%s\"\n", substr($0,2,length-2)
  else
     printf "%s\n", substr($0,2,length-2)
}' "$FILE" |
 awk -F\" -v OFS=\" '{ gsub(",",", ",$2); print}'
4

thank you, its looking good.

5

With Perl:

perl -F, -lane'
print @F > 3
  ? join ",", $F[0], $F[-1], qw(") . ( join ", ", @F[ 1 .. @F - 2 ] ) . qw(")
  : $_
  ' infile