3

I have a file in the following format :

19-08-02  Name                         appel    ok    hope    local  merge   (mk)
                                                        juin    nov    sept    oct
00:00:t1  T1                            299       0      24      8      3     64
          F2                            119       0      11      8      3     62
          I1                             25       0       2      9      4     64
          F3                            105       0      10      7      3     61
          Regulated F2                    0       0       0
          FR T1                         104       0      10      7      3     61
00:00:t2  T1                            649       0      24      8      3     64
          F2                            119       0      11      8      3     62
          I1                            225       0       2      9      4     64
          F3                            165       0      10      7      3     61
          Regulated F2                    5       0       0
          FR T1                         102       0      10      7      3     61
20-08-02  Name                          appel    ok    hope    local  merge   (mk)
                                                        juin    nov    sept    oct
00:00:t5  T1                            800       0      24      8      3     64
          F2                            111       0      11      8      3     62
          I1                             250      0       2      9      4     64
          F3                            105       0      10      7      3     61
          Regulated F2                    0       0       0
          FR T1                         100       0      10      7      3     61

and I want to extract some data and write them in an other file CSV file in the following format :

            T1   F2     I1      F3    Regulated F2    FR T1
00:00:t1    299  119    25      105       0           104  
00:00:t2    649  119    225     165       5           102
00:00:t5    800  111    250     105       0           100
.......

I just need to extract the values in the third field appel every 00:00:XX I've tried to use awk but I didn't succeed to have the right script especially the 5th is composed of two words : Regulated F2. I don't know how to extract it as a single word .

Any help please !

  • if the columns are evenly spaced and length-wise fixed, you can try using cut -c 11-36 (assuming 2nd field is 25 characters, didn't count it) to extract the data. Likewise for the other fields – MelBurslan Feb 26 '16 at 15:01
  • @MelBurslan Thanks for your quick response :) .. I have tried cut -c it extracts fields eventhough there are white spaces but the problem that it's displayed vertically .. I want it to be written horizentally as the example above. – pietà Feb 26 '16 at 15:14
  • yes @don_crissti – pietà Feb 26 '16 at 15:28
  • How are fields defined? Is it a tab-separated file? – terdon Feb 26 '16 at 18:30
5

Using Perl:

perl -lane 'BEGIN{ print("\t\tT1\tF2\tI1\tF3\tRegulated F2\tFR T1"); $, = "\t" } if($F[0] =~ /00:00:t[0-9]+/){ @f[0] = $F[0]; @f[1] = $F[2]; for($i = 2; $i < 7; $i++) { $_ = <>; @F=split(); if($i < 5){ $f[$i] = $F[1] }else{ $f[$i] = $F[2] } } print(@f) }' file

Expanded script (to be made executable with chmod +x script.pl and to be run with ./script.pl file):

#!/usr/bin/perl -lan
BEGIN {
    print("\t\tT1\tF2\tI1\tF3\tRegulated F2\tFR T1");
    $, = "\t"
}
if($F[0] =~ /00:00:t[0-9]+/) {
    $f[0] = $F[0];
    $f[1] = $F[2];
    for($i = 2; $i < 7; $i++) {
        $_ = <>;
        @F=split();
        if($i < 5) {
            $f[$i] = $F[1]
        }
        else {
            $f[$i] = $F[2]
        }
    }
    print(@f) 
}

You can adjust the header by modifying print("\t\tT1\tF2\tI1\tF3\tRegulated F2\tFR T1"); and the output field separator by modifying $, = "\t".

% cat file
19-08-02  Name                         appel    ok    hope    local  merge   (mk)
                                                        juin    nov    sept    oct
00:00:t1  T1                            299       0      24      8      3     64
          F2                            119       0      11      8      3     62
          I1                             25       0       2      9      4     64
          F3                            105       0      10      7      3     61
          Regulated F2                    0       0       0
          FR T1                         104       0      10      7      3     61
00:00:t2  T1                            649       0      24      8      3     64
          F2                            119       0      11      8      3     62
          I1                            225       0       2      9      4     64
          F3                            165       0      10      7      3     61
          Regulated F2                    5       0       0
          FR T1                         102       0      10      7      3     61
20-08-02  Name                          appel    ok    hope    local  merge   (mk)
                                                        juin    nov    sept    oct
00:00:t5  T1                            800       0      24      8      3     64
          F2                            111       0      11      8      3     62
          I1                             250      0       2      9      4     64
          F3                            105       0      10      7      3     61
          Regulated F2                    0       0       0
          FR T1                         100       0      10      7      3     61
% perl -lane 'BEGIN{ print("\t\tT1\tF2\tI1\tF3\tRegulated F2\tFR T1"); $, = "\t" } if($F[0] =~ /00:00:t[0-9]+/){ @f[0] = $F[0]; @f[1] = $F[2]; for($i = 2; $i < 7; $i++) { $_ = <>; @F=split(); if($i < 5){ $f[$i] = $F[1] }else{ $f[$i] = $F[2] } } print(@f) }' file
        T1  F2  I1  F3  Regulated F2    FR T1
00:00:t1    299 119 25  105 0   104
00:00:t2    649 119 225 165 5   102
00:00:t5    800 111 250 105 0   100
% 
5

The task is fairly straightforward: ignore lines that start with date or lines that contain month columns ; if the first line contains time of the test, grab that time and the third column; for all the other lines - grab second column. The AWK script bellow does exactly that.

Demo:

$> ./data2cvs.awk  testData.txt                                                  
T1,F2,I1,F3,Regulated F2,FR T1
00:00:t1,299,119,25,105,0,104
00:00:t2,649,119,225,165,5,102
00:00:t5,800,111,250,105,0,100

Script source

#!/usr/bin/awk -f 

BEGIN {  
  HEADER="T1,F2,I1,F3,Regulated F2,FR T1"; print HEADER  
} 
# Ignore lines containing date and month
$1~/^[[:digit:]]{2}-.+/ || $0~/juin.*nov.*sept.*oct/ { 
     next ; 
}  
# Grab test time and first data value 
# Essentially doing something like sprintf in C
# to a string of arrays
$1~/^[[:digit:]]{2}:.+/{ 
     count++
     DATA[count]=$1","$3 
  }   
# grab remaining data values
$1 !~ /^[[:digit:]]{2}:.+/{

   if ($1~/Regulated/ || $1~/FR/){ 
      DATA[count]=DATA[count]","$3
   }
   else {
       DATA[count]=DATA[count]","$2 ;
   }
} 
# print gathered data to STDIN
END{ 
   for (i=1;i<=count;i++) print DATA[i] 

}
  • awsome response !! thanks a lot @Serg . Just a question If I want to add an other field like Regulated FR I make it like this? : if ($1~/Regulated/ || $1~/FR/ || $1~/Regulated/ || $1~/ ENG/) ? – pietà Feb 29 '16 at 13:32
  • 1
    Well, what the original line does is to test if column number $1 has words regulated or FR. If you want " Regulated ENG" line , it will be found already, just because it has the word regulated. But if you are looking for something like "ENG xx", then add if ($1~/ENG/) . It always depends on how your input text file looks like, so i cannot tell you how to program something i never seen – Sergiy Kolodyazhnyy Feb 29 '16 at 13:57
  • Thanks a lot for your comment and your time, it helped me a lot :) – pietà Feb 29 '16 at 14:04
  • 1
    Always glad to help :) – Sergiy Kolodyazhnyy Feb 29 '16 at 14:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.