Multiple line pattern/ Data extraction

Question

I have the following header below in the about 100,000 files, I have already extracted each line separately and combined each record in excel, so my time crunch is over and i am now looking to for an expedient method of data extraction.

X-RSMF-Generator: RSMF Generator Sample Library

X-RSMF-Version: 1.0.0

X-RSMF-EventCount: 53

X-RSMF-BeginDate: 2022-09-20T04:33:11-04:00

X-RSMF-EndDate: 2022-09-20T16:47:56-04:00

X-RSMF-GroupID: GRP000000118

X-RSMF-SecondaryGroupID: GRP000000118_D_20220920

X-RSMF-ContainsDeleted: False

X-RSMF-Application: Native Messages

X-RSMF-Participants: Person One <5156242756> Person two, Person

three [email protected] <21243210277> Person four *** <345278652345>

MIME-Version: 1.0

Not all lines are present in all files and the last field can contain more than one line. MIME-Version: 1.0 - I think we can use MIME-Version: 1.0 as the stop. I also only need to data for each line entry. everything before the ": " (colon space) can be ignored as those are the field headings.

I started out using sed, thinking I could just concatenate each line and pipe to AWK. to make each column.

#!/bin/sh

shopt -s nullglob
FILES=/mnt/c/Temp/rsmf/*.rsmf

for f in $FILES

do
    #echo "Processing $f"
    sed -rn \
    -e '/^X-RSMF-BeginDate:/{
        s/X-RSMF-BeginDate: //
        s/T/ /
        s/-0[45]:00/ /
        s/X-RSMF-Application://
        h
        #p
        }' \
    -e '/^X-RSMF-EndDate:/{
        s/X-RSMF-EndDate: //
        s/T/ /
        s/-0[45]:00/ /
        H
        #p
        }' \
     -e '/^X-RSMF-GroupID:/{
        s/X-RSMF-GroupID: //
        H
        x
        s/\r\n//gp
        }' \
         $f
done

Results -

2022-10-05 12:54:27 2022-10-05 12:54:27 GRP000000001
2022-10-05 11:48:18 2022-10-05 11:48:18 GRP000000002

Before spending time on this, I wanted to seek recommendations on the best approach and practice for this particular project.

Thoughts??

Answer 1

With awk:

awk -F': ' 'BEGIN{ORS=" "}$1=="MIME-Version"{exit}{print $2}END{print "\n"}' file    

Answer 2

The following is pretty hacky and brute-force method and there's probably a better way of doing it (but I'd need to know more about your data and why you're even mentioning Excel - if the data was originally in a spreadsheet, there are perl modules for extracting data directly from Excel or Open/Libre Office etc), but it does work with the sample data you provided.

It can process any number of input files.

It has been written to use TAB (\t or Ctrl-I or ^I) as the output field separator, rather than a space because your field data can contain spaces.

#!/usr/bin/perl

while (<>) {
  chomp;
  s/^\s*|\s*$//g;  # strip any leading and trailing whitespace
  next if /^$/;    # ignore all blank lines

  # split input line into @F array
  # $F[0] will contain the field name and
  # $F[1] will contain the field data
  # The field separator is a quite-forgiving zero-or-more spaces followed by
  # a colon followed by one-or-more spaces. This should cope with most minor
  # variants caused by manual extraction from Excel. 
  my @F = split /\s*:\s+/;

  # print the data at end of each input record (file)
  if (/^MIME-Version/) {
    # add space-separated @participants array to end of @record array
    push @record, join(" ", @participants);

    # print @record array, tab-separated
    print join("\t", @record), "\n";

    # clear both arrays, ready for next input file
    @record=();
    @participants=();
    next;
  };

  # fix up the date format
  if (/^X-RSMF-(Begin|End)Date/) {
    $F[1] =~ s/T/ /;
    $F[1] =~ s/-0[45]:00$//;
  };

  if (/^X-RSMF-Participants/) {
    # participants need to be handled differently because this field can
    # be multi-line.  Store in a separate @participants array
    push @participants, $F[1];

  } elsif ($#F == 0) {
    # lines without a field name get added to @participants array
    push @participants, $_;

  } else {
    # all other fields get added to @record array
    push @record, $F[1];
  }
}

Save it to a file, e.g. rsmf2tab.pl, make it executable with chmod +x rsmf2tab.pl, and run it as, e.g.

./rsmf2tab.pl /mnt/c/Temp/rsmf/*.rsmf

or if your .rsmf files are in multiple subdirectories:

find /mnt/c/Temp/rsmf/ -name '*.rsmf' -exec /path/to/rsmf2tab.pl {} +

Sample output with two copies of you sample data (as file1.rsmf and file2.rsmf) as input, piped through cat -A to show the tabs as ^I:

$ ./rsmf2tab.pl *.rsmf | cat -A
RSMF Generator Sample Library^I1.0.0^I53^I2022-09-20 04:33:11^I2022-09-20 16:47:56^IGRP000000118^IGRP000000118_D_20220920^IFalse^INative Messages^IPerson One <5156242756> Person two, Person three [email protected] <21243210277> Person four <345278652345>$
RSMF Generator Sample Library^I1.0.0^I53^I2022-09-20 04:33:11^I2022-09-20 16:47:56^IGRP000000118^IGRP000000118_D_20220920^IFalse^INative Messages^IPerson One <5156242756> Person two, Person three [email protected] <21243210277> Person four <345278652345>$

---

BTW, you really do not want to do your FILE=/mnt/c/Temp/rsmf/*.rsmf followed by for f in $FILES. That will break if any files contain any whitespace characters. And it's not necessary, anyway - just run for f in /mnt/c/Temp/rsmf/*.rsmf or (depending on what you're running), just pass all filename args to the command you're running without using a loop.