# Need help processing a text file with awk to conform to CSV flat file format

I have the following problem. 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. Your help would be greatly appreciated.

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. - Is there some reason you don't want to use a general programming language like Python? The solution might be longer than awk, but would probably be easier to understand and modify. – Faheem Mitha Jun 17 '11 at 7:47 @Faheem Mitha - sunk costs mostly, also I'm not sure how long it would take me to learn enough python to do this task effectively can you recommend any good resources to get started? – richiemorrisroe Jun 17 '11 at 13:00 I looked at your data, but am unclear what you are trying to do. (I don't see a clear matchup between your data and the results you give, for example). If you are doing fairly complicated text processing, Python is a good choice. Specific recommendations are always subjective, but people say good things about Dive Into Python. I suggest you try writing your question more clearly and try StackOverflow. If you are willing to consider a Python approach, say so. There are a ton of Python programmers there. – Faheem Mitha Jun 18 '11 at 7:00 @Faheem Mitha - i actually started using python yesterday, and after reading the python.org tutorial and using the shell, i am about halfway through getting this (test) piece done. So thanks very much for the comment, which lead me to reassess my approach. – richiemorrisroe Jun 18 '11 at 10:21 ## 1 Answer 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)} }'

-
@Giles thank you, that was quite useful. However, i need to rethink my approach as my previous solution only gets me halfway there. I'll accept your answer and work away on it myself. –  richiemorrisroe Jun 17 '11 at 13:01