Extract values from a text file for a multiline pattern which has three fields

Question

How to extract values from a text file for a multiline pattern which has three fields "ID", "First name" and "Last name"?

Extract only the pattern where the "ID" field has 9 digits with two spaces in there and the "First name" field not equal to "XXXXX". Remove the two spaces in the "ID" field. Remove the "Gender" field in the "First name" line.

e.g. Input:

ID : 200 000 000
Address: Test
First name: XXXXX   Gender: M
Last name: YYYYY

ID : 123 456 789
Address: Anywhere 1
First name: Albert   Gender: M
Last name: Au

ID : YYY YYY YYY
ID : ZZZ ZZZ ZZZ

ID : 100 000 000
Address: Anywhere 2
First name: Priscilla   Gender: F
Last name: Chan

ID : QQQ ZZZ ZZZ
Address: Test 2
First name: XXXXX   Gender: M
Last name: ZZZZZ

Output:

ID : 123456789
First name: Albert
Last name: Au

ID : 100000000
First name: Priscilla
Last name: Chan

I modified αғsнιη's original command below to:

awk -v RS='' -F'\n|: *' '{ bkup=$0 } gsub("Address:", "", bkup) && gsub("Gender:", "", bkup) && gsub($4, "", bkup) && gsub(" ", "", $2)==2 && $2+0==$2 && $6 !~ /^XXXXX *Gender$/ { print bkup }' infile.txt

Output:

ID : 123 456 789

First name: Albert    M
Last name: Au
ID : 100 000 000

First name: Priscilla    F
Last name: Chan

I modified αғsнιη's improved command below to:

awk -v RS='' -F'\n|: *' '{ bkup=$0; id=$2; gsub(/\nAddress[^\n]*\n| *Gender *: *[MF]\n/, "\n", bkup) } gsub(" ", "", $2)==2 && $2+0==$2 && gsub(id, $2, bkup) && $6 !~ /^XXXXX *Gender$/ { print bkup }' infile.txt

Output as expected:

ID : 123456789
First name: Albert
Last name: Au
ID : 100000000
First name: Priscilla
Last name: Chan

How to get the expected output with sed?

If the input have more spaces and extra characters in the ID field, how to get the expected output? e.g.

Input:

ID      : 200 000 000   Status : 1
Address: Test
First name: XXXXX   Gender: M
Last name: YYYYY

ID      : 123 456 789   Status : 2
Address: Anywhere 1
First name: Albert   Gender: M
Last name: Au

ID      : YYY YYY YYY   Status : _
ID      : ZZZ ZZZ ZZZ   Status : _

ID      : 100 000 000   Status : 3   XYZ
Address: Anywhere 2
First name: Priscilla   Gender: F
Last name: Chan

ID      : QQQ ZZZ ZZZ   Status : 4
Address: Test 2
First name: XXXXX   Gender: M
Last name: ZZZZZ

I modified nezabudka's solution below to:

sed -nr '/ID : ([0-9]{3} ){2}[0-9]{3}/!b s/\s//3g :1;N;/Last name/!b1 /First name: XXXXX/!s/(Gender|Status|\nAddress)[^\n]*//gp ' infile2.txt

Output:

ID  :123456789
First name: Albert
Last name: Au
ID  :100000000
First name: Priscilla
Last name: Chan

Expected output - the ID field is modified to the same format as the First name field:

ID: 123456789
First name: Albert
Last name: Au
ID: 100000000
First name: Priscilla
Last name: Chan

Answer 1

OP resolved his question after giving him/her some hints, and now I'm posting complete command and showing that how that could be simplified as following (click here to see revision history) including adding how to verify length of ID value is 9digits (we only validate length of integer numbers):

awk -v RS= -F'\n|: *' '{ id=$2; gsub(/\nAddress[^\n]*\n| *Gender *: *[MF]\n/, "\n") }
    gsub(" ", "", id)==2 &&
    id+0==id && length(id)==9 &&
    $4 !="XXXXX" && sub($2, id) &&
    $0=(++n>1?ORS:"") $0
' infile

Output is:

ID : 123456789
First name: Albert
Last name: Au

ID : 100000000
First name: Priscilla
Last name: Chan

---

Here we are checking with gsub(" ", "", id)==2, if there was only two space characters in the value of ID key (by replacing them with empty string we will remove those spaces too as requested);

then we checks if that is number id+0==id (by adding 0 it forces awk to do string to integer conversation and then it checks if converted value is equal to itself or not);

next we are checking that First name has value other than XXXXX with $4 != "XXXXX";

and later we swap modified id value with $2 (spaces removed); if all these conditions were evaluated to true, that record will be printed.

Answer 2

Using GNU sed with extended regex option -E we can do as follows. The lines are appended to hold space until we meet an empty line or the last line. At which point we inspect the hold space and print after the validation checks pass.

sed -En '
  /^$/!{H;$!d;}
  g
  /\nID :( [0-9]{3}){3}\n/!bx
  /\nFirst name: X{5} /bx
  s/\nAddress:[^\n]*//
  s/(\nFirst name: [^\n]+)Gender:[^\n]*/\1/;ta
  :a
    s/(\nID : ([0-9]{3}){1,2}) /\1/
  ta
  p;:x;z;x
' file

ID : 123456789
First name: Albert   
Last name: Au

ID : 100000000
First name: Priscilla   
Last name: Chan

Answer 3

sed -nr '/ID : ([0-9]{3} ){2}[0-9]{3}/!b
 s/\s//3g
 :1;N;/Last name/!b1
 /First name: XXXXX/!s/(Gender|\nAddress)[^\n]*//gp
' file

ID : 123456789
First name: Albert   
Last name: Au
ID : 100000000
First name: Priscilla   
Last name: Chan

Answer 4

Using Miller

mlr --prepipe 'sed "s/Gender/\n&/"' --xtab --ps ':' --ops ': ' clean-whitespace \
  then filter '${ID} =~ "[0-9]{3} [0-9]{3} [0-9]{3}" && ${First name} != "XXXXX"' \
  then put '${ID} = gsub(${ID}," ","")' \
  then cut -x -f Address,Gender Input
ID: 123456789
First name: Albert
Last name: Au

ID: 100000000
First name: Priscilla
Last name: Chan

Note that this removes trailing whitespace from the ID field name; if that's not acceptable then either omit the clean-whitespace and use strip({First name}) in the filter, or restore the original name by chaining rename ID,"ID ".

Answer 5

$ perl -00ne 's/\s+Gender:.*//;
              print if s/ID : (\d{3}) (\d{3}) (\d{3})/ID : $1$2$3/ 
                       && ! m/First name: XXXXX\b/' input.txt  
ID : 123456789
Address: Anywhere 1
First name: Albert
Last name: Au

ID : 100000000
Address: Anywhere 2
First name: Priscilla
Last name: Chan

The -00 tells perl to read the input in paragraph mode - i.e. records are separated by one or more empty lines.