3

How can I get the details and transpose it to horizontal form?

Every record ends after Couse. Couse will never be blank or null.

Note: These four headers will be there for the below data: Name, City, Age, Couse

If you see the second record, there isn't any "Name": "" -> missing so it should be null in place of that and the remaining will be appended after that with a pipe separated like this: null | Ors | 11 | MB

I have data like below in the demo.txt file

"Name":"asxadadad  ,aaf dsf"
"City":"Mum"
"Age":"23"
"Couse":"BBS"
"City":"Ors"
"Age":"11"
"Couse":"MB"
"Name":"adad sf"
"City":"Kol"
"Age":"21"
"Couse":"BB"
"Name":"pqr"
"Age":"21"
"Couse":"NN"

Expected Output:

asxadadad  ,aaf dsf | Mum  | 23 | BBS
null                | Ors  | 11 | MB
adad sf             | Kol  | 21 | BB
pqr                 | null | 21 | NN

I tried the below code: but not working my logic

counter=0
var_0='Couse'

 while read -r line

   echo "$line"

   counter=$(( counter + 1 ))

   var_1=`echo "$line" | grep -oh "Couse"`

   if [ $var_0 == $var_1 ]
   then
        head -$counter demo.txt > temp.txt
        sed -i '1,$counter' demo.txt
        counter = 0
   else
        echo "No thing to do"
   fi

 done < demo.txt
0
4

Using any awk in any shell on every Unix box:

