search for a key value pair and append the value to other keys in unix

Question

I need to search for a key and append the value to every key:value pair in a Unix file

Input file data:

1A:trans_ref_id|10:account_no|20:cust_name|30:trans_amt|40:addr
1A:trans_ref_id|10A:ccard_no|20:cust_name|30:trans_amt|40:addr

My desired Output:

account_no|1A:trans_ref_id
account_no|10:account_no
account_no|20:cust_name
account_no|30:trans_amt
account_no|40:addr
ccard_no|1A:trans_ref_id
ccard_no|10A:ccard_no
ccard_no|20:cust_name
ccard_no|30:trans_amt
ccard_no|40:addr

Basically, I need the value of 10 or 10A appended to every key:value pair and split into new lines. To be clear, this won't always be the second field.

I am new to sed, awk and perl. I started with extracting the value using awk:

awk -v FS="|" -v key="59" '$2 == key {print $2}' target.txt

Answer 1

# Looks for 10 or 10A
perl -F'\|' -lane'my ($id) = map /^10A?:(.*)/s, @F; print "$id|$_" for @F'

# Looks for 10 or 10<non-digit><maybe more>
perl -F'\|' -lane'my ($id) = map /^10(?:\D[^:]*)?:(.*)/s, @F; print "$id|$_" for @F'

• -n executes the program for each line of input.
• -l removes LF on read and adds it on print.
• -a splits the line on | (specified by -F) into @F.
• The first statement extracts what follows : in the field with id 10 or 10-plus-something.
• The second statement prints a line for each field.

Specifying file to process to Perl one-liner

Answer 2

If you are still stuck on where to get started, you will use a field-separator and output-field-separator (FS and OFS) set equal to '|' that will split each record into fields at each '|'. Your fields are available as $1, $2, ... $NF. You care about getting, e.g. account_no from field two ($2) so you split() field two with the separator ':' saving the split fields in an array (a used below). You want the second part from field two which will be in the 2nd array element a[2] to use as the new field-1 in output.

The rest is just looping over each field and outputting a[2] a separator and then the current field. You can do that with:

awk  'BEGIN{FS=OFS="|"} {split ($2,a,":"); for(i=1;i<=NF;i++) print a[2],$i}' file

Example Use/Output

With your example input in file, the result would be:

account_no|1A:trans_ref_id
account_no|10:account_no
account_no|20:cust_name
account_no|30:trans_amt
account_no|40:addr
ccard_no|1A:trans_ref_id
ccard_no|10A:ccard_no
ccard_no|20:cust_name
ccard_no|30:trans_amt
ccard_no|40:addr

Which appears to be what you are after. Let me know if you have further questions.

"10" or "10A" at Unknown Field

You can handle the fields containing "10" and "10A" in any order. You just add a loop to loop over the fields and determine which holds "10" or "10A" and save the 2nd element from the array resulting from split() from that field. The rest is the same, e.g.

awk  '
    BEGIN { FS=OFS="|" } 
    {   for (i=1;i<=NF;i++){ 
            split ($i,a,":")
            if (a[1]=="10"||a[1]=="10A"){ 
                key=a[2]
                break
            }
        }
        for (i=1;i<=NF;i++)
            print key, $i
    }
' file1

Example Input

1A:trans_ref_id|10:account_no|20:cust_name|30:trans_amt|40:addr
1A:trans_ref_id|20:cust_name|30:trans_amt|10A:ccard_no|40:addr

Example Use/Output

awk  '
>     BEGIN { FS=OFS="|" }
>     {   for (i=1;i<=NF;i++){
>             split ($i,a,":")
>             if (a[1]=="10"||a[1]=="10A"){
>                 key=a[2]
>                 break
>             }
>         }
>         for (i=1;i<=NF;i++)
>             print key, $i
>     }
> ' file1
account_no|1A:trans_ref_id
account_no|10:account_no
account_no|20:cust_name
account_no|30:trans_amt
account_no|40:addr
ccard_no|1A:trans_ref_id
ccard_no|20:cust_name
ccard_no|30:trans_amt
ccard_no|10A:ccard_no
ccard_no|40:addr

Which picks up the proper new field 1 for output from the 4th field containing "10A" for the second line above.

Let em know if this is what you needed.

Answer 3

I need the value of 10 or 10A appended to every key:value pair

Going by these requirements, you may try this awk:

awk '
BEGIN{FS=OFS="|"}
match($0, /\|10A?:[^|]+/) {
   s = substr($0, RSTART, RLENGTH)
   sub(/.*:/, "", s)
}
{
   for (i=1; i<=NF; ++i)
      print s, $i
}' file

account_no|1A:trans_ref_id
account_no|10:account_no
account_no|20:cust_name
account_no|30:trans_amt
account_no|40:addr
ccard_no|1A:trans_ref_id
ccard_no|10A:ccard_no
ccard_no|20:cust_name
ccard_no|30:trans_amt
ccard_no|40:addr

Answer 4

EDIT: To find 10 OR 10A values in anywhere in line and then print as per that try following then.

awk '
BEGIN{
  FS=OFS="|"
}
match($0,/(10|10A):[^|]*/){
  split(substr($0,RSTART,RLENGTH),arr,":")
}
{
  for(i=1;i<=NF;i++){
    print arr[2],$i
  }
}'  Input_file

Explanation: Adding detailed explanation for above.

awk '                        ##Starting awk program from here.
BEGIN{                       ##Starting BEGIN section of this program.
  FS=OFS="|"                 ##Setting FS and OFS to | here.
}
match($0,/(10|10A):[^|]*/){  ##using match function to match either 10: till | OR 10A: till | here.
  split(substr($0,RSTART,RLENGTH),arr,":") ##Splitting matched sub string into array arr with delmiter of : here.
}
{
  for(i=1;i<=NF;i++){        ##Running for loop for each field for each line.
    print arr[2],$i          ##Printing 2nd element of ar, along with current field.
  }
}'  Input_file               ##Mentioning Input_file name here.

---

---

With your shown samples, please try following.

awk '
BEGIN{
  FS=OFS="|"
}
{
  split($2,arr,":")
  print arr[2],$1
  for(i=2;i<=NF;i++){
    print arr[2],$i
  }
}
' Input_file

Answer 5

Perl script implementation

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

my $fname = shift || die "run as 'script.pl input_file key0 key1 ... key#'";

open my $fh, '<', $fname || die $!;

while( <$fh> ) {
    chomp;
    my %data = split(/[:\|]/, $_);
    for my $key (@ARGV) {
        if( $data{$key} ) {
            say "$data{$key}|$_" for split(/\|/,$_);
        }
    }
}

close $fh;

Run as script.pl input_file 10 10A

Output

account_no|1A:trans_ref_id
account_no|10:account_no
account_no|20:cust_name
account_no|30:trans_amt
account_no|40:addr
ccard_no|1A:trans_ref_id
ccard_no|10A:ccard_no
ccard_no|20:cust_name
ccard_no|30:trans_amt
ccard_no|40:addr

Answer 6

Here's an alternate perl solution:

perl -pe '($id) = /(?<![^|])10A?:([^|]+)/; s/([^|]+)[|\n]/$id|$1\n/g'

• ($id) = /(?<![^|])10A?:([^|]+)/ this will capture the string after 10: or 10A: and save in $id variable. First such match in the line will be captured.
• s/([^|]+)[|\n]/$id|$1\n/g every field is then prefixed with value in $id and | character