Linux shell script to convert rows into column data

Question

I want to convert data from below input file, converting rows tp columns:

Input:

Avamar Hostname                 server1.corpnet2.com
Avamar Server Version           19.1.0-38 
Node Type                       Single Node Gen4t-M2400
Gen4T ROC SN/WWN                WARNING
EMC Hardware Health             PASSED
Avamar Hostname                 server2.CORPNET2.COM
Avamar Server Version           19.1.0-38
Node Type                       Single Node Gen4t-M1200
Gen4T ROC SN/WWN                WARNING
EMC Hardware Health             PASSED

Required output:

Avamar Hostname        Avamar Server Version   Node Type               Gen4T ROC SN/WWN EMC Hardware Health
server1.corpnet2.com   19.1.0-38               Single Node Gen4t-M2400 WARNING          PASSED
server2.CORPNET2.COM   19.1.0-38               Single Node Gen4t-M1200 WARNING          PASSED

score 1 · Accepted Answer · answered Aug 02 '21 at 12:06

#!/usr/bin/perl
use strict;

my @keynames = (
  'Avamar Hostname', 'Avamar Server Version','Node Type',
  'Gen4T ROC SN/WWN', 'EMC Hardware Health',
);

# Make a hash where the keys are the key names, and the values
# are the index number of the key. This will make sure each
# field of every record is stored and printed in the right order.
my %keys;
foreach (0 .. $#keynames) { $keys{$keynames[$_]} = $_; };

# String field lengths are hard-coded after examining the sample data.
#
# These could be auto-calculated at the cost of having two
# passes through the data, and keeping the max length of
# each field in a hash. The hash should be initialised
# (in the foreach loop above) with the lengths of the headers
# so they're at least that wide.
my $fmt = "%-20s  %-21s  %-23s  %-16s  %-19s\n";

my @record;

printf $fmt, @keynames;

while(<>) {
  chomp;
  # split the input line on two-or-more spaces or a tab.
  # I'm not sure if the input is tab or space separated,
  # this will work with either.
  my ($key,$val) = split /  +|\t/;

  if ($. > 1 && (eof || $key eq $keynames[0])) {
    printf $fmt, @record;
    @record=();
  };

  $record[$keys{$key}] = $val;
}

Sample output:

$ ./row-to-col.pl input.txt  
Avamar Hostname       Avamar Server Version  Node Type                Gen4T ROC SN/WWN  EMC Hardware Health
server1.corpnet2.com  19.1.0-38              Single Node Gen4t-M2400  WARNING           PASSED
server2.CORPNET2.COM  19.1.0-38              Single Node Gen4t-M1200  WARNING           PASSED

Note: this would be easier to process if there were at least one blank line between each record. Then it could be parsed in paragraph mode. If you can modify whatever is generating your input data to do that, then I'd advise doing so. Of course, if you could do that, you could also modify it to generate this tabulated format.

Linux shell script to convert rows into column data

1 Answers1