CODEWITHSUNDEEP
shell
Naman Agarwal
Naman Agarwal

Reputation: 654

Convert table like text into separate values with column names using Shell scripting

I have a text file like below:

Execution ID, Unique number, Result 1, Result 2
1234567 , 1002 , Dron,  User suppressed due to Inactivity Rule
1234567 , 1002 , Dron,  User suppressed due to Wrong Email Address
2348976 , 1003 , Dron,  User suppressed due to Language Rule

I want the above text to be converted like below:

Execution ID: 1234567
Unique number: 1002
Result 1: Dron
Result 2: User suppressed due to Inactivity Rule, User suppressed due to Wrong Email Address

Execution ID: 2348976
Unique number: 1003
Result 1: Dron
Result 2: User suppressed due to Language Rule

Upvotes: 0

Views: 68

Answers (2)

RavinderSingh13
RavinderSingh13

Reputation: 133458

Could you please try following.

awk '
BEGIN{
  FS=SUBSEP=OFS=","
}
FNR==1{
  split($0,header,", ")
  next
}
{
  a[$1,$2,$3]=(a[$1,$2,$3]?a[$1,$2,$3]":":"")$4
}
END{
  for(i in a){
    val=i OFS a[i]
    num=split(val,array,",")
    for(o=1;o<=num;o++){
      print header[o],array[o]
    }
    delete array
  }
}
' Input_file

Explanation: Adding a detailed explanation for above code.

awk '                                                     ##Starting awk program from here.
BEGIN{                                                    ##Starting BEGIN section here.
  FS=SUBSEP=OFS=","                                       ##Setting FS, OFS and SUBSEP as comma here.
}                                                         ##Closing BEGIN BLOCK for this awk code.
FNR==1{                                                   ##Checking condition if this is first line then do following.
  split($0,header,",")                                    ##Splitting line into an array named header whose delimiter is comma here.
  next                                                    ##next will skip all further statements from here.
}                                                         ##Closing BLOCK for FNR==1 condition here.
{
  a[$1,$2,$3]=(a[$1,$2,$3]?a[$1,$2,$3]":":"")$4           ##Creating an array named a whose index is $1,$2,$3 and value is $4 with concatenating each line its own value in it.
}
END{                                                      ##Starting END block for this awk code here.
  for(i in a){                                            ##Traversing through array a all elements.
    val=i OFS a[i]                                        ##Creating a variable val and its value is variable i(index of array a) OFS and array a value here.
    num=split(val,array,",")                              ##Splitting variable val to array named array whose delimiter is comma here.
    for(o=1;o<=num;o++){                                  ##Starting a for loop from o=1 to till value of num.
      print header[o],array[o]                            ##Printing header array with index of o and array with index of o here.
    }
    delete array                                          ##Deleting array here, to avoid confusions, since each cycle new array will be created.
  }
}
'  Input_file                                             ##Mentioning Input_file name here.

Upvotes: 0

accdias
accdias

Reputation: 5372

You can do it using awk like this:

awk 'BEGIN{FS=", "}NR==1{split($0, h)}NR>1{printf "%s: %s\n%s: %s\n%s: %s\n%s: %s\n\n",h[1],$1,h[2],$2,h[3],$3,h[4],$4}' textfile

And here is a proof of concept:

$ awk -V | head -1
GNU Awk 5.0.1, API: 3.0 (GNU MPFR 3.1.6-p2, GNU MP 6.1.2)

$ cat csv 
Execution ID, Unique number, Result 1, Result 2
1234567, 1002, Dron, User suppressed due to Inactivity Rule
2348976, 1002, Dron, User suppressed due to Language Rule

$ awk 'BEGIN{FS=", "}NR==1{split($0, h)}NR>1{printf "%s: %s\n%s: %s\n%s: %s\n%s: %s\n\n",h[1],$1,h[2],$2,h[3],$3,h[4],$4}' csv 
Execution ID: 1234567
Unique number: 1002
Result 1: Dron
Result 2: User suppressed due to Inactivity Rule

Execution ID: 2348976
Unique number: 1002
Result 1: Dron
Result 2: User suppressed due to Language Rule

$

Upvotes: 1

Related Questions