Need to convert the content of file into COLUMN (To export into excel)

Shell Programming and Scripting
1

I have multiple condition in file as below.

MONITOR "ALERT_INFO"
DESCRIPTION "Triggered when informational Netware alert occured"
MAXTHRESHOLD 95
SEVERITY Normal

MONITOR "ALERT_MAJOR"
DESCRIPTION "Triggered when major Netware alert occured"
MAXTHRESHOLD
SEVERITY Major

I need to convert them in format
MONITOR DESCRIPTION MAXTHRESHOLD SEVERITY

Please suggest

2

Here's a perl solution:

#!/usr/bin/perl

use strict;

my @a_rec;
my @a_out;
my $i;
my $line;
my $outline;
my $col1;
my $infile = "monitor.dat";
my $outfile = "monitor.csv";

open INFILE, "<$infile"
  or die "can't open file: $!";

open OUTFILE, ">$outfile"
  or die "can't open file: $!";

print OUTFILE "MONITOR,DESCRIPTION,MAXTHRESHOLD,SEVERITY\n";

@a_rec = (<INFILE>);

$i=0;
while (@a_rec[$i] )
{
   chomp($a_rec[$i]);

   while ( $a_rec[$i] )
   {
      $line = $a_rec[$i];
      $col1 = (split(/ /, $line)) [0];
      $line =~ s/$col1\s*//;
      push (@a_out, $line);
      $i++;
      chomp ($a_rec[$i]);
   }

   if ( $a_out[0] )
   {
      $outline = join(",", @a_out);
      print OUTFILE "$outline\n";
   }
   undef @a_out;
   $i++;
}

close(INFILE);

output:

cat monitor.csv
MONITOR,DESCRIPTION,MAXTHRESHOLD,SEVERITY
"ALERT_INFO","Triggered when informational Netware alert occured",95,Normal
"ALERT_MAJOR","Triggered when major Netware alert occured",,Major
3

Here is a nawk solution: -

nawk '
        BEGIN{ print "MONITOR,DESCRIPTION,MAXTHRESHOLD,SEVERITY" }
        (! NF){next}
        /^DESCRIPTION/{ol = ol substr($0,13)","}
        /^MONITOR|^MAXTHRESHOLD/{ol = ol $2","}
        /^SEVERITY/{ol = ol$2; print ol; ol = ""; next}
' infile > out.csv
4 
$ awk 'BEGIN {RS=""; FS="\n"; print "MONITOR,DESCRIPTION,MAXTHRESHOLD,SEVERITY" }
{split ($1,a,"\"");split($2,b,"\""); split ($3,c," "); split ($4,d," ")}
{print a[2],b[2],c[2],d[2]}' OFS="," input.txt
5

I got lazy yesterday and just printed the four column headers. I changed the code to to dynamically determine the column headers. This will allow you to add more rows to your monitor logs and they will be picked up automatically or to use the code against other files with the same format.

If I add rows to the monitor.dat file:
MONITOR "ALERT_INFO"
DESCRIPTION "Triggered when informational Netware alert occured"
MAXTHRESHOLD 95
SEVERITY Normal
NEW_COL NEW_VAL_1


MONITOR "ALERT_MAJOR"
DESCRIPTION "Triggered when major Netware alert occured"
MAXTHRESHOLD
SEVERITY Major
NEW_COL NEW_VAL_2

They get picked up automatically:

MONITOR,DESCRIPTION,MAXTHRESHOLD,SEVERITY,NEW_COL
"ALERT_INFO","Triggered when informational Netware alert occured",95,Normal,NEW_VAL_1
"ALERT_MAJOR","Triggered when major Netware alert occured",,Major,NEW_VAL_2

The new code:

#!/usr/bin/perl

use strict;

my @a_rec;
my @a_out;
my @a_hdr;
my $col_hdr = 0;
my $i;
my $line;
my $outline;
my $col1;
my $infile = "monitor.dat";
my $outfile = "monitor.csv";

open INFILE, "<$infile"
  or die "can't open file: $!";

open OUTFILE, ">$outfile"
  or die "can't open file: $!";

@a_rec = (<INFILE>);

$i=0;
while (@a_rec[$i] )
{
   chomp($a_rec[$i]);

   while ( $a_rec[$i] )
   {
      $line = $a_rec[$i];
      $col1 = (split(/ /, $line)) [0];
      unless ( $col_hdr )
      {
         push (@a_hdr, $col1);
      }
      $line =~ s/$col1\s*//;
      push (@a_out, $line);
      $i++;
      chomp ($a_rec[$i]);
   }

   unless ( $col_hdr )
   {
      $outline = join(",", @a_hdr);
      print OUTFILE "$outline\n";
      $col_hdr++;
   }

   if ( $a_out[0] )
   {
      $outline = join(",", @a_out);
      print OUTFILE "$outline\n";
   }
   undef @a_out;
   $i++;
}

close(INFILE);

close(OUTFILE);
6

My attempt in bash:

#!/bin/bash                                                                               

{
    printf "MONITOR DESCRIPTION MAXTHRESHOLD SEVERITY\n"
    while read key value
    do
        case $key in
            "MONITOR") monitor=$value ;;
            "DESCRIPTION") desc=$value ;;
            "MAXTHRESHOLD") max=$value ;;
            "SEVERITY") printf "$monitor $desc $max $value\n" ;;
        esac
    done < $1
} > $2

Usage: script INPUT OUTPUT

7 
while(<DATA>){
	chomp;
	if(/^$/){
		print "\n";
	}
	else{
		my @tmp=split(" ",$_,2);
		print $tmp[1]," ";
	}
}
__DATA__
MONITOR "ALERT_INFO"
DESCRIPTION "Triggered when informational Netware alert occured"
MAXTHRESHOLD 95
SEVERITY Normal


MONITOR "ALERT_MAJOR"
DESCRIPTION "Triggered when major Netware alert occured"
MAXTHRESHOLD
SEVERITY Major

---------- Post updated at 01:26 AM ---------- Previous update was at 12:23 AM ----------

while(<DATA>){
	chomp;
	if(/^$/){
		print "\n";
	}
	else{
		my @tmp=split(" ",$_,2);
		print $tmp[1]," ";
	}
}
__DATA__
MONITOR "ALERT_INFO"
DESCRIPTION "Triggered when informational Netware alert occured"
MAXTHRESHOLD 95
SEVERITY Normal


MONITOR "ALERT_MAJOR"
DESCRIPTION "Triggered when major Netware alert occured"
MAXTHRESHOLD
SEVERITY Major