Selective extraction of data

Question

I have the following block of data in a txt file :

Informatica(r) PMCMD, version [9.5.1 HotFix4], build [313.0217], SOLARIS 64-bit 
Copyright (c) Informatica Corporation 1994 - 2014   
All Rights Reserved. 

Invoked at Wed Dec 30 11:13:42 2015 

Connected to Integration Service: [TEST_Integration_Service].   
Integration Service status: [Running]   
Integration Service startup time: [Sun Dec 27 06:37:32 2015]  
Integration Service current time: [Wed Dec 30 11:13:42 2015]  
Folder: [ALS_DIM]  
Workflow: [wf_ld_als_dim] version [1].  
Workflow run status: [Scheduled]  
Workflow run error code: [0]  
Workflow run id [0].  
Schedule time: [Wed Dec 30 19:00:00 2015]  
Workflow run type: [Schedule]  
Run workflow as user: [Administrator]  
Run workflow with Impersonated OSProfile in domain: []  
Integration Service: [TEST_Integration_Service]  
Folder: [ALS_FACT]  
Workflow: [wf_s_m_ld_interchanges_detail_log] version [1].  
Workflow run status: [Scheduled]  
Workflow run error code: [0]  
Workflow run id [0].  
Schedule time: [Mon Jan 04 16:30:00 2016]  
Workflow run type: [Schedule]  
Run workflow as user: [Administrator]  
Run workflow with Impersonated OSProfile in domain: []  
Integration Service: [TEST_Integration_Service]  
Folder: [ALS_PRD]  
Workflow: [wf_maint_service_fields] version [1].  
Workflow run status: [Scheduled]  
Workflow run error code: [0]  
Workflow run id [0].  
Schedule time: [Thu Dec 31 07:10:00 2015]  
Workflow run type: [Schedule]  
Run workflow as user: [Administrator]  
Run workflow with Impersonated OSProfile in domain: []  
Integration Service: [TEST_Integration_Service]  
Number of scheduled workflows on this Integration Service: [3]  
Disconnecting from Integration Service  

I need to extract data into another text file in such a manner that only the folder name, workflow name, workflow run status, scheduled time and integration service names are extracted—in that order—for every recurrence.

For example:

Insert into <tablename> values('ALS_DIM', 'wf_ld_als_dim', 'Scheduled', 'Wed Dec 30 19:00:00 2015', 'TEST_Integration_Service')

This should be extracted for the first set and so on.

I had developed a script specific to the given 3 sets of data, but it should be such that the script may run for any number of data sets.

I have very preliminary knowledge about shell scripting so it would be great to get some help regarding this.

Answer 1

A sed solution...

sed -ne'/^Folder: *\[/!{'                                     \
         -e'/^Workflow\( run status\)\{0,1\}: *\[/!{'         \
              -e'/^Schedule time: *\[/!{'                     \
                   -e'/^Integration Service: *\[/!d'          \
    -e\} -e\} -e\} -e"s//'/"      -e"s/\].*/'/"  -e'H;x'      \
                   -e'/ .*\n.*/h' -e's///'       -e'x'        \ 
                   -e's//Insert into <tablename> values(&)/'  \
                   -e's/\n//'     -e's//, /gp'

---

Insert into <tablename> values('ALS_DIM', 'wf_ld_als_dim', 'Scheduled', 'Wed Dec 30 19:00:00 2015', 'TEST_Integration_Service')
Insert into <tablename> values('ALS_FACT', 'wf_s_m_ld_interchanges_detail_log', 'Scheduled', 'Mon Jan 04 16:30:00 2016', 'TEST_Integration_Service')
Insert into <tablename> values('ALS_PRD', 'wf_maint_service_fields', 'Scheduled', 'Thu Dec 31 07:10:00 2015', 'TEST_Integration_Service')

So the first lines negate acceptables matches like...

