Data Conversion Using Sed or Awk - Name to Title

user_2854333 0
Viewed 53

I have data in the below format:

APP_OWNER                     : hari
APP_AREA                      : Work:Business Area:AUS
APP_ID                        : 124080
APP_OWNER                     : ari
APP_AREA                      : Work:AUS
APP_ID                        : 124345

I want the data to be converted to below format.

APP_OWNER,APP_AREA,APP_ID
hari,Work:Business Area:AUS,124080
ari,Work:AUS,124345

I can convert one line but how to do it with 3 lines at the same time?

My Attempt with one line

sed '0,/: /s//\n/' test.txt

Original Question : Convert Rows to Columns Shell Script

Regards

sed awk
3 Answers

Here is an awk solution without hardcoding any value:

awk -F '[[:blank:]]+:[[:blank:]]+' 'NR == 1 {fh=$1} !($1 in hdr) {tr = (tr == "" ? "" : tr ",") $1; hdr[$1]} $1 == fh && NR > 1 {print (body ? "" : tr ORS) td; body=1; td=""} {td = (td == "" ? "" : td ",") $2} END {print td}' file

APP_OWNER,APP_AREA,APP_ID
hari,Work:Business Area:AUS,124080
ari,Work:AUS,124345

Here is more readable version:

awk -F '[[:blank:]]+:[[:blank:]]+' '
NR == 1 {
   fh = $1
}
!($1 in hdr) {                    # collect headers only once
   tr = (tr == "" ? "" : tr ",") $1
   hdr[$1]
}
$1 == fh && NR > 1 {              # print header and body
   print (body ? "" : tr ORS) td  # if body=1 then print only body
   body = 1
   td = ""
}
{
   td = (td == "" ? "" : td ",") $2
}
END {
   print td
}' file
user_5482250

Could you please try following, written and tested with shown samples.

awk -F'[[:blank:]]+:[[:blank:]]+' -v OFS="," '
/^APP_OWNER/{
  if(heading){
    count=1
    print heading
  }
  if(val){
    print val
  }
  val=""
}
count=="" && !($1 in head){
  head[$1]
  heading=(heading?heading OFS:"")$1
}
{
  val=(val?val OFS:"")$2
}
END{
  if(val){
    print val
  }
}
'  Input_file
user_58665800 
$ cat tst.awk
BEGIN { OFS="," }
{
    tag = val = $0
    sub(/[[:space:]]*:.*/,"",tag)
    sub(/[^:]+:[[:space:]]*/,"",val)
    tag2val[tag] = val
}
!seen[tag]++ {
    tags[++numTags] = tag
}
(NR%3) == 0 {
    if ( !doneHdr++ ) {
        for (tagNr=1; tagNr<=numTags; tagNr++) {
            tag = tags[tagNr]
            printf "%s%s", tag, (tagNr<numTags ? OFS : ORS)
        }
    }

    for (tagNr=1; tagNr<=numTags; tagNr++) {
        tag = tags[tagNr]
        val = tag2val[tag]
        printf "%s%s", val, (tagNr<numTags ? OFS : ORS)
    }

    delete tag2val
}

$ awk -f tst.awk file
APP_OWNER,APP_AREA,APP_ID
hari,Work:Business Area:AUS,124080
ari,Work:AUS,124345
user_17450010
Related