transform multiline text into csv with awk sed and grep

user_1049905 0
Viewed 138

I run a shell command that returns a list of repeated values like this (note the indentation):

Name:               vm346
  cpu                1 (12%)      6150m (76%)
  memory             1130Mi (7%)  1130Mi (7%)
Name:               vm847
  cpu                6 (75%)        30150m (376%)
  memory             12980Mi (87%)  12980Mi (87%)
Name:               vm848
  cpu                3500m (43%)   17150m (214%)
  memory             6216Mi (41%)  6216Mi (41%)

I am trying to transform that data like this (in csv):

vm346,1,(12%),6150m,(76%),1130Mi,(7%),1130Mi,(7%)
vm847,6,(75%),30150m,(376%),12980Mi,(87%),12980Mi,(87%)
vm848,3500m,(43%),17150m,(214%),6216Mi,(41%),6216Mi,(41%)

The problem is that any given dataset like the one above is always on more than one line.

when I pipe that into it awk it drives me mad because even if I use:

BEGIN{ FS="\n" }

to try and stitch the data together in one line, it doesn't work. No matter what I do, awk keeps the name value as a separated line above everything else.

I am sorry I haven't much code to share but I have been spinning my wheels with this for a few hours now and I am running out of ideas...

sed csv grep awk
5 Answers

I can solve this in Perl:

perl -ane 'print join ",", @F[1 .. $#F]; print $F[0] eq "memory" ? "\n" : ","'

It should be easy to translate it to awk if you need it.

How does it work?

  • -a splits each line on whitespace into the @F array
  • -n reads the input line by line and runs the code specified after -e for each line
  • We print all the elements but the first one separated by commas (see join)
  • We then look at the first column, if it's memory, we are at the last line of the block, so we print a newline, otherwise we print a comma
user_10306750

With AWK, one option is to set RS to "Name: ", and ignore the first record with NR > 1, e.g.

awk -v RS="Name: " 'BEGIN{OFS=","} NR > 1 {print $1, $3, $4, $5, $6, $8, $9, $10, $11}' file
#> vm346,1,(12%),6150m,(76%),1130Mi,(7%),1130Mi,(7%)
#> vm847,6,(75%),30150m,(376%),12980Mi,(87%),12980Mi,(87%)
#> vm848,3500m,(43%),17150m,(214%),6216Mi,(41%),6216Mi,(41%)
user_129573400 
awk '{$1=""}1' | paste -sd'  \n' - | awk '{$1=$1}1' OFS=,

Get rid of the first column. Join every three rows. Same idea with sed:

sed 's/^ *[^ ]* *//' | paste -sd'  \n' - | sed 's/  */,/g'

Something else:

awk '
$1=="Name:" {
  sep=ors
  ors=ORS
} {
  for (i=2;i<=NF;++i) {
    printf "%s%s",sep,$i
    sep=OFS
  }
} END {printf "%s",ors}'

Or if you want to print an ORS based on the first field being "memory" (note that this program may end without printing a terminating ORS):

awk '{for (i=2;i<=NF;++i) printf "%s%s",$i,(i==NF && $1=="memory" ? ORS : OFS)}'

something else else:

awk -v OFS=, '
index($0,$1)==1 {
  OFS=ors
  ors=ORS
} {
  $1=""
  printf "%s",$0
  OFS=ofs
} END {printf "%s",ors} BEGIN {ofs=OFS}'
Admin1

Here is a ruby to do that:

ruby -e '
s=$<.read
s.scan(/^([^ \t]+:)([\s\S]+?)(?=^\1|\z)/m).      # parse blocks
    map(&:last).                                 # get data part
    # parse and join the data fields:
    map{|block| block.split(/\n[ \t]+[^ \t]+[ \t]+/)}.
    map{|lines| lines.map(&:strip).join(" ").split().join(",")}.
    each{|l| puts "#{l}"}
' file 
vm346,1,(12%),6150m,(76%),1130Mi,(7%),1130Mi,(7%)
vm847,6,(75%),30150m,(376%),12980Mi,(87%),12980Mi,(87%)
vm848,3500m,(43%),17150m,(214%),6216Mi,(41%),6216Mi,(41%)

The advantage is that this is not dependent on the number of lines or the number of fields. It is parsing data that is in blocks of the form:

START:   ([ \t]+[data_with_no_space])*\n
   l1    ([ \t]+[data_with_no_space])*\n
   ...
START:
   ...

Works this way:

  1. Parse the blocks with THIS REGEX;
  2. Save an array of the data elements;
  3. Join the sub arrays and then split into data fields;
  4. Join(',') to make a csv.
user_2986070

This might work for you (GNU sed):

sed -nE '/^ +\S+ +/{s///;H;$!d};x;/./s/\s+/,/gp;x;s/^\S+ +//;h' file

In overview the sed program processes indented lines, already gathered lines (except in the case that the current line is the first line of the file) and non-indented lines.

Turn off implicit printing and enable extended regexp's. (-nE).

If the current line is indented, remove the indent, the first field and any following spaces, append the result to the hold space and if it is not the last line, delete it.

Otherwise, check the hold space for gathered lines and if found, replace one or more whitespaces by commas and print the result. Then prep the current line by removing the first field and any following spaces and replace the hold space with the result.

The solution seems logically back-to-front, but programming in this style avoids having to check for end-of-file multiple times and invoking labels and gotos.

N.B. This solution will work for any number of indented lines.

user_9674920
Related