if ! match ^Folder: *\[
then  if ! match ^Workflow: *\[ or ^Workflow run status: *\[
      then if !  match ^Schedule time: *\[
           then  if !  match ^Integration Service: *\[
                 then  delete
                 fi
           fi
      fi
fi

As soon as a line does match any in the chain, though, the chain is broken and is not traversed to the end. This means that the last regular expression tested for any given matching line will describe the head of the line up to the right square bracket. In a sed script you can refer again to the most recently compiled regular expression with the // empty address. And I just s//'/ubstitute it away and replace it with the leading ' quote in your desired output.

What remains is all of the information you want only plus trailing context following the first ] on each line. And so I s/\].*/'/ubstitute for your trailing ' quote the unwanted tail of each line as well.

At this point all lines have been stripped to only the parts you want, but they have not yet been joined. To effect this I use the Hold space which persists the line cycle. And so append a copy of each line to Hold space, exchange the hold and pattern buffers, and look for / .*\n.*/ for a <space> followed by a <newline> - which happens only on the next held line after a date line.

If the pattern is found I overwrite hold space with it and s///ubstitute away all of pattern space (to leave it blank for the next iteration because this is the last line for this one). The s/// empty address here only removes the contents of a line which matched the <space> + <newline> pattern - and so only on the last line of each iteration can either of these instructions succeed.

Regardless, I afterward exchange the hold and pattern buffers for the last time, and so the pattern buffer will either now contain all of this iteration's matches delimited by <newline> characters, or it will contain only the most recent match and zero <newline> characters. The hold buffer at this point is either empty if this is the last matching line for this iteration, or else it contains all matching lines up to this point, each of them prefixed with a leading <newline>.

Next I refer again to the same regular expression and attempt to substitute all of a pattern space which matches a <space> + <newline> pattern for itself wrapped in Insert into <tablename> values( and a trailing ).

And last, if there are any, I s///ubstitute for nothing the leading <newline> in the last matching line's pattern space, and all remaining <newlines> for a comma then a <space> each. If that s///ubstitution is successful, its results are printed to stdout.

Answer 2

A Perl approach:

$ perl -lne 'if(/^(Folder|Workflow|Workflow.*?status|Sched.*time|Integration Service):.*?\[([^][]+)/){++$k%5==0 ? print "$2"  : printf "%s,",$2}' file
ALS_DIM, wf_ld_als_dim, Scheduled, Wed Dec 30 19:00:00 2015, TEST_Integration_Service
ALS_FACT, wf_s_m_ld_interchanges_detail_log, Scheduled, Mon Jan 04 16:30:00 2016, TEST_Integration_Service
ALS_PRD, wf_maint_service_fields, Scheduled, Thu Dec 31 07:10:00 2015, TEST_Integration_Service

Or, less condensed:

$ perl -lne '
 if(/^                       ## Match the beginning of the line
     (                       ## 1st capturing group: $1
      Folder               | ## The various things we want to match
      Workflow             | 
      Workflow.*?status    |
      Sched.*time          |
      Integration\s*Service
      ):                     ## Only if they are followed by a :
      .*?\[
      (                      ## 2nd caprturing group: $2.
        [^][]+               ## The longest string of non-] or [
      )/x                    ## The x allows writing multiline regexes          
    )
{                            ## If this line matches...
    $k=$k+1;                   ## Increment the counter $k by one
    if($k%5==0){               ## If the current value of $k is a multiple of 5.
      print "$2"               ## Print the 2nd captured group and a newline.
    }                          ## The newline is automatically added by the -l. 

    else{
      printf "%s,",$2         ## For other lines, just print with no newline.
    }
}' file
ALS_DIM, wf_ld_als_dim, Scheduled, Wed Dec 30 19:00:00 2015, TEST_Integration_Service
ALS_FACT, wf_s_m_ld_interchanges_detail_log, Scheduled, Mon Jan 04 16:30:00 2016, TEST_Integration_Service
ALS_PRD, wf_maint_service_fields, Scheduled, Thu Dec 31 07:10:00 2015, TEST_Integration_Service

To add the Insert ..., you can just pass it through a simple sed:

$ perl -lne 'if(/^(Folder|Workflow|Workflow.*?status|Sched.*time|Integration Service):.*?\[([^][]+)/){++$k%5==0 ? print "$2"  : printf "%s,",$2}' file | 
    sed "s/^/Insert into <tablename> values('/; s/,/','/g; s/$/')/"
Insert into <tablename> values("ALS_DIM","wf_ld_als_dim","Scheduled","Wed Dec 30 19:00:00 2015","TEST_Integration_Service")
Insert into <tablename> values("ALS_FACT","wf_s_m_ld_interchanges_detail_log","Scheduled","Mon Jan 04 16:30:00 2016","TEST_Integration_Service")
Insert into <tablename> values("ALS_PRD","wf_maint_service_fields","Scheduled","Thu Dec 31 07:10:00 2015","TEST_Integration_Service")

The sed runs three substitution operators:

• s/^/Insert into <tablename> values("/ : ^ is the beginning of the line. So, s/^/foo/ simply inserts foo at the beginning of the line. Here, it is inserting nsert into <tablename> values(".
• s/,/','/g : replace all (s///g) commas with ','.
• s/$/")/' : $ is the end of the line, so this will add )" at the end.