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 !

Improve this question
4
  • 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 Commented Feb 26, 2016 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. Commented Feb 26, 2016 at 15:14
  • yes @don_crissti Commented Feb 26, 2016 at 15:28
  • How are fields defined? Is it a tab-separated file? Commented Feb 26, 2016 at 18:30

2 Answers 2

Reset to default
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
%
Improve this answer
0
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] 

}
Improve this answer
4
  • 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/) ? Commented Feb 29, 2016 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 Commented Feb 29, 2016 at 13:57
  • Thanks a lot for your comment and your time, it helped me a lot :) Commented Feb 29, 2016 at 14:04
  • 1
    Always glad to help :) Commented Feb 29, 2016 at 14:08

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.