2

I have a text file with the following type of content:

OPERATION_CONTEXT VMD1HTE1A71_ns:.oc.GJ_OAD2 alarm_object 1130 On director: VMD1HTE1A71_ns:.temip.VMD1HTE1A71_director AT Fri 18 Oct 2013 06:56:39 All Attributes

                         Identifier = 1130
                              State = Terminated
                     Problem Status = Closed
              Clearance Report Flag = True
                    Escalated Alarm = False
              Close User Identifier = "Auto-Clear"
        Termination User Identifier = "Auto-Clear"
                   Close Time Stamp = Fri 18 Oct 2013 05:01:46
             Termination Time Stamp = Fri 18 Oct 2013 05:01:46
                 Creation Timestamp = Fri 18 Oct 2013 04:37:29
               Clearance Time Stamp = Fri 18 Oct 2013 05:01:40
        Last Modification Timestamp = Fri 18 Oct 2013 05:01:46
                     Previous State = Outstanding
                     Managed Object = Alcatel_5529OAD VMD1HTE1A71_ns:.OAD2 MD "AMS" Node "INGJJMGRJMTSNB0001AG2OLT001"
                    Target Entities = { Alcatel_5529OAD VMD1HTE1A71_ns:.OAD2 MD "AMS" Node "INGJJMGRJMTSNB0001AG2OLT001" }
                         Alarm Type = CommunicationsAlarm
                         Event Time = Fri 18 Oct 2013 05:01:40
                     Probable Cause = Unknown
                  Specific Problems = {  }
            Notification Identifier = 160315
                             Domain = Domain VMD1HTE1A71_ns:.dm.GJ_OAD2
                       Alarm Origin = IncomingAlarm
                 Perceived Severity = Major
                    Additional Text = "
                                                                  nativeProbableCause: Attempt Threshold Crossed
                                                                  osTime: 20131018163727.250+0530
                                                                  neTime: 20131011174021.0+0530
                                                                  notificationId: AMS:160315
                                                                  portNumber:
                                                                  ftpNumber:
                                                                  meNm: INGJJMGRJMTSNB0001AG2OLT001
                                                                  mdNm: AMS
                                                                  objectType: OT_MANAGED_ELEMENT
                                                                  aliasValue: MGMT Security
                                      Access:INGJJMGRJMTSNB0001AG2OLT001:IP10.70.6.6.T0.S841 "
                  Original Severity = Major
                Original Event Time = Fri 11 Oct 2013 05:40:21

I want to create a CSV file from this text file with Column headers as the values before = in each line like Identifier, State, Problem Status and all the next lines containing the values under each Column header like 1130, Terminated, Closed etc. Apart from the lines with "=" i don't want anything else to be extracted into the CSV file.

Another complication i faced in this is, some fields have a new line character like Additional Text. I want to get all the value for Additional Text in one column under Additional Text Column.

Since i am new to Linux/Unix, i am not able to find a way to do this. What is the best way to do this?

Improve this question
6
  • Perl Text::CSV might help. Commented Oct 18, 2013 at 14:00
  • Python csv module can also be used. docs.python.org/2/library/csv.html Commented Oct 18, 2013 at 14:14
  • Do you care if the columns stay in a particular order? Commented Oct 18, 2013 at 19:55
  • @slm: No, it doen't matter whatever the order is. Commented Oct 19, 2013 at 11:24
  • What are the lines above the data? Is that part of the data? Is that the record header or separator? Can you add an example with at least two full records? Commented Apr 15, 2014 at 2:05

3 Answers 3

Reset to default
1

Well, if you always have the same number of fields per record and you don't have anything between records (assumptions I'm making based on your post which may or may not be correct), you could go the awk route. This will preserve column order and embedded newlines. Assume the following is in parse.awk:

BEGIN {
    RS       = "( = |\n\\s+)";
    isHeader = 0;
    Sep      = "\",\"";
    Q        = "\"";
    # WinEOL   = "\r"; # enable this if your CSV will be used on Windows
    Headers  = Fields = Q;
}

