Extracting info from file rows into columns using whatever it works (PERL, SED, AWK)

Question

Maybe I´m too old for perl/awk/sed, too young to stop programming. Here is the problem I need to solve:

I have info like this in a TXT file:

Name:
Name 1
Phone:
1111111
Email:
some@email1
DoentMatterInfo1:
whatever1
=
Name:
Name 2
Phone:
22222222
DoentMatterInfo2:
whatever2
Email:
some@email2
=
Name:
Name 3
DoentMatterInfo3:
whatever2
Email:
some@email3
=

Please note that the desired info is in the next line, there is a record separator (=) and very important, some records doesn't have all the info, but could have info that we dont want.

So, the challenge is to extract the desired info, if exist, in an output like:

Name 1 ; 111111 ; some@email1
Name 2 ; 222222 ; some@email2
Name 3 ; ; some@email3

What I have tried that worked a little bit but stills is not what I´m looking for.

1. Using PERL

Using Perl I got the fields that matter:

while (<>) {

    if ($_ =~ /Name/) {
        print "=\n". scalar <>;

    }    
    if ($_ =~ /Email/) {
        print "; ". scalar <>;

    } 
    if ($_ =~ /Phone/) {
        print "; ". scalar <>;

    } 

}

The I got a file like:

Name 1
; 1111111
; some@email1
=
Name 2
; 22222222
; some@email2
=
Name:
Name 3
; some@email3
=

Now with sed I put each record in a single line:

SED With SED, this command replaces the Line Feed, got the info in a single line: sed ':a;N;$!ba;s/\n//g' input.txt > out1.txt

And out back the line feed:

sed 's/|=|/\n/g' out1.txt > out2.txt

So I got a file with the info in each line:

Name 1 ; 1111111 ; some@email1
Name 2 ; 22222222 ; some@email2
Name 3 ; some@email3

Still not what I would like to get from coding. I want something better, like being able to fill the missing phone with space, so the second column could be always the phone column. Do you get it?

AS you can see, the poitn is to find a solution, no matter if is using Perl, AWk or SED. I´m trying perl hashes...

Thanks in advance!!

Answer 1

Here is a Perl solution, asked for and attempted

use warnings;
use strict;
use feature 'say';

my @fields = qw(Name Phone Email);  # fields to process

my $re_fields = join '|', map { quotemeta } @fields;

my %record;

while (<>) { 
    if (/^\s*($re_fields):/) { 
        chomp($record{$1} = <>);
    }
    elsif (/^\s*=/) { 
        say join ';', map { $record{$_} // '' } @fields;
        %record = (); 
    }   
}

The input is prepared in the array @fields; this is the only place where those names are spelled out, so if more fields need be added to processing just add them here. A regex pattern for matching any one of these fields is also prepared, in $re_fields.

Then we read line by line all files submitted on the command line, using the <> operator.

The if condition captures an expected keyword if there. In the body we read the next line for its value and store it with the key being the captured keyword (need not know which one).

On a line starting with = the record is printed (correctly with the given sample file). I put nothing for missing fields (no spaces) and no extra spaces around ;. Adjust the output format as desired.

---

In order to collect records throughout and process further (or just print) later, add them to a suitable data structure instead of printing. What storage to choose depends on what kind of processing is envisioned. The simplest way to go is to add strings for each output record to an array

my (@records, %record);

while (<>) {
    ...
    elsif (/^\s*=/) { 
        push @records, join ';', map { $record{$_} // '' } @fields;
        %record = (); 
    }   
}

Now @records has ready strings for all records, which can be printed simply as

say for @records;

But if more involved processing may be needed then better store in an array copies of %record as hash references, so that individual components can later be manipulated more easily

my (@records, %record);

while (<>) {
    ...
    elsif (/^\s*=/) { 
        # Add a key to the hash for any fields that are missing
        $record{$_} //= ''  for @fields;
        push @records, { %record };
        %record = (); 
    }   
}

I add a key for possibly missing fields, so that the hashrefs have all expected keys, and I assign an empty string to it. Another option is to assign undef.

Now you can access individual fields in each record as

foreach my $rec (@records) { 
    foreach my $fld (sort keys %$rec) {
        say "$fld -> $rec->{$fld}"
    }
}

or of course just print the whole thing using Data::Dumper or such.

Answer 2

This will work using any awk in any shell on every UNIX box:

$ cat tst.awk
BEGIN { OFS=" ; " }
$0 == "=" {
    print f["Name:"], f["Phone:"], f["Email:"]
    delete f
    lineNr = 0
    next
}
++lineNr % 2 { tag = $0; next }
{ f[tag] = $0 }

.

$ awk -f tst.awk file
Name 1 ; 1111111 ; some@email1
Name 2 ; 22222222 ; some@email2
Name 3 ;  ; some@email3

Answer 3

I would do it like this:

$ cat prog.awk

#!/bin/awk -f
BEGIN                    { OFS = ";" }
/^(Name|Phone|Email):$/  { getline arr[$0] ; next } 
/^=$/  { print arr["Name:"], arr["Phone:"], arr["Email:"] ; delete arr }

Explanation:

In the BEGIN block, define the output field separator (semicolon).

For each line in the input file, if the line (in its entirety) equals Name: or Phone: or Email: then assign that string to the key and the value of the following line to the value of an element of the associative array arr. (That is how getline can be used to assign a value to a variable.) Then skip the next rule.

If the line is =, print the three values from the arr associative array, and then clear out the array (reset all the values to the empty string).

* * * *

Make it executable:

chmod +x prog.awk

Use it:

$ ./prog.awk file.txt 

Name 1;1111111;some@email1
Name 2;22222222;some@email2
Name 3;;some@email3

Note - a missing value is indicated by two consecutive semicolons (not by a space). Using space as placeholder for NULL is a common bad practice (especially in relational databases, but in flat files too). You can change this to use NULL as placeholder, I am not terribly interested in that bit of the problem.

Answer 4

Input file format is easy to parse: split on =\n into records, split each record on \n into a hash and push the hash into @result array.

Then just output each element of @result array with specifying fields of interest.

use strict;
use warnings;
use feature 'say';

use Data::Dumper;

my @result;
my $data    = do { local $/; <DATA> };
my @records = split('=\n?',$data);

push @result, {split "\n", $_} for @records;

say Dumper(\@result);

my @fields = qw/Name: Phone: Email:/;

for my $record (@result) {
    $record->{$_} = $record->{$_} || '' for @fields;
    say join('; ', @$record{@fields});
}

__DATA__
Name:
Name 1
Phone:
1111111
Email:
some@email1
DoentMatterInfo1:
whatever1
=
Name:
Name 2
Phone:
22222222
DoentMatterInfo2:
whatever2
Email:
some@email2
=
Name:
Name 3
DoentMatterInfo3:
whatever2
Email:
some@email3
=

Output

$VAR1 = [
          {
            'DoentMatterInfo1:' => 'whatever1',
            'Name:' => 'Name 1',
            'Email:' => 'some@email1',
            'Phone:' => '1111111'
          },
          {
            'Phone:' => '22222222',
            'Email:' => 'some@email2',
            'Name:' => 'Name 2',
            'DoentMatterInfo2:' => 'whatever2'
          },
          {
            'DoentMatterInfo3:' => 'whatever2',
            'Name:' => 'Name 3',
            'Email:' => 'some@email3'
          }
        ];

Name 1; 1111111; some@email1
Name 2; 22222222; some@email2
Name 3; ; some@email3