Processing a text file with awk to conform to CSV flat file format

Question

I collected data on reaction time from over 100 participants for an experiment I am running. Unfortunately, the separators between fields were not consistent, but after a lot of heartache with sed, I have managed to solve this problem.

The experiment was divided into blocks (5 for each participant) and what I need is each block to be outputted on its own line, separated by commas.

Here is a sample of my datafile:

Participant: 2456, Test: Optimism IAT. Format is stimulus ,  correct(1)/incorrect(0) ,  time(ms).  Writes 10 trials per line.
17/01/2011, 12:46:03 ,
Block 1: , Theirs   , 1        , 1921     , Myself   , 1        , 928      , Them     , 1        , 716      , Theirs   , 1        , 720      , Myself   , 1        , 533      , Me       , 1        , 596      , Themselves , 1        , 527      , Myself   , 1        , 656      , Mine     , 1        , 551      , Myself   , 1        , 624
     , Themselves , 1        , 570      , Me       , 1        , 514
     ,Block 1 Time,: 8856    ,
Block 2: , Failing  , 1        , 1835     , Happy    , 1        , 1118     , Sad      , 1        , 673      , Succeeding , 1        , 690      , Improving , 1        , 795      , Succeeding , 1        , 602      , Worse    , 1        , 586      , Succeeding , 1        , 553      , Improving , 1        , 619      , Disimproving , 1        , 659
     , Succeeding , 1        , 596      , Failing  , 1        , 539
     ,Block 2 Time,: 9265    ,
Block 3: , Succeeding , 1        , 2881     , Disimproving , 1        , 1072     , Mine     , 1        , 1120     , Me       , 1        , 627      , Happy    , 1        , 669      , Theirs   , 1        , 1539     , Worse    , 1        , 841      , Me       , 1        , 862      , Sad      , 1        , 1370     , Succeeding , 1        , 1115
    , Worse    , 1        , 855      , Theirs   , 1        , 792      , Them     , 1        , 627      , Better   , 1        , 735      , Me       , 1        , 626      , Happy    , 1        , 622      , Succeeding , 1        , 616      , Mine     , 1        , 646      , Them     , 1        , 599      , Disimproving , 1        , 607
     , Better   , 1        , 799      , Myself   , 1        , 1408     , Me       , 1        , 463      , Better   , 1        , 839      , Failing  , 1        , 602      , Mine     , 1        , 633      , Better   , 1        , 525      , Sad      , 1        , 573      , Worse    , 1        , 770      , Me       , 1        , 508
     , Theirs   , 1        , 613      , Disimproving , 1        , 649      , Improving , 1        , 701      , Theirs   , 1        , 590      , Disimproving , 1        , 716      , Better   , 1        , 714
     ,Block 3 Time,: 29924   ,
Block 4: , Them     , 1        , 1659     , Myself   , 1        , 1036     , Themselves , 1        , 595      , Me       , 1        , 509      , Myself   , 1        , 648      , Themselves , 1        , 542      , Myself   , 1        , 536      , Mine     , 1        , 537      , Theirs   , 1        , 615      , Mine     , 1        , 520
     , Me       , 1        , 596      , Mine     , 1        , 471
     ,Block 4 Time,: 8264    ,
Block 5: , Mine     , 1        , 1527     , Myself   , 1        , 1235     , Disimproving , 0        , 2001     , Theirs   , 1        , 981      , Succeeding , 1        , 1994     , Happy    , 1        , 1454     , Failing  , 1        , 1941     , Theirs   , 1        , 1151     , Failing  , 0        , 1358     , Me       , 1        , 790
     , Failing  , 1        , 717      , Mine     , 1        , 585      , Myself   , 1        , 821      , Themselves , 1        , 793      , Disimproving , 1        , 965      , Succeeding , 1        , 727      , Worse    , 1        , 961      , Theirs   , 1        , 1259     , Mine     , 1        , 578      , Better   , 1        , 1112
    , Mine     , 1        , 1207     , Happy    , 1        , 843      , Worse    , 1        , 1064     , Failing  , 1        , 699      , Happy    , 1        , 700      , Myself   , 1        , 516      , Them     , 1        , 794      , Me       , 1        , 526      , Sad      , 1        , 1118     , Improving , 1        , 826
     , Mine     , 1        , 540      , Succeeding , 1        , 952      , Myself   , 1        , 536      , Themselves , 1        , 851      , Improving , 1        , 865      , Mine     , 1        , 582
     ,Block 5 Time,: 35569  

As you can see, each of the blocks take up multiple lines. I need them to take up one line in the following format

Participant Date Time   Block  Word1 Correct1 Time1.....Word36 Correct36  Time36
2456                    1      Happy   1      1200       sad    0         1500
.
.
.
1234                     5    sad       0     1100       happy   1       900

The issue is that blocks 3 &5 have 36 stimuli while blocks 1,2 &4 have 12. I need the participant, datetime and block time in each row also.

Here is the script that got the data into the form you see here, but it doesn't give me each block on one line only which is what i need.

BEGIN{
    FS="\\";
    RS="#";
    OFS=",";
    ORS="\n";
}{
    for(i=1;i<=NF;i++) {printf "%-10s", $i; printf ",";} 

}

I'm using gawk version 3.1.6 on Ubuntu 10.04.

Answer 1

If I understand correctly, your problem is coping with input where each record comes in multiple lines, and you don't detect the end of a record but rather the beginning of a new record: a new record begins whenever a line does not begin with a comma.

Here's some awk boilerplate you can use to pre-process the input into records.

function process (record) {
    RS = " *, *"; /*gawk allows RS to be a regexp; some implementations would require setting RS="," and manually trimming spaces*/
    $0 = record; /*automatically sets $1, $2, ..., and NF*/
    record = "";
    /*your code goes here*/
}
{ if (/^ *,/) {record = record $0} else {process(record); record=$0} }
END { if (record != "") {process(record)} }'