function sanitise (Entry) {
    gsub(/(^[ "]*|[" \n]*$)/, "", Entry); # Trim leading/trailing double quotes and white space
    gsub(/"/, "\"\"", Entry); # Escape double quotes
    return Entry;
}

function addField (Field) {
    Fields    = Fields FieldsSep sanitise(Field);
    isHeader  = 1;
    FieldsSep = Sep;
    FieldCounter++
}

function addHeader (Header) {
    Headers = Headers HeadersSep sanitise($0);
    isHeader = 0;
    HeadersSep = Sep;
}

1 == NR {                   # Special case of first header
    addHeader($1);
    next;
}

$0 == "\"" {                # Fields with newlines
    LongField    = $0;
    LongFieldSep = "";
    while (getline > 0) {
        LongField    = LongField LongFieldSep $0;
        LongFieldSep = "\n";
        if ($NF ~ /"$/) {
            addField(LongField);
            next;
        }
    }
}
{
    if (isHeader) {
        addHeader($0);
    }
    else {
        addField($0);
    }

    if (FieldsPerRecord == FieldCounter) {
        if (!HeadersPrinted) {
            print Headers Q WinEOL;
            HeadersPrinted = 1
        }
        print Fields Q WinEOL;
        Fields = FieldsSep = "";
        FieldCounter = 0
    }
}

Then you could just invoke it with FieldsPerRecord set on the command line:

$ awk -v FieldsPerRecord=26 -f parse.awk data.csv

Which will produce the following CSV-encoded data which LibreOffice Calc seems to accept without problems:

"Identifier","State","Problem Status","Clearance Report Flag","Escalated Alarm","Close User Identifier","Termination User Identifier","Close Time Stamp","Termination Time Stamp","Creation Timestamp","Clearance Time Stamp","Last Modification Timestamp","Previous State","Managed Object","Target Entities","Alarm Type","Event Time","Probable Cause","Specific Problems","Notification Identifier","Domain","Alarm Origin","Perceived Severity","Additional Text","Original Severity","Original Event Time"
"1130","Terminated","Closed","True","False","Auto-Clear","Auto-Clear","Fri 18 Oct 2013 05:01:46","Fri 18 Oct 2013 05:01:46","Fri 18 Oct 2013 04:37:29","Fri 18 Oct 2013 05:01:40","Fri 18 Oct 2013 05:01:46","Outstanding","Alcatel_5529OAD VMD1HTE1A71_ns:.OAD2 MD ""AMS"" Node ""INGJJMGRJMTSNB0001AG2OLT001","{ Alcatel_5529OAD VMD1HTE1A71_ns:.OAD2 MD ""AMS"" Node ""INGJJMGRJMTSNB0001AG2OLT001"" }","CommunicationsAlarm","Fri 18 Oct 2013 05:01:40","Unknown","{  }","160315","Domain VMD1HTE1A71_ns:.dm.GJ_OAD2","IncomingAlarm","Major","nativeProbableCause: Attempt Threshold Crossed
osTime: 20131018163727.250+0530
neTime: 20131011174021.0+0530
notificationId: AMS:160315
portNumber:
ftpNumber:
meNm: INGJJMGRJMTSNB0001AG2OLT001
mdNm: AMS
objectType: OT_MANAGED_ELEMENT
aliasValue: MGMT Security
Access:INGJJMGRJMTSNB0001AG2OLT001:IP10.70.6.6.T0.S841","Major","Fri 11 Oct 2013 05:40:21"

Note that I take the quote everything approach which, for me at least, produces fewer surprises when importing, but you can disable this by setting Q = "", Sep = "," and the two gsub() lines in sanitise().

however, I don't think this is a regex problem. These data are fixed-width, so it seems that Perl's unpack is probably the best approach to take. I've never been able to get my head around it, but this might be a good opportunity for me to learn if someone wants to show a way to do it with unpack.

update

I'm not a Perl Hacker™, but the following seems to work well, makes no assumptions about the content of multi-line fields, retains field order and all original spacing within fields (but removes leading space from headers), and appears perl-esqueish to my untrained eyes:

