Collapse multi-line records into single CSV lines containing the "value" parts only

It seems you want to

• collapse multi-line records into single CSV lines,
• only print the values of the record attributes Name, Admin and Oper
• and print "explicit" empty fields where one of these attributes is not given.

I would recommend the following awk program:

awk -F'>' 'function printrec(){printf "%s,%s,%s\n",buf["Name"],buf["Admin"],buf["Oper"]}
           (FNR>1 && $1=="Name"){printrec();delete buf}
           {sub(/[[:space:]]*$/,"",$2); buf[$1]=$2}
           END{printrec()}' input.txt

It works as follows:

• The field separator for the input file is set to >.

• All elements of the records are stored in an associative array buf.

• A function printrec() is defined which prints the relevant fields of buf with comma-separation. If buf doesn't contain a particular key, referencing it will evaluate to the empty string, thereby fulfilling your requirement of an empty field for a missing attribute.

• A record is assumed to start with a Name line. If such a line is encountered, and it is not the first line in the file (FNR>1), the previously buffered record will be printed, and the buffer cleared again.

• For each line, the current attribute will be stored in buf, with the "key" part as "array index" and the "value" part as array value.

  Note that I included a sub() call to remove trailing space from the "value" part, which your input example contains. If you are sure that there are no spaces in reality, you can omit that part of line 3.

• At end-of-file, the final buffered record will be printed.

Applying this program to your example will yield

Ami,2,1
Sum,3,
Tar,1,2

Note that the use of delete for an array requires GNU awk. If you have a different awk flavor, you will need to use

split("",buf)

as a workaround.

Also, if a record contains multiple instances of an attribute (except for Name which is always taken as record start), later occurences will overwrite previous occurences.

If I understood what you want then you should use xargs and awk commands:

xargs -n3 < your_file.txt  | awk  '{gsub(/(Name|Admin|Oper)>/,"");  print $1","$2","$3}' | awk -F',' --file script.awk

Where script.awk contains this:

#! /usr/bin/awk

{
   if($1 ~ ".*>.*") print ","$2","$3 
   else if($2 ~ ".*>.*") print $1",,"$3 
   else if($3 ~ ".*>.*") print $1","$2"," 
   else print
}

If in the your_file.txt you have:

Name>Ami  
Admin>2  
Oper>1  
Name>Sum  
Admin>3  
Total>2  
Name>Tar  
Admin>1  
Oper>2

With xargs -n3 you will get the output of the file every 3 lines (as one line):

Name>Ami Admin>2 Oper>1
Name>Sum Admin>3 Total>2
Name>Tar Admin>1 Oper>2

With awk '{gsub(/(Name|Admin|Oper)>/,""); print $1","$2","$3}' the values like Name> Admin> or Oper> will be replaced by an empty string and with print $1","$2","$3 the values (after >) will be printed with a comma.

If you use:

xargs -n3 < data3  | awk  '{gsub(/(Name|Admin|Oper)>/,"");  print $1","$2","$3}

you will get:

Ami,2,1
Sum,3,Total>2
Tar,1,2

Now, it will be removed unnecessary strings like Total>2. With the script.awk you can remove them but before we have to define the separator, in this case the comma (,). The awk code like $1 ~ ".*>.*" will verify if the current string ($1, $2 or $3) does match with the pattern .*>.* if it does, then, the current string will not be printed.

Important: The script.awkworks by group. So, if there is a not valid column, this one must be placed instead of Name or Admin or Oper. For example if your_file.txt has:

Name>Ami  
Total>2  
Oper>1  
Name>Sum  
Admin>3  
Total>2  
Total>Tar  
Admin>1  
Oper>2

The output of the script will be:

Ami,,1
Sum,3,
,1,2

But if you have inside the your_file.txt:

Total>Ami  
Total>2  
Oper>1  
Name>Sum  
Admin>3  
Total>2  
Total>Tar  
Admin>1  
Total>2

the command will not work as you expect.

Note: If you want that your_file.txt be edited too, you will have to use tee your_file.txt at the end:

xargs -n3 < your_file.txt  | awk  '{gsub(/(Name|Admin|Oper)>/,"");  print $1","$2","$3}' | awk -F',' --file script.awk | tee your_file.txt

[Not knowing whether the Name>... record can be missing at times instead of systematically appearing at regular interval in the data to be processed, I will assume it's always there.]

A simple solution based on awk:

• does not use an intermediate array,
• need not resort to defining a function:
• fixes the issue of trailing spaces in the data records to process
• allows for any number of input files listed as space separated arguments of the awk script below.

and is, subjectively, a little simpler to read, although perhaps less elegant than @AdminBee's answer.

 $ awk -F'>' '($1 == "Name") {
                  if (NR>1) printf "\n"; 
                  gsub(" ","",$2); 
                  printf "%s%s", $2,","}
              ($1 == "Admin") {
                  gsub(" ","",$2); 
                  printf "%s%s", $2,","} 
              ($1 == "Oper") {
                  gsub(" ","",$2); 
                  printf "%s", $2} 
              END {printf "\n"}' input_file
Ami,2,1
Sum,3,
Tar,1,2 
 

In the above, action gsub(" ","",$2) suppresses all spaces in the second field, denoted $2. (OP includes those trailing spaces and they wreak havoc when displaying results.)

If only the input data looked a bit more like this (rec-formatted):

Name: Ami
Admin: 2
Oper: 1

Name: Sum
Admin: 3
Total: 2

Name: Tar
Admin: 1
Oper: 2

... then we could easily use rec2csv | csvcut -C Total to produce the CSV document

Name,Admin,Oper
Ami,2,1
Sum,3,
Tar,1,2

Here rec2csv is a utility from GNU recutils that reformats rec-formatted data into CSV, and csvcut is a utility that selects columns from CSV (here it's used to exclude the Total column), from csvkit.

The original data can be transformed into rec-formatted format using awk, and then fed into rec2csv and csvcut:

awk '/^Name>/ { print "" } { sub(">",": "); print }' file | rec2csv | csvcut -C Total

This simply ensures that each line that starts with Name> is preceded by an empty line and then replaces the first occurrence of > with : on each line.

Pass the result through tail -n +2 if you want to remove the CSV header.

This obviously assumes that each occurrence of Name> in the original file introduces a new record.

If only the input looked a bit more like this (xtab-formatted):

Name Ami
Admin 2
Oper 1

Name Sum
Admin 3
Total 2

Name Tar
Admin 1
Oper 2

Then we could use mlr (Miller) to first "unsparsify" each record (assign empty values to missing keys), and then extract the wanted keys in CSV output format:

mlr --ixtab unsparsify | mlr --ocsv cut -f Name,Admin,Total

We can get the original data into xtab-format using an awk command similar to the previous one, and then feed it through Miller:

awk '/^Name>/ { print "" } { sub(">"," "); print }' file | mlr --ixtab unsparsify | mlr --ocsv cut -f Name,Admin,Oper

This would give you

Name,Admin,Oper
Ami,2,1
Sum,3,
Tar,1,2

Use mlr (the final one in the pipeline) with its -N option to remove the CSV header.