# 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

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 = "";