BEGIN{
    our (@headers, @fields);
    our $headers_printed = 0;
}
my ($header, $field) = unpack("A36x2A*", $_); # magic!

if ("" eq $header) {            # Fields with newlines
    $fields[$#fields] .= "\n" . $field;
    next;
}

push(@headers, $header =~ s/^\s*//gr);
push(@fields, $field);

if (26 == $#headers + 1) {      # Print complete record
    printf "%s\n", join ",", @headers  unless $headers_printed;
    $headers_printed = 1;
    printf "%s\n", join ",", @fields;
    @fields = @headers = ();
}

Just invoke with:

$ perl -nf /tmp/parse.pl /tmp/data.txt
Identifier,State,Problem Status,Clearance Report Flag,Escalated Alarm,Close User Identifier,Termination User Identifier,Close Time Stamp,Termination Time Stamp,Creation Timestamp,Clearance Time Stamp,Last Modification Timestamp,Previous State,Managed Object,Target Entities,Alarm Type,Event Time,Probable Cause,Specific Problems,Notification Identifier,Domain,Alarm Origin,Perceived Severity,Additional Text,Original Severity,Original Event Time
1130,Terminated,Closed,True,False,"Auto-Clear","Auto-Clear",Fri 18 Oct 2013 05:01:46,Fri 18 Oct 2013 05:01:46,Fri 18 Oct 2013 04:37:29,Fri 18 Oct 2013 05:01:40,Fri 18 Oct 2013 05:01:46,Outstanding,Alcatel_5529OAD VMD1HTE1A71_ns:.OAD2 MD "AMS" Node "INGJJMGRJMTSNB0001AG2OLT001",{ Alcatel_5529OAD VMD1HTE1A71_ns:.OAD2 MD "AMS" Node "INGJJMGRJMTSNB0001AG2OLT001" },CommunicationsAlarm,Fri 18 Oct 2013 05:01:40,Unknown,{  },160315,Domain VMD1HTE1A71_ns:.dm.GJ_OAD2,IncomingAlarm,Major,"
                            nativeProbableCause: Attempt Threshold Crossed
                            osTime: 20131018163727.250+0530
                            neTime: 20131011174021.0+0530
                            notificationId: AMS:160315
                            portNumber:
                            ftpNumber:
                            meNm: INGJJMGRJMTSNB0001AG2OLT001
                            mdNm: AMS
                            objectType: OT_MANAGED_ELEMENT
                            aliasValue: MGMT Security
Access:INGJJMGRJMTSNB0001AG2OLT001:IP10.70.6.6.T0.S841 ",Major,Fri 11 Oct 2013 05:40:21

It would probably be better to use Text::CSV, but I was more interested in learning how unpack works. It seems to be much more readable and robust for fixed-width data than regular expressions.

Improve this answer
0

Or you can use Perl's regex subroutines:

my $grammar = qr!
    ( ?(DEFINE)
       (?<Identifier> [^=\n]+ )
       (?<Statement>
           (?: # Begin alternation
               " #Opening quotes
               [^"]+? # Any non-quotes (including a new line)
               " # Closing quotes
              | [^\n]+ # Or a single line
           )   # End alternation
        )   

   )

!x;

my $file = do { local $/; <> }; #Slurp file named on command line
my %columns;
while( $file =~ 
   m{ ((?&Identifier))[\t ]*=[ \t]*((?&Statement)) $grammar}xgc )
{ 
   my ($header,$value) = ($1,$2);

       # Remove leading spaces and quote variable if it contains commas:
   for($header,$value) { s/^\s+//mg; /,/ and s/^|$/"/g }

       # Substitute \n with \\n to make multi-line values single-line:
   for($value) { chomp; s/\n/\\n/g }

   $columns{$header}=$value
}

print join "," => sort keys %columns; # Print column headers
print "\n";
print join "," => map { $columns{$_} } sort keys %columns; # Column content
print "\n";

Invoke it like so:

[user@host]$ /path/to/script.pl /path/to/file.txt

It will print to standard output the CSV-formatted table

This assumes a multi-line statement will contain no double quotes (") except its opening and closing ones.

Improve this answer
8
  • You been working on this one too? Commented Oct 20, 2013 at 13:27
  • @slm You mean regex subroutines? I only recently learned about them. Pretty powerful stuff! Commented Oct 20, 2013 at 19:04
  • No, I just thought it was funny that you and I posted our answers to this Q at roughly the same time, and we both came up w/ Perl solutions 8-). Commented Oct 20, 2013 at 19:48
  • @slm I actually posted my answer after reading yours. You perl-ceptioned me :) Commented Oct 20, 2013 at 19:58
  • Too funny! I do that too sometimes where I see an example and re-imagine it in another language or technique just to see if I can do it. I'll have to try out regex subroutines too. Never used them before. Commented Oct 20, 2013 at 20:10
0

OK this isn't pretty but does as you ask. I wrote a script in Perl that will take the above file and parse it, and then uses the module Text::CSV to convert it to a CSV format.

The script

#!/usr/bin/env perl

use Text::CSV;

open(my $fh, "<data.txt");
@lines = <$fh>;
close ($fh);

my (%csv, $name, $val);

foreach my $line (@lines) {
  if ($line =~ m/=/) {
    chomp($line);
        $line =~ s/^\s+//g;
    ($name, $val) = split(/ = /, $line);
        $val =~ s/^"$//;
        $csv{$name} = $val;
  } else {
        $line =~ s/^\s+//g;
        $line =~ s/\s+$/\\n/g;
        $line =~ s/ "\\n$//;
        $csv{$name} .= $line;
  }
}

my @vals;
foreach my $i (sort keys %csv) {
  push(@vals, $csv{$i});
}

my $ccsv = Text::CSV->new();
$ccsv->combine(sort keys %csv);
$ccsv->parse($ccsv->string());
print $ccsv->string() . "\n";
$ccsv->combine(@vals);
$ccsv->parse($ccsv->string());
print $ccsv->string() . "\n";

Example

Simply run it like this:

$ ./csv.pl
"Additional Text","Alarm Origin","Alarm Type","Clearance Time Stamp","Close Time Stamp","Creation Timestamp",Domain,"Event Time","Last Modification Timestamp","Managed Object","Notification Identifier","Original Event Time","Original Severity","Perceived Severity","Previous State","Probable Cause","Specific Problems","Target Entities","Termination Time Stamp"
"nativeProbableCause: Attempt Threshold Crossed\nosTime: 20131018163727.250+0530\nneTime: 20131011174021.0+0530\nnotificationId: AMS:160315\nportNumber:\nftpNumber:\nmeNm: INGJJMGRJMTSNB0001AG2OLT001\nmdNm: AMS\nobjectType: OT_MANAGED_ELEMENT\naliasValue: MGMT Security\nAccess:INGJJMGRJMTSNB0001AG2OLT001:IP10.70.6.6.T0.S841",IncomingAlarm,CommunicationsAlarm,"Fri 18 Oct 2013 05:01:40","Fri 18 Oct 2013 05:01:46","Fri 18 Oct 2013 04:37:29","Domain VMD1HTE1A71_ns:.dm.GJ_OAD2","Fri 18 Oct 2013 05:01:40","Fri 18 Oct 2013 05:01:46","Alcatel_5529OAD VMD1HTE1A71_ns:.OAD2 MD ""AMS"" Node ""INGJJMGRJMTSNB0001AG2OLT001""",160315,"Fri 11 Oct 2013 05:40:21",Major,Major,Outstanding,Unknown,"{  }","{ Alcatel_5529OAD VMD1HTE1A71_ns:.OAD2 MD ""AMS"" Node ""INGJJMGRJMTSNB0001AG2OLT001"" }","Fri 18 Oct 2013 05:01:46"

Let me know what you think, or if you have issues running it. If it does what you need I'll fill in the details of how it works.

References

Improve this answer

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.