$ cat tst.awk
BEGIN {
    numTags = split("Name City Age Couse",nums2tags)
    for (tagNr=1; tagNr<=numTags; tagNr++) {
        tag = nums2tags[tagNr]
        tags2nums[tag] = tagNr
        wids[tagNr] = ( length(tag) > length("null") ? length(tag) : length("null") )
    }
    OFS=" | "
}
(NR==1) || (prevTag=="Couse") {
    numRecs++
}
{
    gsub(/^"|"$/,"")
    tag = val = $0
    sub(/".*/,"",tag)
    sub(/[^"]+":"/,"",val)

    tagNr = tags2nums[tag]
    vals[numRecs,tagNr] = val

    wid = length(val)
    wids[tagNr] = ( wid > wids[tagNr] ? wid : wids[tagNr] )

    prevTag = tag
}
END {
    # Uncomment these 3 lines if youd like a header line printed:
    # for (tagNr=1; tagNr<=numTags; tagNr++) {
    #   printf "%-*s%s", wids[tagNr], nums2tags[tagNr], (tagNr<numTags ? OFS : ORS)
    # }

    for (recNr=1; recNr<=numRecs; recNr++) {
        for (tagNr=1; tagNr<=numTags; tagNr++) {
            val = ( (recNr,tagNr) in vals ? vals[recNr,tagNr] : "null" )
            printf "%-*s%s", wids[tagNr], val, (tagNr<numTags ? OFS : ORS)
        }
    }
}

$ awk -f tst.awk file
asxadadad  ,aaf dsf | Mum  | 23  | BBS
null                | Ors  | 11  | MB
adad sf             | Kol  | 21  | BB
pqr                 | null | 21  | NN

or if you didn't want to use a hard-coded list of tags (field/column names):

$ cat tst.awk
BEGIN { OFS=" | " }
(NR==1) || (prevTag=="Couse") {
    numRecs++
}
{
    gsub(/^"|"$/,"")
    tag = val = $0
    sub(/".*/,"",tag)
    sub(/[^"]+":"/,"",val)

    if ( !(tag in tags2nums) ) {
        tagNr = ++numTags
        tags2nums[tag] = tagNr
        nums2tags[tagNr] = tag
        wids[tagNr] = ( length(tag) > length("null") ? length(tag) : length("null") )
    }

    tagNr = tags2nums[tag]
    vals[numRecs,tagNr] = val

    wid = length(val)
    wids[tagNr] = ( wid > wids[tagNr] ? wid : wids[tagNr] )

    prevTag = tag
}
END {
    for (tagNr=1; tagNr<=numTags; tagNr++) {
        printf "%-*s%s", wids[tagNr], nums2tags[tagNr], (tagNr<numTags ? OFS : ORS)
    }

    for (recNr=1; recNr<=numRecs; recNr++) {
        for (tagNr=1; tagNr<=numTags; tagNr++) {
            val = ( (recNr,tagNr) in vals ? vals[recNr,tagNr] : "null" )
            printf "%-*s%s", wids[tagNr], val, (tagNr<numTags ? OFS : ORS)
        }
    }
}

$ awk -f tst.awk file
Name                | City | Age | Couse
asxadadad  ,aaf dsf | Mum  | 23  | BBS
null                | Ors  | 11  | MB
adad sf             | Kol  | 21  | BB
pqr                 | null | 21  | NN

Note that the order of the columns in the output for that second script will be the order those tags appear in the input which is why they need a header row to identify the values unless all tags are guaranteed to occur in the input in the order you want them output.

0
3

In perl. I'd add more explanations of what it does/how it works, but I think the comments in the code cover all of that.

#!/usr/bin/perl

use strict;

my @people; # Array-of-Arrays (AoA) to hold each record
my %person; # hash to hold the current record as it's being read in.

# list of valid field names, in the order you want them printed
my @names   = qw(Name City Age Couse);
my $end_key = 'Couse';

# build a regex from the valid names
my $names    = join('|',@names);
my $names_re = qr/^(?:$names)$/;

# Initialise field widths, with a minimum of 4 (for 'null').
my %widths = map {$_ => (length > 4 ? length : 4) } @names;

while(<>) {
  chomp;

  s/^"|"$//g;                       # strip leading and trailing quotes
  my ($key,$val) = split /"?:"?/;   # split on :, with optional quotes.

  if ($key =~ m/$names_re/) {
    $widths{$key} = length($val) if ($widths{$key} < length($val) );

    $person{$key} = $val;

    if ($key eq $end_key) {
      # push an array into the @people array, containing the values of
      # the valid fields, in order.  Use null as the default value
      # if any field is empty/undefined.
      push @people, [ map { $person{$_} || 'null' } @names ];
      %person = ();
    };
  } else {
    print STDERR "Error on input line $.: unrecognised data\n";
  };
};

# build a printf format string, using the longest width of each field.
my $fmt = join(' | ', map { "%-$widths{$_}s" } @names) . "\n";

# optional header line, comment out if not wanted
printf $fmt, @names;

# optional ruler line, comment out if not wanted
print join('-|-', map { '-' x $widths{$_} } @names) . "\n";

foreach my $p (@people) {
  printf $fmt, @{ $p };
}

Save as, e.g., columns.pl, and make executable with chmod +x.

Output:

$ chmod +x columns.pl 
$ ./columns.pl demo.txt 
Name                | City | Age  | Couse
--------------------|------|------|------
asxadadad  ,aaf dsf | Mum  | 23   | BBS 
null                | Ors  | 11   | MB  
adad sf             | Kol  | 21   | BB  
pqr                 | null | 21   | NN  
3

A short GNU awk compatible (for the RS defined as regex) solution and works apart from where the tags are located within a block of records and we follows the tags order from the input when it's come to printing; and the last tag Couse is the record end identifier:

<infile awk -F'\n' -v tags='Name,City,Age,Couse' '
BEGIN{ tagsNum=split(tags, tgs, ","); RS="\n?\""tgs[tagsNum]"\":[^\n]*\n" }

function tbl(tag, field) {
    if(index(field, "\""tag"\"")==1 && !key[tag]++ || field==RT){
        gsub(/(^[^:]*:"|"\n?)/, "", field)
        key[tag]=field
    }
}
{ for(i=1; i<=NF; i++){ for(k in tgs) tbl(tgs[k], $i); tbl(RT, RT) }
  for(i=1; i<tagsNum; i++)
      printf "%s", (key[tgs[i]]!=""? key[tgs[i]]:"null") OFS; print key[RT]
  delete key
}' OFS='@|' |column -ts'@'

with calling the function for every tag name from the tgs array, we refill the values for them by the matching on relevant field they occurred in, and next we print them for each record and null if they don't take any value and then delete the array and do the same for next block and so on.

we used the column -ts'@' to tabular the output and that @ character is come from the OFS='@|' and using this way column will adjust the output fields based on that character and later will be removed from the output, so this assumed that @ character should not be occurred in your input data (if it can, change to another character instead). if you have column from util-linux package, you can change OFS='@|' |column -ts'@' to OFS='|' |column -t -s'|' -o' | '.


asxadadad  ,aaf dsf  |Mum   |23  |BBS
null                 |Ors   |11  |MB
adad sf              |Kol   |21  |BB
pqr                  |null  |21  |NN
0
0

Making use of bash arrays in which we Store the header field names. Since the sed code is quite repetitive for the various header fields, so we first generate the sed code based on the header. Then generate the output.

#!/usr/bin/env bash
declare -a tags=(Name City Age Couse end)
{
echo 'H;/^"Couse":/!d;z;x'
paste -d'\n' \
  <(printf 's/\\n"%s":"([^"]*)"(.*)/\\2|\\1/\n' "${tags[@]:0:4}") \
  <(printf 't %s\n' "${tags[@]:1:4}") \
  <(yes 's/$/|null/'|head -n4) \
  <(printf ':%s\n' "${tags[@]:1:4}");
echo 's/^\|//'
} |
sed -Ef - demo.txt |
column -ts'|' -o '|' |
sed -e 's/|/ & /g'

Output:

asxadadad  ,aaf dsf | Mum  | 23 | BBS 
null                | Ors  | 11 | MB  
adad sf             | Kol  | 21 | BB  
pqr                 | null | 21 | NN  

Another method using the perl utility is given here.

perl -lne '
  @tags = qw(Name City Age Couse) if $. == 1;
  %h = (%h, /"([^:]+)":"(.*)"/);
  next unless /^"Couse":/;
  for (@tags) {
    $h{$_} ||= q(null);
    length($h{$_})>$maxw{$_} and $maxw{$_}=length($h{$_});
    push @{$Aref->[$nr]}, $h{$_};
  }
  $nr++; %h=();
  }{
  my $fmt = join "A2", map { sprintf q[A%d], $_+1 } @maxw{@tags};
  print pack $fmt, split /([|])/, join q[|], @{shift @$Aref} while @$Aref;
' demo.txt

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.